我想EF出来，我做了很多过滤基于多对多的关系。例如我的人，地点和一个personlocation表链接两个。我也有一定的作用，并personrole表。

I'm trying EF out and I do a lot of filtering based on many to many relationships. For instance I have persons, locations and a personlocation table to link the two. I also have a role and personrole table.

EDIT: Tables:

Person (personid, name)

Personlocation (personid, locationid)

Location (locationid, description)

Personrole (personid, roleid)

Role (roleid, description)

EF给我的人，角色和位置的实体。编辑：既然EF将不会生成的personlocation和personrole实体类型，它们不能在查询中使用

EF will give me persons, roles and location entities. Since EF will NOT generate the personlocation and personrole entity types, they cannot be used in the query.

如何创建一个查询，以给我一个给定的位置，所有的人有一个给定的角色？

How do I create a query to give me all the persons of a given location with a given role?

在SQL查询是

select p.*
from persons as p
join personlocations as pl on p.personid=pl.personid
join locations       as l  on pl.locationid=l.locationid
join personroles     as pr on p.personid=pr.personid
join roles           as r  on pr.roleid=r.roleid
where r.description='Student' and l.description='Amsterdam'

我已经看了，但是我似乎无法找到一个简单的解决方案。

推荐答案

在LAMBDA：

    var persons = Persons.Where(p=>(p.PersonLocations.Select(ps=>ps.Location)
   .Where(l=>l.Description == "Amsterdam").Count() > 0)
    && (p.PersonRoles.Select(pr=>pr.Role)
   .Where(r=>r.Description == "Student").Count() > 0));

查询结果：

SELECT [t0].[personId] AS [PersonId], [t0].[description] AS [Description]
FROM [Persons] AS [t0]
WHERE (((
    SELECT COUNT(*)
    FROM [personlocations] AS [t1]
    INNER JOIN [Locations] AS [t2] ON [t2].[locationid] = [t1].[locationid]
    WHERE ([t2].[description] = @p0) AND ([t1].[personid] = [t0].[personId])
    )) > @p1) AND (((
    SELECT COUNT(*)
    FROM [PersonRoles] AS [t3]
    INNER JOIN [Roles] AS [t4] ON [t4].[roleid] = [t3].[roleid]
    WHERE ([t4].[description] = @p2) AND ([t3].[personid] = [t0].[personId])
    )) > @p3)

使用载有（）：

var persons = Persons
    		.Where(p=>(p.Personlocations.Select(ps=>ps.Location)
    		.Select(l=>l.Description).Contains("Amsterdam")) && 
    		(p.PersonRoles.Select(pr=>pr.Role)
    		.Select(r=>r.Description).Contains("Student")));

查询结果：

SELECT [t0].[personId] AS [PersonId], [t0].[description] AS [Description]
FROM [Persons] AS [t0]
WHERE (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [personlocations] AS [t1]
    INNER JOIN [Locations] AS [t2] ON [t2].[locationid] = [t1].[locationid]
    WHERE ([t2].[description] = @p0) AND ([t1].[personid] = [t0].[personId])
    )) AND (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [PersonRoles] AS [t3]
    INNER JOIN [Roles] AS [t4] ON [t4].[roleid] = [t3].[roleid]
    WHERE ([t4].[description] = @p1) AND ([t3].[personid] = [t0].[personId])
    ))

使用join（）方法：

var persons = Persons
    	.Join(Personlocations, p=>p.PersonId, ps=>ps.Personid,
(p,ps) => new {p,ps})
.Where(a => a.ps.Location.Description =="Amsterdam")
    	.Join(PersonRoles,
pr=> pr.p.PersonId, r=>r.Personid,(pr,r) => new {pr.p,r})
.Where(a=>a.r.Role.Description=="Student")
    	.Select(p=> new {p.p});

查询结果：

SELECT [t0].[personId] AS [PersonId], [t0].[description] AS [Description]
FROM [Persons] AS [t0]
INNER JOIN [personlocations] AS [t1] ON [t0].[personId] = [t1].[personid]
INNER JOIN [Locations] AS [t2] ON [t2].[locationid] = [t1].[locationid]
INNER JOIN [PersonRoles] AS [t3] ON [t0].[personId] = [t3].[personid]
INNER JOIN [Roles] AS [t4] ON [t4].[roleid] = [t3].[roleid]
WHERE ([t4].[description] = @p0) AND ([t2].[description] = @p1)

您可能希望测试至极的是更快的处理大量数据。

You may want test wich one is faster with large data.

祝你好运。

朱利亚诺Lemes