Geek Answers Handbook

Linq for Left Join Objects

I want to get the following in Linq to Entities:

Get all Requests that do not have an Application or Applications have 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 Linq does not support for Entities?

I am trying to add a filter to an IQueryable query, for example:

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

+5
source share
5 answers

Do it:

IQueryable<Enquiry> query = Context.EnquirySet; 

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

I do not find LINQ handling the problem of what would be an “outer join” in SQL “goofy” in general. The key to understanding this is to think in terms of a graph of objects with null properties, rather than with a tabular set of results.

Any() EXISTS SQL, , Count().

+6

EF 4.0+ LEFT JOIN :

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 
        }

SQL Server Profiler, , LEFT OUTER JOIN. , LEFT JOIN ( "Group Join" ) Linq-to-Entity, , self-join INNER JOIN - !

? , , MS, , , . LEFT JOIN Linq Group, SQL Profiler INNER JOIN. LEFT JOIN LAST Linq Group Join, SQL Profiler LEFT JOIN.

+10

, , . , .

IQueryable<Enquiry> query = Context.EnquirySet;

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

- , Linq goofy ( ) outers, DefaultIfEmpty().

, Linq-To-Entities IEnumerables, LEFT DefaultIfEmpty(). :

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.*;

, Linq-To-Entities, , raw Linq.

(: , - ... , , . . , , ?)

+1

, , - where , ( ), null, Entity Framework LEFT JOIN INNER JOIN.

, "from x in leftJoinedExtent" :

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 , , , LEFT JOIN.

+1

More articles:


All Articles