How to query 2 lists with join on multiple columns

Question

I have 2 lists in my c# code code.

The first list has following structure

List<ObjectA>

Where ObjectA is class of following structure

public class ObjectA
{
    public string ID { get; set; }
    public string EId1 { get; set; }
    public string EId2 { get; set; }
    public string EId3 { get; set; }
    public string EId4 { get; set; }
}

The second list has following structure

 List<ObjectB>

where ObjectB is class of following structure

public class objectB
{
    public string ID{get;set;}
    public string Name{get;set;}
}

I can do a join on a single column using the following query

var finalList= from objA in objectAList
               join objB in objectBList
               on objA.EId1 equals objB.ID
               select new
               {
                    Id = objA.ID,
                    EId = objB.Id,
                    Name = objB.Name    
               };

This works fine. However i need to check if the Id from the second list matches any of the 4 columns(Eid1, Eid2, Eid3 , Eid4) from the first list . How do i do that?

If you have control over the objects' design, this is bad design. Are you mapping a relation in a database here by chance? — Thierry Prost, May 03 '19 at 12:28
Check this: https://stackoverflow.com/questions/3944803/use-linq-to-get-items-in-one-list-that-are-not-in-another-list — Divya Agrawal, May 03 '19 at 12:29

score 3 · Answer 1 · answered May 03 '19 at 12:39

You mean like this? :

       var finalList= from objA in objectAList
                      from objB in objectBList
                      where objA.EId1 == objB.Id 
                       || objA.EId2 == objB.Id 
                       || objA.EId3 == objB.Id 
                       || objA.EId4 == objB.Id 
                       select new
                       {
                             Id = objA.ID,
                             EId = objB.Id,
                             Name = objB.Name    
                       };

score 1 · Accepted Answer · answered May 03 '19 at 12:31

var finalList = 
    from objA in listA
    join objB in listB
    on objA.EId1 equals objB.ID
    where new string[] { objB.ID, objA.EId2, objA.EId3, objA.EId4 }.Contains(objA.ID)
    select new { Id = objA.ID, EId = objB.ID, Name = objB.Name };

This is what you're looking for. However, I think you've designed your objects have not been designed in an optimal way. If you have any control over them, I'd s

How to query 2 lists with join on multiple columns

2 Answers2