Query many to many relationship in Linq and EFCore

Question

I'm trying to do the following query in linq, however I'm getting an exception error, though my query looks fine to me. So here is the story:

Diagram

I have a many to many relationship between the users and the organizations. A user can be a part of many organizations, and an organization can have many users.

What Im trying to query

So given a user id, i want to query all the team members (users) i have in all the organizations i belong to. So

• Input: User X id (guid), and this user belongs to Organization A, and Organization B

• Output:

  User A, Organization A

  User B, Organization A

  User C, Organization B

The Actual Query

I though this would do just that

                var user = db.Users.Include(q => q.UserOrganization).SingleOrDefault( q => q.Id == id.ToString());
                var members = (from us in db.Users.Include(q => q.UserOrganization)
                                let orgs =  user.UserOrganization.Select(z => z.OrganizationId)
                                where us.UserOrganization.Any(q => orgs.Contains(q.OrganizationId) )
                                select new UserResource{
                                    id = Guid.Parse(us.Id),
                                    email = us.Email
                                }
                            ).ToArray();

My query fails on the where clause, with the error:

Processing of the LINQ expression 'AsQueryable<long>((Unhandled parameter: __Select_0))' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core

Not sure what to change in the query. Please help.

PS: I wrote the query initially in MySql as follows:

SELECT UU.`Id`, UU.`Email`, UUO.`OrganizationId`
FROM aspnetusers AS UU
LEFT JOIN userorganization AS UUO ON UUO.`UserId` = `UU`.Id 
WHERE UUO.`OrganizationId` IN  
(
    SELECT UO.`OrganizationId` FROM aspnetusers AS U
    LEFT JOIN userorganization AS UO ON UO.UserId = U.Id 
    WHERE u.Id = '6caa67e7-69f3-49a3-ad61-10b07d379f10' 
)
AND UU.Id != '6caa67e7-69f3-49a3-ad61-10b07d379f10'

Answer 1

The "SingleOrDefault" always executes the Query. User is not an IQueryable.

So the let orgs = user.UserOrganization.Select(z => z.OrganizationId) cannot be translated to SQL, do your var orgs = user.UserOrganization.Select(z => z.OrganizationId) before the Query, in Plain C#. This cannot be used in SQL-Queries.

With orgs being an IList<int> it will work.

But it should be prefered to find a solution that can be solved with one query only. Here you have two.

The SingleOrDefault might be not useful, you go better without, than you have a simple IQueryable. And The "Any" can most often be realized with a simple (Inner) Join, returning only values, if you have a match between to tables. That is the Same as Where - Any - Contains