linq-to-entities - LINQ to EF left join with multiple conditions

Question

我正在尝试使用 LINQ to EF 复制以下 SQL，但没有成功。

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

这是我尝试过的。

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

也试过这个。

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

我一直只获取具有指定 queueId 的记录，但没有获取 queueId 为空的其他记录。

感谢您的帮助。

Answer 1

It's nearly always a mistake to use join in LINQ to Entities .相反，做:

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