How to write LINQ for multiple joins involving self join?

Question

I am trying to translate the following query with self join to a LINQ expression.

select r2.* from depends d
  join request r on d.DESC =r.DESC
  join request r2 on d.ID=r2.ID
  and d.TYPE ='sometype'
where r.ID= 12345

How can I correct the following LINQ query to match the correct SQL query above?

var result = (from d in depends
            join r in request on d.DESC equals r.DESC
            join r2 in request on d.ID == r2.ID && d.TYPE == incomingType.ToString()
            where r.ID == incomingId
            select r2).AsEnumerable();

First, there is no self join in the SQL query, just two joins to the one and the same table. Second, the SQL query is strange and inconsistent, both `d.TYPE='sometype'` and `r.ID=12345` conditions involve a single table, hence should be simply put in a `where` clause. — Ivan Stoev, Sep 19 '16 at 07:50

score 1 · Answer 1 · edited May 23 '17 at 11:51

Why don't you simple use a Where clause if your incomingType is fixed?

Also there is syntax for multiple join conditions, as shown here: LINQ Joining in C# with multiple conditions

E.g.:

var result = (from d in depends
              where d.TYPE == incomingType.ToString()
              join r in request on
              new { ID = r.ID, desc = r.DESC }
              equals 
              new { ID = d.ID, desc = d.DESC }
              where r.ID == incomingId
              select r).AsEnumerable();

If your incomingType is not actually fixed, and it gets its value from the depends table, you can just add a third parameter to the join condition, e.g.

var result = (from d in depends
              where d.TYPE == incomingType.ToString()
              join r in request on
              new { ID = r.ID, desc = r.DESC, type = r.someType1 }
              equals 
              new { ID = d.ID, desc = d.DESC, type = d.someType2 }
              where r.ID == incomingId
              select r).AsEnumerable();

How to write LINQ for multiple joins involving self join?

1 Answers1

Linked