LINQ:按聚合分组，但仍然从最近的行获取信息

本文关键字：最近 信息 获取 LINQ | 更新日期: 2023-09-27 18:10:17

假设我有一个保存发货历史记录的表。我想编写一个查询，计算每个用户的发货数量，并从表中该用户的最近条目中获取发货名称。

表结构简单:ShipmentIDMemberIDShippingNameShippingDate

我如何写一个LINQ c#查询来做到这一点?

LINQ:按聚合分组，但仍然从最近的行获取信息

听起来像是:

var query = from shipment in context.ShippingHistory
            group shipment by shipment.MemberID into g
            select new { Count = g.Count(),
                     MemberID = g.Key,
                     MostRecentName = g.OrderByDescending(x => x.ShipmentDate)
                                       .First()
                                       .ShipmentName };

不是真正的LINQ答案，但个人，我将下降到SQL，以确保它没有做任何N+1等;例如:

select s1.MemberID, COUNT(1) as [Count],
    (select top 1 ShippingName from Shipping s2 where s2.MemberID = s1.MemberID
     order by s2.ShippingDate desc) as [LastShippingName]
from Shipping s1
group by s1.MemberID

你可以这样做LINQ(未测试):

var qry = from row in data
          group row by row.MemberId into grp
          select new {
               MemberId = grp.Key,
               Count = grp.Count(),
               LastShippingName =
              grp.OrderByDescending(x => x.ShippingDate).First().ShippingName
           };
相关文章：