.net - net - one to many entity framework

Marco de la entidad y muchas a muchas consultas inutilizables?

Question

Estoy probando EF y hago un montón de filtrado basado en muchas relaciones. Por ejemplo, tengo personas, ubicaciones y una tabla de ubicación de personas para vincular los dos. También tengo un rol y una lista de roles.

EDIT: Tables:
Person (personid, name)
Personlocation (personid, locationid)
Location (locationid, description)
Personrole (personid, roleid)
Role (roleid, description)

EF me dará personas, roles y entidades de localización. EDITAR: Dado que EF NO generará los tipos de entidad personlocation y personrole, no se pueden usar en la consulta.

¿Cómo creo una consulta para darme todas las personas de una ubicación determinada con un rol dado?

En SQL, la consulta sería

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''

Lo he buscado, pero parece que no puedo encontrar una solución simple .

Answer 1

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

resultado de la consulta:

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)

Usando Contiene ():

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

resultado de la consulta:

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])
))

usando 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});

Resultado de la consulta:

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)

Es posible que desee una prueba que sea más rápida con datos de gran tamaño.

Buena suerte.

Giuliano Lemes

Answer 2

Ok, LINQ no tiene ninguna provisión por lo que puedo decir. Las expresiones Lambda funcionan con any ().

Answer 3

Se encontró más información sobre esto, cómo y por qué las entidades manejan muchas a muchas:

Blog de ASP.NET: asignaciones de muchos a muchos en Entity Framework

Answer 4

Nota:

Dado que está en EF v1, NO tendremos PersonLocation y PersonRole generados como entidades como lo que hace LINQ2SQL (La respuesta anterior domestica el escenario LINQ2SQL, que no se aplica a la pregunta).

Solución 1:

Persons.Include("Role").Include("Location") // Include to load Role and Location
.Where(p => p.Role.Any(r => r.description == "Student")
&& p.Location.Any(l => l.description == "Amsterdam")).ToList(); 

Esto se ve bien y sencillo, pero esto genera secuencias de comandos SQL feas y su rendimiento es bueno.

Solución 2:

Aquí hay desgloses.

 // Find out all persons in the role
// Return IQuerable<Person>
var students = Roles.Where(r => r.description == "Student")
.SelectMany(r => r.Person);
// Find out all persons in the location
// Return IQuerable<Person>
var personsInAmsterdam = Locations.Where(l=> l.description == "Amsterdam")
.SelectMany(l=>l.Person);
// Find out the intersection that gives us students in Admsterdam.
// Return List<Person>
var AdmsterdamStudents = students.Intersect(personsInAmsterdam).ToList();

Combina tres pasos arriba en uno:

 //Return List<Person>
var AdmsterdamStudents = Roles.Where(r => r.description == "Student")
.SelectMany(r => r.Person)
.Intersect
(
Locations
.Where(l=> l.description == "Amsterdam")
.SelectMany(l=>l.Person)
).ToList();

Es un poco detallado. Pero esto genera una consulta SQL limpia y funciona bien.