Getting rid of unnecessary join when using link tables with EF

Question

I have this database schema:

User  <--  UserRole  -->  Role

The Role table has a few columns but I'm only interested in the primary key and I can get that from UserRole's foreign key so I wouldn't really need to join in the Role table at all, but I can't figure out how to avoid joining to that table with EF.

I tried this code:

context.Users.Where(u => u.UserId == x).Single().Roles.Select(r => r.RoleId);

This generates two queries. One on the user table and one on a join of UserRole and Role. I can of course reduce it to one query by using .Include("Roles") or SelectMany() but that query would join three tables. Is there a way to get rid of the superfluous join? I want my SQL to resemble this:

SELECT u.*, ur.RoleId
FROM User u
LEFT OUTER JOIN UserRole ur on ur.UserId = u.UserId
WHERE ...

In reality I'm using automapper, but I think this example demonstrates the same problem. Roles is a small table so I can live with the performance penalty this time, but it bugs me that I can't get it as efficient as handwritten SQL. Has anyone else run into this before me and figured out a solution?

score 1 · Answer 1 · answered Dec 30 '11 at 17:27

You can use the following (I don't use var here to make the types explicit):

IQueryable<IEnumerable<int>> query = context.Users
    .Where(u => u.UserId == x)
    .Select(u => u.Roles.Select(r => r.RoleId));

IEnumerable<int> result = query.Single();

This

throws an exception if the user with UserId == x doesn't exist. (I guess you want that because you are using Single in your example.)
returns a collection of RoleIds of user x. The collection can be empty (result.Count() == 0) if the user isn't in any role.

creates the following SQL for query (with x == 1) which only joins User and UserRoles table:

SELECT 
[Project1].[UserId] AS [UserId], 
[Project1].[C1] AS [C1], 
[Project1].[RoleId] AS [RoleId]
FROM ( SELECT 
        [Extent1].[UserId] AS [UserId], 
        [Extent2].[RoleId] AS [RoleId], 
       CASE WHEN ([Extent2].[UserId] IS NULL) 
           THEN CAST(NULL AS int) 
           ELSE 1
       END AS [C1]
       FROM  [dbo].[Users] AS [Extent1]
       LEFT OUTER JOIN [dbo].[UserRoles] AS [Extent2]
           ON [Extent1].[UserId] = [Extent2].[UserId]
       WHERE 1 = [Extent1].[UserId]
     )  AS [Project1]
ORDER BY [Project1].[UserId] ASC, [Project1].[C1] ASC

If you don't want to distinguish if the user doesn't exist or exists but has no roles you can get a much simpler SQL with this query:

IQueryable<int> query = context.Users
    .Where(u => u.UserId == x)
    .SelectMany(u => u.Roles.Select(r => r.RoleId));

IEnumerable<int> result = query.ToList();

This returns an empty collection if the user has no roles or if the user doesn't exist. But the SQL is very simple:

SELECT 
[Extent1].[RoleId] AS [RoleId]
FROM [dbo].[UserRoles] AS [Extent1]
WHERE 1 = [Extent1].[UserId]

So, here is no join between tables involved at all and the query only uses the UserRoles link table.

score 0 · Answer 2 · answered Dec 30 '11 at 15:58

I use roles more efficiently.

Checkout Flags for adding roles to your usertables.

What flags does:

Creates a sort of Enum with:

Administrator = 1
Moderator = 2
SuperUser = 4
User = 8
Visitor = 16

You add an attribute Role in the Usertable as Integer.

"User" = Administrator + Moderator --> Role =  3 ( 1 + 2 )
"User" = Moderator + SuperUser     --> Role =  6 ( 2 + 4 )
"User" = SuperUser + User          --> Role = 12 ( 4 + 8 )

No Junction table anymore and solves your issue.

(Though additional coding will be required for adding the logic into your application)

Yes, I can sacrifice normalization for speed, but I'm curious if I can solve this without altering the database schema. — Johan Levin, Dec 30 '11 at 16:00

Getting rid of unnecessary join when using link tables with EF

2 Answers2