Linq outer join using inequality?

Question

In SQL, I would say:

select a.* from TableA a left join TableB b on a.Type = b.Type and a.SomeDate < b.AnotherDate where b.ID is null 

This will select all the records in table A, where in table B there is no record in the same Type and later date.

In Linq, how do you do this?

 from a in TableA join b in TableB on a.Type equals b.Type into j // what about the comparator? from x in j.DefaultIfEmpty() where x == null select a; 

Thanks!

EDIT:

Several good answers were suggested, all of which relate to the specific needs expressed in this question, but they are all basically workarounds. In one way or another, they all move to embedded "existing" queries, while the SQL in the question is one neat query without any nesting. The case given here is only an example of a general principle; what I really would like to see is a Linq expression that will translate the (roughly) syntax of the aforementioned SQL query.

Answer 1

 var results = TableA.Where(a => !TableB.Any(b => a.Type == b.Type && a.Date < b.Date)) 

If you want the linq query to be exactly the same as your SQL, you can write:

 var result = from a in TableA from b in TableB.Where(b => a.Type = b.Type && a.SomeDate < b.AnotherDate).DefaultIfEmpty() where b == null select a; 

But I would say that the first solution is better, since where b == null will result in a filter operation in the request.

Answer 2

Something like this should help:

 var results = (from itemA in TableA from itemB in TableB where itemA.Type != itemB.Type && itemA.Date < itemB.Date select itemA).Distinct(); 

Answer 3

 from a in tableA let rights = from b in tableB where a.Type == b.Type && a.Date < b.Date select b where !rights.Any() select a; 

It translates to:

 SELECT [t0].[Type] AS [Type], [t0].[SomeDate] AS [SomeDate] FROM [TableA] AS [t0] WHERE NOT (EXISTS( SELECT NULL AS [EMPTY] FROM [TableB] AS [t1] WHERE ([t0].[Type] = [t1].[Type]) AND ([t0].[SomeDate] < [t1].[AnotherDate])))