EF Query Optimization

Question

I am working on a small POC mainly just to help me understand EF better. Is there a more efficient way to implement the following?

private static bool IsUserGrantedPermission(DatabaseContext db, Permission permission, User user)
{
    var userRoles = db.Roles.Where(r => r.RolesUsers.Any(ru => ru.UserId == user.Id));
    var userPerms = db.Permissions.Where(p => p.RolesPermissions.Any(rp => userRoles.Any(ur => ur.Id == rp.RoleId)));
    //Console.WriteLine(userPerms.ToString());
    return userPerms.Any(up => up.Id == permission.Id);
}

Here's the SQL that is generated:

exec sp_executesql N'SELECT 
CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[Permissions] AS [Extent1]
    WHERE ( EXISTS (SELECT 
        1 AS [C1]
        FROM ( SELECT 
            [Extent2].[RoleId] AS [RoleId]
            FROM [dbo].[RolesPermissions] AS [Extent2]
            WHERE [Extent1].[Id] = [Extent2].[PermissionId]
        )  AS [Project1]
        WHERE  EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[Roles] AS [Extent3]
            WHERE ( EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[RolesUsers] AS [Extent4]
                WHERE ([Extent3].[Id] = [Extent4].[RoleId]) AND ([Extent4].[UserId] = @p__linq__0)
            )) AND ([Extent3].[Id] = [Project1].[RoleId])
        )
    )) AND ([Extent1].[Id] = @p__linq__1)
)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[Permissions] AS [Extent5]
    WHERE ( EXISTS (SELECT 
        1 AS [C1]
        FROM ( SELECT 
            [Extent6].[RoleId] AS [RoleId]
            FROM [dbo].[RolesPermissions] AS [Extent6]
            WHERE [Extent5].[Id] = [Extent6].[PermissionId]
        )  AS [Project6]
        WHERE  EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[Roles] AS [Extent7]
            WHERE ( EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[RolesUsers] AS [Extent8]
                WHERE ([Extent7].[Id] = [Extent8].[RoleId]) AND ([Extent8].[UserId] = @p__linq__0)
            )) AND ([Extent7].[Id] = [Project6].[RoleId])
        )
    )) AND ([Extent5].[Id] = @p__linq__1)
)) THEN cast(0 as bit) END AS [C1]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]',N'@p__linq__0 uniqueidentifier,@p__linq__1 uniqueidentifier',@p__linq__0='C0E7EB21-BB3D-424E-8EF0-48A6C9526410',@p__linq__1='A94F0203-B97B-46FF-824D-BBA9D482E674'

Why doesn't EF generate WHEN-THEN-ELSE statements (where the ELSE statement returns 0 instead of generating a WHEN-THEN-THEN set of statements where the second THEN is practically a duplicate of the first, just negated? Since the userPerms.Any(...) call returns boolean, wouldn't a WHEN-THEN-ELSE be a more efficient implementation? In the false case, isn't (virtually) the same statement being run twice?

Again, I'm new to this, so maybe I just need to model things differently, or maybe I need to write my query differently. I just want to understand what is going on behind-the-scenes better.

Here's the overridden OnModelCreating function.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<RoleUser>()
                .HasKey(ru => new { ru.RoleId, ru.UserId })
                .ToTable("RolesUsers");

            modelBuilder.Entity<User>()
                .HasMany(u => u.RolesUsers)
                .WithRequired()
                .HasForeignKey(ru => ru.UserId);

            modelBuilder.Entity<Role>()
                .HasMany(r => r.RolesUsers)
                .WithRequired()
                .HasForeignKey(ru => ru.RoleId);


            modelBuilder.Entity<RolePermission>()
                .HasKey(rp => new { rp.RoleId, rp.PermissionId })
                .ToTable("RolesPermissions");

            modelBuilder.Entity<Permission>()
                .HasMany(p => p.RolesPermissions)
                .WithRequired()
                .HasForeignKey(rp => rp.PermissionId);

            modelBuilder.Entity<Role>()
                .HasMany(r => r.RolesPermissions)
                .WithRequired()
                .HasForeignKey(rp => rp.RoleId);

            modelBuilder.Entity<User>()
                .HasKey(user => user.Id)
                .Property(user => user.Id)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            modelBuilder.Entity<User>()
                .Property(user => user.Name);

            modelBuilder.Entity<Role>()
                .HasKey(role => role.Id)
                .Property(role => role.Id)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            modelBuilder.Entity<Role>()
                .Property(role => role.Name);

            modelBuilder.Entity<Permission>()
                .HasKey(permission => permission.Id)
                .Property(permission => permission.Id)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            modelBuilder.Entity<Permission>()
                .Property(permission => permission.Name);

            base.OnModelCreating(modelBuilder);
        }

The code is also located here if you find that easier: http://samplesecurityapp.codeplex.com/SourceControl/changeset/view/24664#385932

Follow up to questions below:

@[Richard Deeming]

The following is the result of your suggested changes to the query.

exec sp_executesql N'SELECT 
[Project1].[C1] AS [C1], 
[Project1].[Id] AS [Id], 
[Project1].[AuthenticationId] AS [AuthenticationId], 
[Project1].[Name] AS [Name], 
[Project1].[C2] AS [C2], 
[Project1].[RoleId] AS [RoleId], 
[Project1].[UserId] AS [UserId]
FROM ( SELECT 
    [Limit1].[Id] AS [Id], 
    [Limit1].[AuthenticationId] AS [AuthenticationId], 
    [Limit1].[Name] AS [Name], 
    1 AS [C1], 
    [Extent2].[RoleId] AS [RoleId], 
    [Extent2].[UserId] AS [UserId], 
    CASE WHEN ([Extent2].[RoleId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
    FROM   (SELECT TOP (2) [Extent1].[Id] AS [Id], [Extent1].[AuthenticationId] AS [AuthenticationId], [Extent1].[Name] AS [Name]
        FROM [dbo].[Users] AS [Extent1]
        WHERE [Extent1].[Id] = @p__linq__0 ) AS [Limit1]
    LEFT OUTER JOIN [dbo].[RolesUsers] AS [Extent2] ON [Limit1].[Id] = [Extent2].[UserId]
)  AS [Project1]
ORDER BY [Project1].[Id] ASC, [Project1].[C2] ASC',N'@p__linq__0 uniqueidentifier',@p__linq__0='C0E7EB21-BB3D-424E-8EF0-48A6C9526410'

Although, I do see the WHEN-THEN-ELSE generated by this query, it returns additional columns that are not required. Is there a way to get EF to only return a bit field indicating if a given user has a given permission? The query that I wrote does return just one field, but I believe in the false case, it runs the same query twice.

I'm curious if I could generate something more like this. It is a hybrid of both approaches in that it only returns a bit field indicating if the user has the permission and it uses joins instead of numerous WHERE EXISTS statements

DECLARE @UserId UNIQUEIDENTIFIER
DECLARE @PermissionId UNIQUEIDENTIFIER

SET @UserId = '151b517b-051f-4040-b6c6-036dd06d661d';
SET @PermissionId = '2A379840-F44D-4D09-AAD5-2B34EDF1EDC9';

SELECT
CASE
    WHEN (
        EXISTS(
            SELECT p.Id
            FROM Permissions p
            INNER JOIN RolesPermissions rp
                ON p.Id = rp.PermissionId
            INNER JOIN Roles r
                ON rp.RoleId = r.id
            INNER JOIN RolesUsers ru
                ON r.id = ru.RoleId
            WHERE ru.UserId = @UserId AND p.Id = @PermissionId
        )
    ) THEN cast(1 AS BIT)
    ELSE CAST(0 AS BIT)
END

Answer 1

You should be able to use:

return user.RolesUsers
   .SelectMany(ru => ru.Role.RolesPermissions)
   .Any(up => up.PermissionId == permission.Id);

Since your RoleUser and RolePermission classes are simple many-to-many containers, I'd be inclined to remove them and go for a straight many-to-many relationship:

public class Role : Base
{
   public virtual ICollection<Permission> Permissions { get; set; }
   public virtual ICollection<User> Users { get; set; }
}

public class User : Base
{
   public virtual ICollection<Role> Roles { get; set; }
   public string AuthenticationId { get; set; }
}

public class Permission : Base
{
   public virtual ICollection<Role> Roles { get; set; }
}

You shouldn't even need any mapping code; the conventions should do the right thing for you.