I want to use Linq to retrieve the most recent entry for each group from a SQL Server table.
a sample table
I wish to achieve the following:
One record is returned by my Linq query for each company, but not 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 overlooking here? Why is the NextPaymentDate not arranged properly?
!!UPDATE!! My query is functioning as intended. I conducted more testing after analyzing @Gilang and @JonSkeet's remarks and discovered that the reason I wasn't obtaining the desired results was because a column wasn't being updated.