CODEWITHSUNDEEP
linqentity-framework-6
Ricky
Ricky

Reputation: 3072

Linq: Select Most Recent Record of Each Group

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.

Upvotes: 4

Views: 6079

Answers (1)

Gilang
Gilang

Reputation: 311

 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.

Upvotes: 6

Related Questions