LINQ: conditionally join two different tables depending on each element

Question

Is there a way to install 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 is the best way to implement this?

Answer 1

Note that the following does not solve the problem that the OP has, since the join predicate depends on each element. The following helps if the condition is known for the entire query immediately:

---

You shared the request:

 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 recorded. You need to make anonymous types compatible for both queries. This is the only important thing.

Answer 2

You can make a LEFT JOIN, and one of the conditions of the LEFT JOIN may be the condition that you have in the IF clause. That way, you always do all LEFT JOINS, but they will only return results when the condition you have in the IF cluase is true.

Another way, with much greater performance, is to create a stored procedure and call it from EF.