Linq to entities Left Join

Question

Hi all,

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

Answer 1

Because of Linq's goofy (read non-standard) way of handling outers, you have to use DefaultIfEmpty().

What you'll do is run your Linq-To-Entities query into two IEnumerables, then LEFT Join them using DefaultIfEmpty(). It may look something like:

IQueryable enq = Enquiry.Select();
IQueryable app = Application.Select();
var x = from e in enq
join a in app on e.enquiryid equals a.enquiryid
into ae
where e.Status != 4
from appEnq in ae.DefaultIfEmpty()
select e.*;

Just because you can't do it with Linq-To-Entities doesn't mean you can't do it with raw Linq.

(Note: before anyone downvotes me ... yes, I know there are more elegant ways to do this. I'm just trying to make it understandable. It's the concept that's important, right?)

Answer 2

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.

Answer 3

Thanks guys for your help. I went for this option in the end but your solutions have helped broaden my knowledge no end :-)

IQueryable<Enquiry> query = Context.EnquirySet;

query = query.Except(from e in query
                     from a in e.Applications
                     where a.Status == 4
                     select e);