Greater Than Condition in Linq Join

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;

Solution 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));

Solution 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).

Solution 3:

var currentDetails = from c in customers
                  group c by new { c.Name, c.Authed } into g
                  where g.Key.Authed == "True"
                  select g.OrderByDescending(t => t.EffectiveDate).First();

var currentAndUnauthorised = (from c in customers
                           join cd in currentDetails
                           on c.Name equals cd.Name
                           where c.EffectiveDate >= cd.EffectiveDate
                           select c).OrderBy(o => o.CoverId).ThenBy(o => o.EffectiveDate);

If you have a table of historic detail changes including authorisation status and effective date. The first query finds each customers current details and the second query adds all subsequent unauthorised detail changes in the table.

Hope this is helpful as it took me some time and help to get too.