Greater Than Condition in Linq Join

Question

I had tried to join two table conditionally but it is giving me syntax error. I tried to find solution in the net but i cannot find how to do conditional join with condition. The only other alternative is to get the value first from one table and make a query again.

I just want to confirm if there is any other way to do conditional join with linq.

Here is my code, I am trying to find all position that is equal or lower than me. Basically I want to get my peers and subordinates.

from e in entity.M_Employee join p in entity.M_Position on e.PostionId >= p.PositionId select p; 

Answer 1

You can't do that with a LINQ joins - LINQ only supports equijoins. However, you can do this:

var query = from e in entity.M_Employee             from p in entity.M_Position             where e.PostionId >= p.PositionId             select p; 

Or a slightly alternative but equivalent approach:

var query = entity.M_Employee                   .SelectMany(e => entity.M_Position                                       .Where(p => e.PostionId >= p.PositionId)); 

Answer 2

Following:

from e in entity.M_Employee from p in entity.M_Position.Where(p => e.PostionId >= p.PositionId) select p; 

will produce exactly the same SQL you are after (INNER JOIN Position P ON E..PostionId >= P.PositionId).