CODEWITHSUNDEEP
linqlinq-to-entitiesleft-joinouter-join
Mark
Mark

Reputation: 468

Linq to entities Left Join

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

Upvotes: 5

Views: 11301

Answers (5)

K0D4
K0D4

Reputation: 2603

Another thing to consider, if you directly reference any properties in your where clause from a left-joined group (using the into syntax) without checking for null, Entity Framework will still convert your LEFT JOIN into an INNER JOIN.

To avoid this, filter on the "from x in leftJoinedExtent" part of your query like so:

var y = from parent in thing
        join child in subthing on parent.ID equals child.ParentID into childTemp
        from childLJ in childTemp.Where(c => c.Visible == true).DefaultIfEmpty()
        where parent.ID == 123
        select new {
            ParentID = parent.ID,
            ChildID = childLJ.ID
        };

ChildID in the anonymous type will be a nullable type and the query this generates will be a LEFT JOIN.

Upvotes: 1

Mark Duff
Mark Duff

Reputation: 111

In EF 4.0+, LEFT JOIN syntax is a little different and presents a crazy quirk:

var query = from c1 in db.Category 
        join c2 in db.Category on c1.CategoryID equals c2.ParentCategoryID  
        into ChildCategory 
        from cc in ChildCategory.DefaultIfEmpty() 
        select new CategoryObject  
        { 
            CategoryID = c1.CategoryID,  
            ChildName = cc.CategoryName 
        }

If you capture the execution of this query in SQL Server Profiler, you will see that it does indeed perform a LEFT OUTER JOIN. HOWEVER, if you have multiple LEFT JOIN ("Group Join") clauses in your Linq-to-Entity query, I have found that the self-join clause MAY actually execute as in INNER JOIN - EVEN IF THE ABOVE SYNTAX IS USED!

The resolution to that? As crazy and, according to MS, wrong as it sounds, I resolved this by changing the order of the join clauses. If the self-referencing LEFT JOIN clause was the 1st Linq Group Join, SQL Profiler reported an INNER JOIN. If the self-referencing LEFT JOIN clause was the LAST Linq Group Join, SQL Profiler reported an LEFT JOIN.

Upvotes: 10

Mark
Mark

Reputation: 468

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

IQueryable<Enquiry> query = Context.EnquirySet;

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

Upvotes: 3

Craig Stuntz
Craig Stuntz

Reputation: 126547

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.

Upvotes: 6

Rap
Rap

Reputation: 7282

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

Upvotes: 1

Related Questions