Entity framework and many to many queries unusable?

Question

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.

Answer 1

Note:

Since it's in EF v1, we will NOT have PersonLocation and PersonRole generated as entities like what LINQ2SQL does (The answer above domonstrating LINQ2SQL scenario, which doesn't apply to the question.)

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

This looks nice and straightforward, but this generates ugly SQL script and its performance is ok.

Solution 2:

Here are breakdowns.

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

Combine three steps above into one:

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

It's sort of verbose. But this generates clean SQL query and performs well.

Answer 2

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

Answer 3

Found more info on this, the how and why Entities handles many to many:

ASP.NET Blog - Many to Many Mappings in Entity Framework