内部连接的实体框架查询

问题是什么:

select s.* from Service s
inner join ServiceAssignment sa on sa.ServiceId = s.Id
where  sa.LocationId = 1

在实体框架内？

这是我写的:

 var serv = (from s in db.Services
join sl in Location on s.id equals sl.id
where sl.id = s.id
select s).ToList();

但这是不对的，有人能指引我吗？

213542 次浏览
小开
最佳答案 
from s in db.Services
join sa in db.ServiceAssignments on s.Id equals sa.ServiceId
where sa.LocationId == 1
select s

其中 db是您的 DbContext。生成的查询将类似于(EF6的示例) :

SELECT [Extent1].[Id] AS [Id]
-- other fields from Services table
FROM [dbo].[Services] AS [Extent1]
INNER JOIN [dbo].[ServiceAssignments] AS [Extent2]
ON [Extent1].[Id] = [Extent2].[ServiceId]
WHERE [Extent2].[LocationId] = 1
小开

如果导航属性可用，您可以使用它。

from s in db.Services
where s.ServiceAssignment.LocationId == 1
select s
小开

如果有人对 Method 语法感兴趣的话，如果你有一个导航属性，那就非常简单了:

db.Services.Where(s=>s.ServiceAssignment.LocationId == 1);

如果你没有，除非有一些我不知道的 Join()覆盖，我认为它看起来相当粗糙(我是一个方法语法纯粹主义者) :

db.Services.Join(db.ServiceAssignments,
s => s.Id,
sa => sa.ServiceId,
(s, sa) => new {service = s, asgnmt = sa})
.Where(ssa => ssa.asgnmt.LocationId == 1)
.Select(ssa => ssa.service);

提交答案