Linq to entities Left Join

left-join, linq, linq-to-entities, outer-join

Solution

Do this:

IQueryable<Enquiry> query = Context.EnquirySet; 

query = (from e in query 
         where (!e.Applications.Any()) 
               || e.Applications.Any(app => app.Status != 4)
         select e);

I don't find LINQ's handling of the problem of what would be an "outer join" in SQL "goofy" at all. The key to understanding it is to think in terms of an object graph with nullable properties rather than a tabular result set.

Any() maps to EXISTS in SQL, so it's far more efficient than Count() in some cases.

Problem

I want to achieve the following in Linq to Entities: Get all Enquires that have no Application or the Application has a status != 4 (Completed) ``` select e.* from Enquiry enq left outer join Application app on enq.enquiryid = app.enquiryid where app.Status <> 4 or app.enquiryid is null ``` Has anyone done this before without using DefaultIfEmpty(), which is not supported by Linq to Entities? I'm trying to add a filter to an IQueryable query like this: ``` IQueryable<Enquiry> query = Context.EnquirySet; query = (from e in query where e.Applications.DefaultIfEmpty() .Where(app=>app.Status != 4).Count() >= 1 select e); ``` Thanks Mark

Original source