Join in LINQ to SQL on multiple conditions

Question

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?

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) could help you? — NetMage, Nov 19 '18 at 21:05

Florian · Accepted Answer · 2018-11-19T12:45:00.837

1

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();

edited Nov 19 '18 at 12:45

answered Nov 19 '18 at 08:34

Florian

1,473
10
15

Thanks for the swift response. Are there implications in terms of performance to going in either direction? I am using SQLServer, from a Core 2.1 application. – GilShalit Nov 19 '18 at 09:01
I am a little confused. are a and b in the second solution the same a and b as those in the join clauses? what is tfa? should it be `new {m=a.T1F2, n=b.T2F2} equals {m=b.T2F2, n=SomeValue}` ? – GilShalit Nov 19 '18 at 09:23
using where clause can perform a **cartesian product** (like *cross join*). I'm not an expert but I'd say that in this special case, the performance shouldn't be impacted that much (you could create a test method and run tests on both so you can see if there is an impact). – Florian Nov 19 '18 at 12:16
Oopsie, that's a typo, it should be `a.T1F1` instead of `tfa`. `a` and `b` are the name of your object : `new { *propertyname*=value } equals new { *propertyname*=value }`, you should give an explicit name instead of `a` and `b` – Florian Nov 19 '18 at 12:18

Join in LINQ to SQL on multiple conditions

1 Answers1