-1

This is how my table data looks like ,

   Id          C1            C2             C3 
 -------------------------------------------------
   1           John          1990           A
   2           John          1990           B
   3           John          2000           C
   4           Mary          2001           D
   5           Mary          2010           E 
   6           Mary          2010           F
   7           Jack          2010           G
   8           Jack          2010           H
   9           Jack          2011           I

What I want to do is I want to group by C1,C2 columns and get the first items of each group which is order by C3 column descending. So the result will be like

      Id          C1            C2             C3 
     -------------------------------------------------
       1           John          1990           B
       3           John          2000           C
       4           Mary          2001           D
       5           Mary          2010           F 
       7           Jack          2010           H
       9           Jack          2011           I

And I also want to filter row number 2 to 5 using skip and take function.
So the Final result should be like

          Id          C1            C2             C3 
         -------------------------------------------------
           3           John          2000           C
           4           Mary          2001           D
           5           Mary          2010           F 
           7           Jack          2010           H

What I have tried is

await data.GroupBy(d => new { d.C1, d.C2 }).Skip(1).Take(4).FirstAsync();

But it only retrun one row.
How can I solve for this ?

Steven Sann
  • 478
  • 1
  • 7
  • 27

2 Answers2

1

You need to do a Select and since there will be multiple results you'd need to use ToListAsync instead.

data.GroupBy(d => new { d.C1, d.C2 })
   .Select(grp => grp.First())
   .Skip(1)
   .Take(4)
   .ToListAsync();
juharr
  • 31,741
  • 4
  • 58
  • 93
  • `first()` and `firstOrDefault()` , which is better ? – Steven Sann Jul 12 '20 at 15:12
  • 1
    @StevenSann In this case either will work, but it's safe to use `First` since the resulting groups from a `GroupBy` can not be empty. – juharr Jul 12 '20 at 15:14
  • is `.Select(grp => grp.First())` will randomly retrun a row of group ? can I order and get the first row ? for ( eg. in my question , I want to order by `C3` column descending. so the first row should be ` 2 John 1990 B` – Steven Sann Jul 12 '20 at 16:40
  • @StevenSan In that case you can co `grp => grp.OrderBy(x => x.C3).First()` – juharr Jul 13 '20 at 11:18
1

You can do it by,

using System; 
using System.Linq; 
using System.Collections.Generic;
...

var result = await data.GroupBy(d => new { d.C1, d.C2 }) //Group by C1 and C2
                 .Select(s => s.First())  //Select first record from each Group
                 .Skip(1)     //Skip first record
                 .Take(4)     //Pick up next 4 records
                 .ToListAsync()    //Convert 4 records to List.
Prasad Telkikar
  • 15,207
  • 5
  • 21
  • 44
  • `first()` and `firstOrDefault()` , which is better ? – Steven Sann Jul 12 '20 at 15:12
  • 3
    @StevenSann `FirstOrDefault` isn't needed since `GroupBy` will always return a collection of items that do not have empty sets. – juharr Jul 12 '20 at 15:13
  • @juharr, thanks for the information. I was not aware of this fact. I updated my answer with `First()` – Prasad Telkikar Jul 12 '20 at 15:13
  • 1
    @StevenSann, You can use `First()` or `FirstOrDefault()`. for more information https://stackoverflow.com/questions/1024559/when-to-use-first-and-when-to-use-firstordefault-with-linq – Prasad Telkikar Jul 12 '20 at 15:16
  • @PrasadTelkikar , is .Select(grp => grp.First()) will randomly retrun a row of group ? can I order and get the first row ? for ( eg. in my question , I want to order by C3 column descending. so the first row should be ` 2 John 1990 B` – Steven Sann Jul 12 '20 at 18:22