LINQ to EF left join with multiple condition

linq-to-entities left-join multiple-conditions
Joe picture Joe · Jan 29, 2010 · Viewed 9.6k times · Source

I am trying to replicate the following SQL using LINQ to EF but with no luck.

select * from Role
left join QueueAccess on Role.RoleId = QueueAccess.RoleId and queueId = 361

Here's what I've tried.

var myAccess = (from role in entity.Role.Include(p => p.QueueAccess)
join qa in entity.QueueAccess
on new { rID = role.RoleId, qID = queueId } equals new { rID = qa.RoleId, qID = qa.QueueId }
select role).ToList();

Also tried this.

var myAccess = entity.Role.Include(p => p.QueueAccess)
         .Where(x => x.QueueAccess.Any(a => a.QueueId == queueId)).ToList();

I keep on getting only the record with the specified queueId but none of the other records where the queueId is null.

Thanks for your help.

Answer

Craig Stuntz picture Craig Stuntz · Jan 29, 2010

It's nearly always a mistake to use join in LINQ to Entities. Instead, do:

var myAccess = (((ObjectQuery)from role in entity.Role
                              where role.QueueAccess.Any(a => a.QueueId == queueId)
                              select role).Include("QueueAccess")).ToList();

Related questions

Entity framework left join

How do I change this query so it returns all u.usergroups? from u in usergroups from p in u.UsergroupPrices select new UsergroupPricesList { UsergroupID = u.UsergroupID, UsergroupName = u.UsergroupName, Price = p.Price };

Read More
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 …

Read More
How to do SQL Like % in Linq?

I have a procedure in SQL that I am trying to turn into Linq: SELECT O.Id, O.Name as Organization FROM Organizations O JOIN OrganizationsHierarchy OH ON O.Id=OH.OrganizationsId where OH.Hierarchy like '%/12/%' The line …

Read More