Using LINQ nested query get latest rows for update

Question

LINQ experts, I am looking to update the latest data on a per user basis, to explain say I have a table "dummy":

CREATE TABLE [dbo].[dummy](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [UserId] [int] NOT NULL,
    [TransAmount] [decimal](10, 4) NOT NULL
) ON [PRIMARY]

What I am looking to do is get the latest record for each UserId, using SQL I would use something like:

select * from dummy d1 join 
(
select max(id) as id 
from dummy d1
Join (select distinct userid from dummy) d2 on d1.userid = d2.userid
group by d1.userid)
as d2 on d1.id = d2.id

but I am looking to do this using LINQ.

Thanks.

I don't think that second nested query is necessary since you're already grouping by userid. — Stickman, May 29 '13 at 18:27

score 5 · Answer 1 · answered May 29 '13 at 18:33

5

What you're looking for is GroupBy in LINQ. Something along these lines:

var users = from d in dummy
            group d by d.userId into u
            select new 
            {
               UserId = u.Key
               Id = u.Max(i => i.Id)
            };

answered May 29 '13 at 18:33

Deeko

1,514
9
29

1

An excellent answer although I was looking for the complete record as in the accepted answer. – Wili Whitelaw May 30 '13 at 09:28

Ayo I · Accepted Answer · 2013-05-29T20:52:16.163

2

The following would work as well using method syntax:

var maxIds = dummy.GroupBy(d => d.UserId, (userId, dummyGroup) => 
dummyGroup.Max(dg => dg.Id));

If you want the entire record, you can use the following:

var recordsWithMaxId = dummy.GroupBy(d => d.UserId, (userId, dummyGroup) => 
dummyGroup.OrderByDescending(dg => dg.Id).First());

edited May 29 '13 at 20:52

answered May 29 '13 at 20:39

Ayo I

7,722
5
30
40

score -1 · Answer 3 · edited May 23 '17 at 12:28

-1

Check that :

var maxID = dummy.Max(i => i.UserID)
var row = table.First(i => i.UserID == maxID);

reference from : link

edited May 23 '17 at 12:28

Community

1
1

answered May 29 '13 at 18:40

Emmanouil Chountasis

590
1
7
23

Um, how does this answer the question? OP is looking for the `the latest record for each UserId`, which has nothing to do with `Max of UserID`. Your linked answer only expects to return one row. – gunr2171 May 29 '13 at 19:10

Using LINQ nested query get latest rows for update

3 Answers3