LINQ: Join conditionally to two different tables depending on each item

Question

Is there any way to set if in a linq statement?

return(from x in db.products where x.id == id 
    if(x.type == 1){
        join y in db.category1 on x.idItem equals y.id
    }else if(x.type == 2){
        join z in db.category2 on x.idItem equals z.id
    }
select New {....}).ToList();

I know this code is wrong but my question is:

What's the best way to implement this?

Answer 1

Note, that the following does not solve the problem that the OP is having because the join predicate depends on each item. The following helps if the condition is known for the entire query at once:

---

You split the query:

var part1 = from x in db.products where x.id == id select x;
var part2 =
 b ? (from x in part1 join db.category1 select { x, joinedItem }) :
 (from x in part1 join db.category2 select { x, joinedItem });

Quickly written up. You need to make the anonymous types on both queries compatible. That's the only important thing.

Answer 2

You could do a LEFT JOIN and one of the conditions of the LEFT JOIN could be the condition you have in the IF clause. So, you always do all the LEFT JOINs but they will only return results when the condition you have in the IF cluase is true.

Another way, with much better performance, is to create a Stored Procedure and call it from EF.