Linq:选择每个组的最新记录 [英] Linq: Select Most Recent Record of Each Group

问题描述

我想使用Linq从SQL Server表中获取每个组的最新记录.

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

表格示例:

我想得到这个结果:

我的Linq查询为每个公司返回一条记录，但不返回最新记录:

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();

我在这里想念什么?为什么不能正确订购NextPaymentDate?

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

!!更新!! 我的查询正在按预期方式工作.在分析了@Gilang和@JonSkeet的评论之后，我进行了进一步的测试，结果发现由于未更新列，我没有得到预期的结果.

!!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.

推荐答案

 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
             };

您的查询未正确排序的原因是您的查询未进行正确的分组.您已按CompanyID正确分组，但随后必须通过调用聚合函数来检索最大的NextPaymentDate.

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.

可以将状态分配为false，因为早期条款中的Where子句已经过滤了状态.

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

这篇关于Linq:选择每个组的最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！