CODEWITHSUNDEEP
linqjoin
GilShalit
GilShalit

Reputation: 6493

Join in LINQ to SQL on multiple conditions

I have a join on three tables with more then one condition on the join. What I want to have is List<MyVM> MyList { get; set; } MyList = (from a in _context.Tab1 join b in _context.Tab2 on a.T1F1 equals b.T2F2 and b.T2F2 equals SomeValue join c in _context.Tab3 on a.T1F2 equals c.T3F1 orderby a.T1F3 select new MyVM() { P1 = a.T1F5, P2 = a.T1F6, P3= b.T2F4 P4 = c.T3F3 } ).ToList();

The statement compiles OK with ony one condition on the first join, but once I add the second one it complains.

In SQL this would be on a.T1F1 = b.T2F2 and b.T2F2 = SomeValue

BTW, all columns in the join clauses as well as SomeValue are of type int.

How can I have multiple conditions?

Upvotes: 0

Views: 45

Answers (1)

Florian
Florian

Reputation: 1481

Move your condition in the where clause :

MyList = (from a in _context.Tab1
    join b in _context.Tab2 on a.T1F1 equals b.T2F2 
    join c in _context.Tab3 on a.T1F2 equals c.T3F1
    where b.T2F2 == SomeValue
    orderby a.T1F3
    select new MyVM()
    {
        P1 = a.T1F5,
        P2 = a.T1F6,
        P3= b.T2F4
        P4 = c.T3F3
    }
).ToList();

If you want to avoid using where clause : 

MyList = (from a in _context.Tab1
    join b in _context.Tab2 on new { firstKey = a.T1F1, secondKey = SomeValue } equals new { firstKey = b.T2F2, secondKey = b.T2F2 }
    join c in _context.Tab3 on a.T1F2 equals c.T3F1
    orderby a.T1F3
    select new MyVM()
    {
        P1 = a.T1F5,
        P2 = a.T1F6,
        P3= b.T2F4
        P4 = c.T3F3
    }
).ToList();

Upvotes: 1

Related Questions