LINQ deletes items with a specific value in a group by

Question

I have code where I group my requirements by feature number, as shown below:

        List<Requirement> result = requirements
                                    .GroupBy(l => l.CRMOpportunityNumber)
                                    .Select(cl => new Requirement
                                    {
                                        CRMOpportunityNumber = cl.First().CRMOpportunityNumber,
                                        OpportunityTitle = cl.First().OpportunityTitle,
                                        ClientName = cl.First().ClientName,
                                        TentativeStartDate = cl.Min(c => c.TentativeStartDate),
                                        TotalPositions = cl.Sum(c => c.Required),
                                        RegionName = cl.First().RegionName,
                                        TotalCVProposed = cl.Sum(c => c.Associates.Count),
                                        TotalDeployed = cl.Sum(c => c.Associates.Count(x => x.IsIdentified == true)),
                                        NetGap = cl.Sum(c => c.Required) - cl.Sum(c => c.Associates.Count(x => x.IsIdentified == true)),
                                    }).OrderByDescending(l => l.CRMOpportunityNumber).ToList();

Now I do not want to include these requirements in the grouping (result), for which all CATEGORY field values ​​are equal to "work has begun."

The requirements class has a field category.

Answer 1

Exclude the Job Started category using the Where clause before grouping

    List<Requirement> result = requirements
                                .Where(r => r.Category != "work commenced")
                                .GroupBy(l => l.CRMOpportunityNumber)
                                .Select(cl => new Requirement
                                {
                                    CRMOpportunityNumber = cl.First().CRMOpportunityNumber,
                                    OpportunityTitle = cl.First().OpportunityTitle,
                                    ClientName = cl.First().ClientName,
                                    TentativeStartDate = cl.Min(c => c.TentativeStartDate),
                                    TotalPositions = cl.Sum(c => c.Required),
                                    RegionName = cl.First().RegionName,
                                    TotalCVProposed = cl.Sum(c => c.Associates.Count),
                                    TotalDeployed = cl.Sum(c => c.Associates.Count(x => x.IsIdentified == true)),
                                    NetGap = cl.Sum(c => c.Required) - cl.Sum(c => c.Associates.Count(x => x.IsIdentified == true)),
                                }).OrderByDescending(l => l.CRMOpportunityNumber).ToList();

Edit

If you want to exclude groups in which all requirements have the category “work started”, how about:

List<Requirement> result = requirements
    .GroupBy(l => l.CRMOpportunityNumber)
    .Where(cl => cl.All(l => l.Category != "work commenced"))
    .Select(cl => new Requirement
    {
        CRMOpportunityNumber = cl.First().CRMOpportunityNumber,
        OpportunityTitle = cl.First().OpportunityTitle,
        ClientName = cl.First().ClientName,
        TentativeStartDate = cl.Min(c => c.TentativeStartDate),
        TotalPositions = cl.Sum(c => c.Required),
        RegionName = cl.First().RegionName,
        TotalCVProposed = cl.Sum(c => c.Associates.Count),
        TotalDeployed = cl.Sum(c => c.Associates.Count(x => x.IsIdentified == true)),
        NetGap = cl.Sum(c => c.Required) - cl.Sum(c => c.Associates.Count(x => x.IsIdentified == true))
    }).OrderByDescending(l => l.CRMOpportunityNumber)
    .ToList();

Answer 2

, CATEGORY, " ". , - , " ", . LINQ Any() :

List<Requirement> result = 
                    requirements.GroupBy(l => l.CRMOpportunityNumber)
                                .Where(g => g.Any(item => item.Category != "work commenced")
                                .Select(cl => new Requirement
                                {
                                    ......
                                }).OrderByDescending(l => l.CRMOpportunityNumber)
                                .ToList();