How to get first record in each group using Linq

Question

Considering the following records:

   Id          F1            F2             F3 
 -------------------------------------------------
   1           Nima          1990           10
   2           Nima          1990           11
   3           Nima          2000           12
   4           John          2001           1
   5           John          2002           2 
   6           Sara          2010           4

I want to group by based on the F1 field and sort by Id and get all fields from the first record of group similar to these records:

   Id          F1            F2             F3 
 -------------------------------------------------
   1           Nima          1990           10
   4           John          2001           1
   6           Sara          2010           4

How can I do this using linq?

King King · Answer 1 · 2021-03-31T10:25:02.640

237

var result = input.GroupBy(x => x.F1, (key,g) => g.OrderBy(e => e.F2).First());

edited Mar 31 '21 at 10:25

answered Sep 25 '13 at 18:59

King King

61,710
16
105
130

4

This showed me the way using Fluent syntax. The [overloads for GroupBy](http://msdn.microsoft.com/en-us/library/vstudio/system.linq.enumerable.groupby(v=vs.100).aspx) appear to give you a lot of power - one to add to the long list of learning! – rogersillito Jul 03 '14 at 12:05
1

@rogersillito that's why it's called a query (Language Integrated Query - LINQ), also the GroupBy has many overloads but there are just a few being commonly used, also it depends on the personal preference, such as some ones prefer to write it as `.GroupBy(x=>x.F1).Select(g=>...)`, it may look easier to understand. – King King Jul 03 '14 at 12:12
similar to get the Get the **first record of a group** try [this](http://stackoverflow.com/a/3850476/2218697) , hope helps someone. – Shaiju T Jan 23 '16 at 16:35
15

You should replace `.First()` with `.FirstOrDefault()` or you will get the exception: `The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead.` – Nikolay Kostov Feb 12 '17 at 16:57
5

@NikolayKostov I know about that, but I assumed the LINQ here is used as LINQ-to-object, not LINQ-to-entities, so we can safely use `.First()`. The OP's question looks really like just caring about linq-to-object, although he also tagged the question with `linq-to-entities` (not sure if he understood what that is). – King King Feb 13 '17 at 02:02
i like this answer except for the fact that i cant make any sense out of it or use it – user1566694 Jan 11 '18 at 20:37
1

I liked this answers,Nice one. – Ajas Aju Jun 06 '18 at 05:46
alternatively this is equivalent `input.GroupBy(x => x.F1).Select(g => g.OrderBy(x => x.F2).First())` – CervEd Mar 31 '21 at 12:21
This only returns the first group. If you want the first from each group you need to ass an additional select. (var result = input.GroupBy(x => x.F1, (key,g) => g.OrderBy(e => e.F2).Select(s => s..First()); – Russ Ebbing Jul 21 '23 at 16:59

score 154 · Accepted Answer · edited Jul 29 '21 at 08:53

154

var res = from element in list
          group element by element.F1
              into groups
              select groups.OrderBy(p => p.F2).First();

edited Jul 29 '21 at 08:53

Selim Yildiz

5,254
6
18
28

answered Sep 25 '13 at 19:00

Alireza

10,237
6
43
59

6

So glad i found this. I got error message "The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead." from your example. Only operation on the list i did after the query was passing myResult.ToList() to a method. Using FirstOrDefault solved it though – aghost Feb 05 '16 at 10:38
Would it be `OrderBy(p => p.Id)`, they wanted it ordered by ID not the year column. – Mike Flynn Jun 10 '16 at 17:21
Can you please provider some assistance for this: https://stackoverflow.com/questions/44764687/how-to-use-linq-to-group-by-and-order-by-certain-column – Si8 Jun 28 '17 at 14:00
What if want to retrieve the first and last item from the group? – Sandeep Pandey Feb 14 '20 at 13:11
Is there any way to project the results of this into a known type? – Ken Hadden Aug 18 '22 at 18:04

score 15 · Answer 3 · answered Oct 08 '16 at 11:57

The awnser of @Alireza is totally correct, but you must notice that when using this code

var res = from element in list
          group element by element.F1
              into groups
              select groups.OrderBy(p => p.F2).First();

which is simillar to this code because you ordering the list and then do the grouping so you are getting the first row of groups

var res = (from element in list)
          .OrderBy(x => x.F2)
          .GroupBy(x => x.F1)
          .Select()

Now if you want to do something more complex like take the same grouping result but take the first element of F2 and the last element of F3 or something more custom you can do it by studing the code bellow

 var res = (from element in list)
          .GroupBy(x => x.F1)
          .Select(y => new
           {
             F1 = y.FirstOrDefault().F1;
             F2 = y.First().F2;
             F3 = y.Last().F3;
           });

So you will get something like

   F1            F2             F3 
 -----------------------------------
   Nima          1990           12
   John          2001           2
   Sara          2010           4

The delimiter or separator of the new object should be comma not semi colon.Please Check. — Naredla Nithesh Reddy, Jun 17 '19 at 16:22
possibly the `First()` part is nomore supported in EFCore 3.1, you might have to materialize the data first. — LuckyLikey, Feb 04 '21 at 10:21

score 4 · Answer 4 · answered Nov 22 '16 at 19:01

4

Use it to achieve what you want. Then decide which properties you want to return.

yourList.OrderBy(l => l.Id).GroupBy(l => new { GroupName = l.F1}).Select(r => r.Key.GroupName)

answered Nov 22 '16 at 19:01

Rubens Mussi Cury

508
4
16

score 3 · Answer 5 · answered Jul 21 '21 at 19:38

Another way:

var result = input.GroupBy(i => i.F1).Select(g => g.First());

You can group by multiple fields:

var result = input.GroupBy(i => new {i.F1, i.F2}).Select(g => g.First());

If you need keep order, then use lookup:

var result = input.ToLookup(i => i.F1).Select(g => g.First());

score 1 · Answer 6 · answered Jan 03 '21 at 14:16

1

var res = (from element in list)
      .OrderBy(x => x.F2).AsEnumerable()
      .GroupBy(x => x.F1)
      .Select()

Use .AsEnumerable() after OrderBy()

answered Jan 03 '21 at 14:16

gaurav

75
1
3

Damian Vogel · Answer 7 · 2023-07-27T10:53:29.360

It's not exactly what you were looking for, but sometimes we look for the wrong thing because we don't know what exists. So my solution I find the most intuitiv:

var dict = 
input.OrderByDescending(x => x.Id)
     .GroupBy(x => x.F1)
     .ToDictionary(x => x.Key, x => new { x.First().F1, x.First().F2, x.First().F3});

First order, then group – straight forward. Now the result of that will be a list of key-type pairs❊. As our table was already sorted, we can just pick the first entry. Also I advise to put the result in a dictionary with the ID as accessor, it's a very fast data structure to access and still flexible. Now you can access it using

dict[4].F1 // returns John

❊ technically its IEnumerable<IGrouping<int, yourType>>

How to get first record in each group using Linq

7 Answers7

Linked

Related