CODEWITHSUNDEEP
c#linq
Peter Smith
Peter Smith

Reputation: 5550

Linq query and potential self join/group by

Suffering sadly from brain fade. I have the following scenario:

void Main()
{
    List<CaseBase> caseList = new List<UserQuery.CaseBase>();
    caseList.Add(new CaseBase() {CaseID = 1, CaseSequence = 1, CaseStatus = 1});
    caseList.Add(new CaseBase() {CaseID = 1, CaseSequence = 2, CaseStatus = 2});
    caseList.Add(new CaseBase() {CaseID = 2, CaseSequence = 1, CaseStatus = 1});

    var cases = caseList.Where(x => new List<int> {2}.Contains(x.CaseStatus));
    
}

// Define other methods and classes here
public class CaseBase
{ 
    public int CaseID {get;set;}
    public int CaseSequence {get;set;}
    public int CaseStatus {get;set;}
}

Which returns the expected

CaseID CaseSequence CaseStatus
1 2 2

What I want are all cases with the same ID where one of them has a status of 2.

CaseID CaseSequence CaseStatus
1 1 1
1 2 2

Which should be simple but I'm struggling for a simple solution.

Upvotes: 0

Views: 41

Answers (2)

MarcG
MarcG

Reputation: 342

Or you might want to do it like this:

void Main()
{
    List<CaseBase> caseList = new List<UserQuery.CaseBase>();
    caseList.Add(new CaseBase() { CaseID = 1, CaseSequence = 1, CaseStatus = 1 });
    caseList.Add(new CaseBase() { CaseID = 1, CaseSequence = 2, CaseStatus = 2 });
    caseList.Add(new CaseBase() { CaseID = 2, CaseSequence = 1, CaseStatus = 1 });
    
    var cases = caseList.Where(x => new List<int> { 2 }.Contains(x.CaseStatus))
        .Join(caseList,x => x.CaseID,y => y.CaseID,(x,y) => new {x,y})
        .Select(z => z.y)
        .Dump();

}

Upvotes: 1

NetMage
NetMage

Reputation: 26926

There are a couple of ways to proceed:

  1. You can combine the cases by CaseID and select the matching groups and then break them apart:

    var cases = caseList
                .GroupBy(c => c.CaseID)
                .Where(cg => cg.Any(c => new List<int> { 2 }.Contains(c.CaseStatus)))
                .SelectMany(cg => cg);

  2. You can find the desired CaseIDs and then get all matching cases:

    var wantedCaseIDs = caseList
                        .Where(c => new List<int> { 2 }.Contains(c.CaseStatus))
                        .Select(c => c.CaseID)
                        .ToHashSet();
var cases = caseList.Where(c => wantedCaseIDs.Contains(c.CaseID));

Upvotes: 1

Related Questions