How to select last record in a LINQ GroupBy clause

Question

I have the following simple table with ID, ContactId and Comment.

I want to select records and GroupBy contactId. I used this LINQ extension method statement:

Mains.GroupBy(l => l.ContactID)
 .Select(g => g.FirstOrDefault())
 .ToList()

It returns record 1 and 4. How can I use LINQ to get the ContactID with the highest ID? (i.e. return 3 and 6)

score 47 · Accepted Answer · answered Jul 11 '16 at 10:34

47

You can order you items

Mains.GroupBy(l => l.ContactID)
.Select(g=>g.OrderByDescending(c=>c.ID).FirstOrDefault()) 
.ToList()

answered Jul 11 '16 at 10:34

tym32167

4,741
2
28
32

1

Despite this query will return correct data, it results in compilation into K+1 SQL statements, where K is number of keys (use LINQpad to see it). I faced same problem today and solved it this way: ` var lastCommentIDs = Mains.GroupBy(k => k.ContactID, e => e.ID, (k, e) => new { k, lastID = e.Max(x => x)}); var result = lastCommentIDs.Join(Mains, o => o.lastID, i => i.ID, (o, i) => new { ContactID = o.k, LastComment = i.Comment).ToList(); ` – Dmytro Dec 12 '18 at 14:04
Hi, @Dmitrij. Thanks for feedback. Linqpad is using Linq to SQL, did you tried Linq to entities with this? – tym32167 Dec 12 '18 at 14:15
Hi, @tym32167. yes, I had an attempt to jump into EF, not sure wha exactly was wrong, but EF produced ugliest and absolutely inefficient SQL queries out of LINQ statements. Probably I did something wrong, not sure but the fact is: LINQ2SQL produces more predictable queries than EF. (at least I understand what will happen when I'm executing paticular query using LINQ2SQL) – Dmytro Dec 12 '18 at 22:11

score 8 · Answer 2 · answered Jul 11 '16 at 10:35

8

Use OrderByDescending on the items in the group:

Mains.GroupBy(l => l.ContactID)
    .Select(g => g.OrderByDescending(l => l.ID).First())
    .ToList();

Also, there is no need for FirstOrDefault when selecting an item from the group; a group will always have at least one item so you can use First() safely.

answered Jul 11 '16 at 10:35

RePierre

9,358
2
20
37

1

A shorter version: `g => g.OrderBy(l => l.ID).Last()` :) – Mr. Blond Dec 17 '21 at 13:52

Ivan Yurchenko · Answer 3 · 2016-07-11T11:04:17.180

6

Perhaps selecting with Max instead of OrderByDescending could result into improving of performance (I'm not sure how it's made inside so it needs to be tested):

var grouped = Mains.GroupBy(l => l.ContactID);
var ids = grouped.Select(g => g.Max(x => x.Id));
var result = grouped.Where(g => ids.Contains(g.Id));

As I assume it could result into a query that will take MAX and then do SELECT * FROM ... WHERE id IN ({max ids here}) which could be significantly faster than OrderByDescending.

Feel free to correct me if I'm not right.

edited Jul 11 '16 at 11:04

answered Jul 11 '16 at 10:52

Ivan Yurchenko

3,762
1
21
35

1

fater than OrderBy – Sha-Pai Li Oct 09 '18 at 06:32
Should that last line be...? var result = Mains.Where(g => ids.Contains(g.Id)); – James in Indy Dec 23 '20 at 13:38

score 3 · Answer 4 · answered Jul 11 '16 at 10:43

3

OrderByDescending

Mains.GroupBy(l => l.ContactID)
.Select(g=>g.OrderByDescending(c=>c.ID).FirstOrDefault()) 
.ToList()

is your best solution

It orders by the highest ID descending, which is pretty obvious from the name.

answered Jul 11 '16 at 10:43

Jonathan Ben Avraham

56
1
6

score 0 · Answer 5 · edited May 31 '18 at 09:27

0

You could use MoreLinq like this for a shorter solution:

Main.OrderByDescending(i => i.Id).DistinctBy(l => l.ContactID).ToList();

edited May 31 '18 at 09:27

Jesse

3,522
6
25
40

answered May 31 '18 at 06:35

Mous Kamel

257
1
2
11

How to select last record in a LINQ GroupBy clause

5 Answers5

Linked

Related