实体框架和许多查询不可用？ [英] Entity framework and many to many queries unusable?

问题描述

我正在尝试EF，我根据多对多关系进行大量的过滤。例如我有人，地点和个性分配表来链接两者。我也有一个角色和personrole表。

 编辑：表：
 
个人（personid，name）
 
 personlocation（personid，locationid）
 
位置（位置，描述）
 
 Personrole（personid，roleid）
 
角色（roleid，description）
  

EF会给我个人，角色和位置实体。编辑：由于EF将不生成personlocation和personrole实体类型，所以不能在查询中使用。

如何创建查询给定给定位置的所有人员给定角色？

在SQL中，查询将是

 选择p。* 
从人员p 
加入个人位置作为p在p.personid = pl.personid 
连接位置作为l在pl.locationid = l.locationid 
将personroles作为pr on p.personid = pr.personid 
将角色作为r on pr.roleid = r.roleid 
其中r.description ='Student'和l .description ='阿姆斯特丹'
  

我已经看了，但我似乎找不到一个<

解决方案

在Lambda中：

  var persons = Persons.Where（p =>（p.PersonLocations.Select（ps => ps.Location）
 .Where（l => l。描述==阿姆斯特丹）。Count（）> 0）
 &&（p.PersonRoles.Select（pr => pr.Role）
 .Where（r => r.Description ==学生）Count（）> 0））; 
  

查询结果

[pre> SELECT [t0]。[personId] AS [PersonId]，[t0]。[description] AS [描述]
FROM [Persons] AS [t0]
WHERE（（
SELECT COUNT（*）
FROM [personlocations] AS [t1]
INNER JOIN [位置] 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 [角色] AS [t4] ON [t4] [roleid] = [t3]。[roleid]
WHERE（[t4]。[description] = @ p2）AND（[t3]。[personid] = [t0]。[personId]）
））> @ p3）


使用Contains（）：

  var persons = Persons 
 .Where（p =>（p.Personlocations.Select（ps => ps.Location）
。选择（l => l.Description）.Contains（Amsterdam））&& 
（p.Pers onRoles.Select（pr => pr.Role）
 .Select（r => r.Description）.Contains（Student）））; 
  

查询结果

[pre> SELECT [t0]。[personId] AS [PersonId]，[t0]。[description] AS [描述]
FROM [Persons] AS [t0]
WHERE（EXISTS（
SELECT NULL AS [EMPTY]
FROM [personlocations] AS [t1]
INNER JOIN [位置] AS [t2] ON [t2] = [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）
 .Jo in（PersonRoles，
 pr => pr.p.PersonId，r => r.Personid，（pr，r）=>新的{pr.p，r}）
 .Where（a => a.r.Role.Description ==Student）
 .Select（p => new {p.p}）; 
  

查询结果：

  SELECT [t0]。[personId] AS [PersonId]，[t0]。[description] AS [描述] 
 FROM [Persons] AS [t0] 
 INNER JOIN [personlocations ] AS [t1] ON [t0]。[personId] = [t1]。[personid] 
 INNER JOIN [位置] AS [t2] ON [t2]。[locationid] = [t1] 
 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）
  / pre> 
 
 
你可能想要测试一个更快的大数据。
 
 
 
祝你好运。 > 
 
 
 Giuliano Lemes 
 
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 will give me persons, roles and location entities. EDIT: 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?

In SQL the query would be
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'
I've looked, but I can't seem to find a simple solution.
解决方案
In 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));
query result:
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)
Using Contains():
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")));
query result:
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])
    ))
using 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});
Query Result:
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.

Good luck.

Giuliano Lemes

                        
这篇关于实体框架和许多查询不可用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！