CODEWITHSUNDEEP
c#linqlinq-to-sql
Naupad Doshi
Naupad Doshi

Reputation: 496

LINQ JOIN different tables based on conditions

How can I join two different tables based on a condition?

I have my query as under:

var myquery = from p in db.tbl1
              join q in db.tbl2 on p.field1 equals q.field1
              join r in db.tbl3 on q.field2 equals r.field2

Till here everything is fine, now I want to add 1 more join to a table but it should be based on a condition like:

if(q.field3 == 1)
    join s in db.tbl4 on q.field4 equals s.field4
 else if(q.field3 == 2)
    join s in db.tbl5 on ....

So basically I want to join to different tables based on the value of q.field3.

Upvotes: 2

Views: 2396

Answers (1)

Servy
Servy

Reputation: 203816

You're not going to be able to conditionally join based on the value of a single row. The idea of joining is that you're joining based off of all of the rows. Conditionally determining what/how to join based on some value outside of the query would make sense.

What you can do is do both joins unconditionally, but choose which result to use conditionally for each row. Obviously this will only work if the tables are of the same type, or if you first project s1 and s2 into a common type (using let)

var myquery = from p in db.tbl1
    join q in db.tbl2 on p.field1 equals q.field1
    join r in db.tbl3 on q.field2 equals r.field2
    join s1 in db.tbl4 on q.field4 equals s1.field4
    join s2 in db.tbl5 on q.field5 equals s2.field5
    let s = q.field3 == 1 ? s1 : 
            q.field3 == 2 ? s2 : null

This should be able to be translated by the query provider into a CASE statement.

Upvotes: 2

Related Questions