Linq: Select Most Recent Record of Each Group

entity-framework-6 linq

Question

I want to get the latest record of each group from a SQL Server table using Linq.

Table Example:

Original data

I want to get this result:

Desired result

My Linq query returns one record for each company, but it doesn't return the most recent ones:

var query = from p in db.Payments
            where p.Status == false 
            && DateTime.Compare(DateTime.Now, p.NextPaymentDate.Value) == 1
            group p by p.CompanyID into op                                          
            select op.OrderByDescending(nd => nd.NextPaymentDate.Value).FirstOrDefault();

What am i missing here? Why isn't the NextPaymentDate being ordered correctly?

!!UPDATE!! My query is working as expected. After analysing @Gilang and @JonSkeet comments i ran further tests and found that i wasn't getting the intended results due to a column that wasn't being updated.

1
-1
11/13/2015 12:14:49 PM

Accepted Answer

 var query = from p in db.Payments
             where p.Status == false
             group p by p.CompanyID into op
             select new { 
                 CompanyID = op.Key,
                 NextPaymentDate = op.Max(x => x.NextPaymentDate), 
                 Status = false
             };

The reason your query is not being ordered correctly is that your query does not do proper grouping. You did correctly grouping by CompanyID, but then you have to retrieve the maximum NextPaymentDate by calling aggregate function.

Status can be assigned false because it is already filtered by Where clause in the early clauses.

1
11/13/2015 4:08:44 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow