LINQ to EF left join with multiple condition

Question

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 1

Try something like this:

var access = from role in Role
             join oq in (from q in QueueAccess
                         where q.queueId = 361
                         select q) on role.RoleId equals queue.RoleId into oqs
             from queue in oqs.DefaultIfEmpty()
             select new { role.RoleId, queue.Property };

Answer 2

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();