How do you join two many to many tables in Entity Framework?

Question

I have a few Tables I want to join together:

• Users
• UserRoles
• WorkflowRoles
• Role
• Workflow

The equivalent sql I want to generate is something like

select * from Users u
inner join UserRoles ur on u.UserId = ur.UserId
inner join WorkflowRoles wr on wr.RoleId = ur.RoleId
inner join Workflow w on wr.WorkflowId = w.Id
where u.Id = x

I want to get all the workflows a user is part of based on their roles in one query. I've found that you can get the results like this:

user.Roles.SelectMany(r => r.Workflows)

but this generates a query for each role which is obviously less than ideal.

Is there a proper way to do this without having to resort to hacks like generating a view or writing straight sql?

Answer 1

You could try the following two queries:

This one is better readable, I think:

var workflows = context.Users
    .Where(u => u.UserId == givenUserId)
    .SelectMany(u => u.Roles.SelectMany(r => r.Workflows))
    .Distinct()
    .ToList();

(Distinct because a user could have two roles and these roles may contain the same workflow. Without Distinct duplicate workflows would be returned.)

But this one performs better, I believe:

var workflows = context.WorkFlows
    .Where(w => w.Roles.Any(r => r.Users.Any(u => u.UserId == givenUserId)))
    .ToList();