How to write a LEFT OUTER JOIN where the right side is null in LINQ to SQL?

Question

How do you write a LEFT OUTER JOIN in LINQ to SQL, where the right side is null?

A graphical representation of the result I want would be this:

Image credits Jeff Atwood.

Take this SQL for example:

select Document.*
from Document left outer join Invoice
     on Document.DocumentId = Invoice.DocumentId
where Invoice.DocumentId is null

Basically I want all documents that are not invoices but some other kind of document, doesn't matter what.

I would greatly appreciate examples in both LINQ Query Syntax and LINQ Method (Fluent) Syntax.

Thank you!

Answer 1

Firstly, even in SQL that query should really be a not exists, it is generally more efficient than the left join / is null construct.

The compiler also understands exists better, as it understands that the not exists cannot add more rows to the resultset, so it can keep any uniqueness guarantee that may be there. The compiler does not see that left join with an is null check cannot add rows (perhaps it should, but there is no logic currently built into it to do so).

select Document.*
from Document
where not exists (select 1
    from Invoice
    where Document.DocumentId = Invoice.DocumentId);

Now it's obvious how to do it in Linq:

var docs =
    from doc in Documents
    where !Invoices.Any(inv => doc.DocumentId == inv.DocumentId);

var docs =
    Documents
    .Where(doc => !Invoices.Any(inv => doc.DocumentId == inv.DocumentId));