CODEWITHSUNDEEP
c#entity-frameworklinq
Manu
Manu

Reputation: 1470

How to group a class by date and select Max(Date), primary-key and foreign-key?

I need to reduce data from a class as given below (PK ... primary key, FK ... foreign key):

enter image description here

Using linq I tried several variations as given below

var groupedClass = from record in records
               group new {
                   record.Date
               }
               by new{
                   record.FK,
                   //record.PK  //  when included then resulting data are not distinct regarding FK
               }
               into g
               select new{
                   MaxDate = g.Max(r => r.Date),
                   SelectedFK = g.key.FK,
                   //SelectedPK = g.key.PK    // gives an error when PK is not inluced in by new
               };

but I can not get distinct PK, FK and Max(date) into the grouped data set. Please help!

Upvotes: 1

Views: 238

Answers (1)

Johnathan Barclay
Johnathan Barclay

Reputation: 20363

Group by FK, then use MaxBy to select the record with the maximum date in each group:

var groupedClass = records
    .GroupBy(record => record.FK)
    .Select(grp => grp.MaxBy(record => record.Date));

Working example: https://dotnetfiddle.net/9Pe4Z4

If you're not using .NET 6 then MaxBy won't be available, so you could use OrderByDescending and First instead:

var groupedClass = records
    .GroupBy(record => record.FK)
    .Select(grp => grp.OrderByDescending(record => record.Date).First());

Upvotes: 2

Related Questions