LINQ Group by query is not giving correct result using C#-CodePudding

I have SQL table data like below

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

I have c# entity to assign total values

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

}

My LINQ query which not working what I need

  public List<RecruiterDivisionWiseDasboardCounts> GetUsersTroveByDivisionWiseCandidateStatusCount(int createdBY)
    {

        RecruiterDivisionWiseDasboardFactory RWPD =new RecruiterDivisionWiseDasboardFactory();
        List<RecruiterDivisionWiseDasboardCounts> list_WorkingPendingRecruiterDasboardCounts = new List<RecruiterDivisionWiseDasboardCounts>();
        List<RecruiterDivisionWiseDasboardCounts> lst=RWPD.GetRelatedObjects("GetUsersTroveByDivisionWiseCandidateStatusCount",createdBY).Select(m => (RecruiterDivisionWiseDasboardCounts)m).ToList();
       
        list_WorkingPendingRecruiterDasboardCounts.AddRange(lst.GroupBy(mc => mc.candidateStatus.ToUpper()).Select(g => new RecruiterDivisionWiseDasboardCounts()
        {
                 candidateStatus = g.Key,      
            CDIPackaging = g.Where(c => c.Name.ToLower() == "cdi" && c.candidateStatus.ToUpper() == "PACKAGING").Sum(c => c.total),
            HIMPackaging = g.Where(c => c.Name.ToLower() == "him" && c.candidateStatus.ToUpper() == "PACKAGING").Sum(c => c.total),
            CMURPackaging = g.Where(c => c.Name.ToLower() == "cmur" && c.candidateStatus.ToUpper() == "PACKAGING").Sum(c => c.total),
            HITPackaging = g.Where(c => c.Name.ToLower() == "hit" && c.candidateStatus.ToUpper() == "PACKAGING").Sum(c => c.total),
            TraumaPackaging = g.Where(c => c.Name.ToLower() == "trauma" && c.candidateStatus.ToUpper() == "PACKAGING").Sum(c => c.total),
            BMTPackaging = g.Where(c => c.Name.ToLower() == "bmt" && c.candidateStatus.ToUpper() == "PACKAGING").Sum(c => c.total),
            ODMPackaging = g.Where(c => c.Name.ToLower() == "odm" && c.candidateStatus.ToUpper() == "PACKAGING").Sum(c => c.total),
            TotalPackaging = g.Where(c => c.candidateStatus.ToUpper() == "PACKAGING").Sum(c => c.total),
           
            //FULLY PACKAGED    
            CDIFullypackaged = g.Where(c => c.Name.ToLower() == "cdi" && c.candidateStatus.ToUpper() == "FULLY PACKAGED").Sum(c => c.total),
            HIMFullypackaged = g.Where(c => c.Name.ToLower() == "him" && c.candidateStatus.ToUpper() == "FULLY PACKAGED").Sum(c => c.total),
            CMURFullypackaged = g.Where(c => c.Name.ToLower() == "cmur" && c.candidateStatus.ToUpper() == "FULLY PACKAGED").Sum(c => c.total),
            HITFullypackaged = g.Where(c => c.Name.ToLower() == "hit" && c.candidateStatus.ToUpper() == "FULLY PACKAGED").Sum(c => c.total),
            TraumaFullypackaged = g.Where(c => c.Name.ToLower() == "trauma" && c.candidateStatus.ToUpper() == "FULLY PACKAGED").Sum(c => c.total),
            BMTFullypackaged = g.Where(c => c.Name.ToLower() == "bmt" && c.candidateStatus.ToUpper() == "FULLY PACKAGED").Sum(c => c.total),
            ODMFullypackaged = g.Where(c => c.Name.ToLower() == "odm" && c.candidateStatus.ToUpper() == "FULLY PACKAGED").Sum(c => c.total),
            TotalFullypackaged = g.Where(c => c.candidateStatus.ToUpper() == "FULLY PACKAGED").Sum(c => c.total),             
           
        }));

        return list_WorkingPendingRecruiterDasboardCounts;
    }

I need to return list with single row but this list return multiple list_WorkingPendingRecruiterDasboardCounts

I want to return single row of list in linq query output should like below single assigning of entity

I want output like below to assign values to entity to return single array to display this value into labels

    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

My linq query is not working to return like above data.

CodePudding user response：

Based on the description there is no need in group be here:

list_WorkingPendingRecruiterDasboardCounts.Add(new RecruiterDivisionWiseDasboardCounts()
{
    CDIPackaging = lst.Where(c => c.Name.ToLower() == "cdi" && c.candidateStatus.ToUpper() == "PACKAGING")
        .Sum(c => c.total),
    HIMPackaging = lst.Where(c => c.Name.ToLower() == "him" && c.candidateStatus.ToUpper() == "PACKAGING")
        .Sum(c => c.total),
    CMURPackaging = lst.Where(c => c.Name.ToLower() == "cmur" && c.candidateStatus.ToUpper() == "PACKAGING")
        .Sum(c => c.total),
    HITPackaging = lst.Where(c => c.Name.ToLower() == "hit" && c.candidateStatus.ToUpper() == "PACKAGING")
        .Sum(c => c.total),
    TraumaPackaging = lst.Where(c => c.Name.ToLower() == "trauma" && c.candidateStatus.ToUpper() == "PACKAGING")
        .Sum(c => c.total),
    BMTPackaging = lst.Where(c => c.Name.ToLower() == "bmt" && c.candidateStatus.ToUpper() == "PACKAGING")
        .Sum(c => c.total),
    ODMPackaging = lst.Where(c => c.Name.ToLower() == "odm" && c.candidateStatus.ToUpper() == "PACKAGING")
        .Sum(c => c.total),
    TotalPackaging = lst.Where(c => c.candidateStatus.ToUpper() == "PACKAGING").Sum(c => c.total),

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