C# 如何使用LINQ获取最大父ID对应子项数量并按状态统计

问题原因 #

代码存在两处核心逻辑错误，导致统计结果不符合预期：

1. 子查询Status取值逻辑错误：按RfqId分组后，直接取了分组第一条记录的Status作为该分组的StatusId，如果需要的是该分组下最大Id（最新事件）对应的Status，这个取值逻辑是错误的。
2. 二次查询逻辑冗余且不一致：后续统计时又从原列表匹配最大事件Id对应的记录取Status，会出现子查询里存储的StatusId和实际要统计的记录Status不一致的问题，同时也增加了不必要的查询开销。

修正方案 #

直接在子查询中拿到每个RfqId对应的最新完整事件记录，后续统计直接基于子查询结果分组即可，修正后的代码如下：

using System;
using System.Collections.Generic;
using System.Linq;

public class Program
{
    public static void Main()
    {
        var lstRfq = new List<RfqEvents>()
        {
            new RfqEvents(1,1,DateTime.Parse("2021-05-06 03:00:00+00"),1),
            new RfqEvents(2,2,DateTime.Parse("2021-05-06 03:00:00+00"),1),
            new RfqEvents(3,2,DateTime.Parse("2021-05-06 03:00:00+00"),1),
            new RfqEvents(4,3,DateTime.Parse("2021-05-06 00:00:00+00"),2),
            new RfqEvents(5,4,DateTime.Parse("2021-05-06 00:00:00+00"),2),
            new RfqEvents(6,5,DateTime.Parse("2021-05-06 00:00:00+00"),2),
            new RfqEvents(7,5,DateTime.Parse("2021-05-06 00:00:00+00"),2),
            new RfqEvents(8,5,DateTime.Parse("2021-05-06 00:00:00+00"),3),
            new RfqEvents(9,6,DateTime.Parse("2021-05-06 00:00:00+00"),3),
            new RfqEvents(10,6,DateTime.Parse("2021-05-06 00:00:00+00"),3),
        };

        // 子查询：取每个RfqId对应的最新事件记录
        var subquery = from c in lstRfq
                       group c by c.RfqId into g
                       let latestEvent = g.OrderByDescending(x => x.Id).First()
                       select new InternalStatusInformations
                       {
                           RfqId = g.Key,
                           RfqEventId = latestEvent.Id,
                           StatusId = latestEvent.Status,
                           DueDate = latestEvent.DueDate
                       };
        
        // 直接基于子查询结果按Status分组统计
        var sss = from d in subquery
                  group d by d.StatusId into z
                  select new InternalStatusInformations
                  {
                      StatusId = z.Key,
                      Total = z.Count(),
                      Past = z.Where(p => p.DueDate.HasValue && p.DueDate.Value.Date < DateTime.Now.Date).Count(),
                      Future = z.Where(p => p.DueDate.HasValue && p.DueDate.Value.Date > DateTime.Now.Date).Count(),
                      Today = z.Where(p => p.DueDate.HasValue && p.DueDate.Value.Date == DateTime.Now.Date).Count(),
                      FiveDays = z.Where(p => p.DueDate.HasValue && p.DueDate.Value.Date > DateTime.Now.Date && p.DueDate.Value.Date < DateTime.Now.Date.AddDays(5)).Count(),
                  };
        
        sss.Dump();
    }

    public class InternalStatusInformations
    {
        public int RfqEventId { get; set; }
        public int RfqId { get; set; }
        public int StatusId { get; set; }
        public int Future { get; set; }
        public int Past { get; set; }
        public int Today { get; set; }
        public int FiveDays { get; set; }
        public int Total { get; set; }
        public DateTime? DueDate { get; set; }
    }

    public class RfqEvents
    {
        public RfqEvents(int id, int rfqId, DateTime? dueDate, int status)
        {
            Id = id;
            RfqId = rfqId;
            DueDate = dueDate;
            Status = status;
        }
        public int Id { get; set; }
        public DateTime? DueDate { get; set; }
        public int RfqId { get; set; }
        public int Status { get; set; }
    }
}

如果统计规则是按每个RfqId第一条记录的Status统计，只需要把子查询里的OrderByDescending(x => x.Id).First()改成OrderBy(x => x.Id).First()即可，后续直接用子查询结果统计就能得到你写的预期结果。

内容的提问来源于stack exchange，提问作者Paulo Campez