C#中LINQ GroupBy查询结果异常，需返回单条实体数据

现有SQL数据 #

total   candidateStatus       Name
5        FULLY PACKAGED       cdi
2        FULLY PACKAGED       him
5        FULLY PACKAGED       cmur
1        FULLY PACKAGED       hit
5        FULLY PACKAGED       trauma
7        FULLY PACKAGED       bmt
8        FULLY PACKAGED       odm
5        PACKAGING            cdi
1        PACKAGING            him
5        PACKAGING            cmur
8        PACKAGING            hit
5        PACKAGING            trauma
4        PACKAGING            bmt
1        PACKAGING            odm

C#实体类定义 #

public class RecruiterDivisionWiseDasboardCounts : AbstractDBObject
{
   //RecruiterDivisionWiseDasboardCounts
    public int total { get; set; }
    public string Name { get; set; }
    public string candidateStatus { get; set; }

    public int CDIPackaging { get; set; }
    public int HIMPackaging { get; set; }
    public int CMURPackaging { get; set; }
    public int HITPackaging { get; set; }
    public int TraumaPackaging { get; set; }
    public int BMTPackaging { get; set; }
    public int ODMPackaging { get; set; }
    
    
    public int CDIFullypackaged { get; set; }
    public int HIMFullypackaged { get; set; }
    public int CMURFullypackaged { get; set; }
    public int HITFullypackaged { get; set; }
    public int TraumaFullypackaged { get; set; }
    public int BMTFullypackaged { get; set; }
    public int ODMFullypackaged { get; set; }
}

当前问题 #

原LINQ查询按candidateStatus分组，导致返回两条数据（对应PACKAGING和FULLY PACKAGED两个分组），不符合需求。需要返回仅包含单条实体的List，将所有统计值合并到一个对象中。

预期赋值结果：

CDIFullypackaged=5
HIMFullypackaged=2
CMURFullypackaged=5
HITFullypackaged=1
TraumaFullypackaged=5
BMTFullypackaged=7
ODMFullypackaged=8
CDIPackaging=5
HIMPackaging=1
CMURPackaging=5
HITPackaging=8
TraumaPackaging=5
BMTPackaging=4
ODMPackaging=1

修正后的LINQ查询 #

public List<RecruiterDivisionWiseDasboardCounts> GetUsersTroveByDivisionWiseCandidateStatusCount(int createdBY)
{
    RecruiterDivisionWiseDasboardFactory RWPD = new RecruiterDivisionWiseDasboardFactory();
    List<RecruiterDivisionWiseDasboardCounts> lst = RWPD.GetRelatedObjects("GetUsersTroveByDivisionWiseCandidateStatusCount", createdBY)
        .Select(m => (RecruiterDivisionWiseDasboardCounts)m)
        .ToList();

    // 创建单个统计实体
    var singleResult = new RecruiterDivisionWiseDasboardCounts()
    {
        // PACKAGING 统计
        CDIPackaging = lst.Where(c => c.Name.Equals("cdi", StringComparison.OrdinalIgnoreCase) && 
                                      c.candidateStatus.Equals("PACKAGING", StringComparison.OrdinalIgnoreCase))
                          .Sum(c => c.total),
        HIMPackaging = lst.Where(c => c.Name.Equals("him", StringComparison.OrdinalIgnoreCase) && 
                                      c.candidateStatus.Equals("PACKAGING", StringComparison.OrdinalIgnoreCase))
                          .Sum(c => c.total),
        CMURPackaging = lst.Where(c => c.Name.Equals("cmur", StringComparison.OrdinalIgnoreCase) && 
                                       c.candidateStatus.Equals("PACKAGING", StringComparison.OrdinalIgnoreCase))
                           .Sum(c => c.total),
        HITPackaging = lst.Where(c => c.Name.Equals("hit", StringComparison.OrdinalIgnoreCase) && 
                                      c.candidateStatus.Equals("PACKAGING", StringComparison.OrdinalIgnoreCase))
                          .Sum(c => c.total),
        TraumaPackaging = lst.Where(c => c.Name.Equals("trauma", StringComparison.OrdinalIgnoreCase) && 
                                         c.candidateStatus.Equals("PACKAGING", StringComparison.OrdinalIgnoreCase))
                             .Sum(c => c.total),
        BMTPackaging = lst.Where(c => c.Name.Equals("bmt", StringComparison.OrdinalIgnoreCase) && 
                                      c.candidateStatus.Equals("PACKAGING", StringComparison.OrdinalIgnoreCase))
                          .Sum(c => c.total),
        ODMPackaging = lst.Where(c => c.Name.Equals("odm", StringComparison.OrdinalIgnoreCase) && 
                                      c.candidateStatus.Equals("PACKAGING", StringComparison.OrdinalIgnoreCase))
                          .Sum(c => c.total),

        // FULLY PACKAGED 统计
        CDIFullypackaged = lst.Where(c => c.Name.Equals("cdi", StringComparison.OrdinalIgnoreCase) && 
                                          c.candidateStatus.Equals("FULLY PACKAGED", StringComparison.OrdinalIgnoreCase))
                              .Sum(c => c.total),
        HIMFullypackaged = lst.Where(c => c.Name.Equals("him", StringComparison.OrdinalIgnoreCase) && 
                                          c.candidateStatus.Equals("FULLY PACKAGED", StringComparison.OrdinalIgnoreCase))
                              .Sum(c => c.total),
        CMURFullypackaged = lst.Where(c => c.Name.Equals("cmur", StringComparison.OrdinalIgnoreCase) && 
                                           c.candidateStatus.Equals("FULLY PACKAGED", StringComparison.OrdinalIgnoreCase))
                               .Sum(c => c.total),
        HITFullypackaged = lst.Where(c => c.Name.Equals("hit", StringComparison.OrdinalIgnoreCase) && 
                                          c.candidateStatus.Equals("FULLY PACKAGED", StringComparison.OrdinalIgnoreCase))
                              .Sum(c => c.total),
        TraumaFullypackaged = lst.Where(c => c.Name.Equals("trauma", StringComparison.OrdinalIgnoreCase) && 
                                             c.candidateStatus.Equals("FULLY PACKAGED", StringComparison.OrdinalIgnoreCase))
                                 .Sum(c => c.total),
        BMTFullypackaged = lst.Where(c => c.Name.Equals("bmt", StringComparison.OrdinalIgnoreCase) && 
                                          c.candidateStatus.Equals("FULLY PACKAGED", StringComparison.OrdinalIgnoreCase))
                              .Sum(c => c.total),
        ODMFullypackaged = lst.Where(c => c.Name.Equals("odm", StringComparison.OrdinalIgnoreCase) && 
                                          c.candidateStatus.Equals("FULLY PACKAGED", StringComparison.OrdinalIgnoreCase))
                              .Sum(c => c.total)
    };

    return new List<RecruiterDivisionWiseDasboardCounts> { singleResult };
}

关键修正点 #

1. 移除原代码中按candidateStatus分组的逻辑，直接基于整个数据集计算每个字段的总和
2. 使用StringComparison.OrdinalIgnoreCase替代手动转大小写，提升性能和可读性
3. 创建单个统计实体，将其放入List中返回，满足“仅包含单条数据的List”需求

内容的提问来源于stack exchange，提问作者coder rock