Entity Framework - Many to Many Subquery

Question

I asked a question about this previously but my database structure has changed, and while it made other things simpler, now this part is more complicated. Here is the previous question.

At the time, my EF Context had a UsersProjects object because there were other properties. Now that I've simplified that table, it is just the keys, so all my EF context knows about is Users and Projects and the M2M relationship between them. There is no more UsersProjects as far as EF knows.

So my goal is to say "show me all the users who are working on projects with me."

in SQL, this would go something like:

SELECT * FROM Users INNER JOIN UsersProjects ON Users.ID=UsersProjects.UserID
WHERE ProjectID IN (SELECT ProjectID FROM UsersProjects WHERE UserID=@UserID)

and I started in EF with something like this:

            var myProjects =
                (from p in edmx.Projects
                 where p.Users.Contains(edmx.Users.FirstOrDefault(u => u.Email == UserEmail))
                 orderby p.Name
                 select p).ToList();

            var associatedUsers =
                (from u in edmx.Users
                 where myProjects.Contains(?????????)
                 //where myProjects.Any(????????)
                 select u);

The trick is finding what to put in the ????????. Anyone help here?

Answer 1

var me = context
    .Users
    .First(user => user.Email = "[email protected]");

// Note that there is no call to ToList() or AsEnumerable().
var myProjects = context
    .Projects
    .Where(project => project.Users.Contains(me));

var associatedUsers = context
    .Users
    .Where(user => myProjects.Any(project => user.Project.Contains(project)));

But there are several other possible solutions. For example

var associatedUsers = myProjects
    .SelectMany(project => project.Users)
    .Distinct();

which I would prefer.

Further note that it is much easier to obtain myProjects using a navigation property instead of using Contains().

var myProjects = me.Projects;

Answer 2

Daniel, I tried what you had and ran into some issues. Can you explain what these errors mean?

I tried:

        // Doesn't work.
        using (var edmx = new MayflyEntities())
        {
            var me = edmx.Users.First(user => user.Email == UserEmail);
            var myProjects = edmx.Projects.Where(project => project.Users.Contains(me));
            var associatedUsers = myProjects.SelectMany(project => project.Users).Distinct();
        }

but got the two following exceptions:

Unable to create a constant value of type 'DomainModel.User'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

and

The ObjectContext instance has been disposed and can no longer be used for operations that require a connection.

So, I moved some things around and this works fine, but now I'm curious as to why? In SQL Profiler, it all executes in one query, so why does it show that the context has been disposed? Also, why can it not use the me object instead of the lambda?

        // Works fine
        var edmx = new MayflyEntities();
        var myProjects = edmx.Projects.Where(project => project.Users.Contains(edmx.Users.First(user => user.Email == UserEmail)));
        var associatedUsers = myProjects.SelectMany(project => project.Users).Distinct();