Linq outer join using inequality?

In SQL I'd 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 would select all records in TableA where no record exists in TableB of the same Type and later date.

开发者_JAVA技巧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:

A few good answers have been proposed, all of which address the specific need expressed in this question, but they're all basically workarounds. They all translate to a nested "exists" queries in one way or another, whereas the SQL in the question is one neat query without any nesting. The case given here is just an example of a general principle; what I'd really like to see is a Linq expression that will translate to (roughly) the syntax of the above SQL query.

Something like this ought to help:

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

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's translated into:

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])))

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 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 as the where b == null would result in a filter operation in the queryplan.