Databases
StackOverflow2013
Postsdbo
POEF Query Optimization
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEF Query Optimization
    text
    copied!<p>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? </p> <pre><code>private static bool IsUserGrantedPermission(DatabaseContext db, Permission permission, User user) { var userRoles = db.Roles.Where(r =&gt; r.RolesUsers.Any(ru =&gt; ru.UserId == user.Id)); var userPerms = db.Permissions.Where(p =&gt; p.RolesPermissions.Any(rp =&gt; userRoles.Any(ur =&gt; ur.Id == rp.RoleId))); //Console.WriteLine(userPerms.ToString()); return userPerms.Any(up =&gt; up.Id == permission.Id); } </code></pre> <p>Here's the SQL that is generated:</p> <pre><code>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' </code></pre> <p>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?</p> <p>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.</p> <p>Here's the overridden OnModelCreating function.</p> <pre><code>protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity&lt;RoleUser&gt;() .HasKey(ru =&gt; new { ru.RoleId, ru.UserId }) .ToTable("RolesUsers"); modelBuilder.Entity&lt;User&gt;() .HasMany(u =&gt; u.RolesUsers) .WithRequired() .HasForeignKey(ru =&gt; ru.UserId); modelBuilder.Entity&lt;Role&gt;() .HasMany(r =&gt; r.RolesUsers) .WithRequired() .HasForeignKey(ru =&gt; ru.RoleId); modelBuilder.Entity&lt;RolePermission&gt;() .HasKey(rp =&gt; new { rp.RoleId, rp.PermissionId }) .ToTable("RolesPermissions"); modelBuilder.Entity&lt;Permission&gt;() .HasMany(p =&gt; p.RolesPermissions) .WithRequired() .HasForeignKey(rp =&gt; rp.PermissionId); modelBuilder.Entity&lt;Role&gt;() .HasMany(r =&gt; r.RolesPermissions) .WithRequired() .HasForeignKey(rp =&gt; rp.RoleId); modelBuilder.Entity&lt;User&gt;() .HasKey(user =&gt; user.Id) .Property(user =&gt; user.Id) .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); modelBuilder.Entity&lt;User&gt;() .Property(user =&gt; user.Name); modelBuilder.Entity&lt;Role&gt;() .HasKey(role =&gt; role.Id) .Property(role =&gt; role.Id) .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); modelBuilder.Entity&lt;Role&gt;() .Property(role =&gt; role.Name); modelBuilder.Entity&lt;Permission&gt;() .HasKey(permission =&gt; permission.Id) .Property(permission =&gt; permission.Id) .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); modelBuilder.Entity&lt;Permission&gt;() .Property(permission =&gt; permission.Name); base.OnModelCreating(modelBuilder); } </code></pre> <p>The code is also located here if you find that easier: <a href="http://samplesecurityapp.codeplex.com/SourceControl/changeset/view/24664#385932" rel="nofollow">http://samplesecurityapp.codeplex.com/SourceControl/changeset/view/24664#385932</a></p> <p><em>Follow up to questions below:</em></p> <p>@[Richard Deeming]</p> <p>The following is the result of your suggested changes to the query.</p> <pre><code>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' </code></pre> <p>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.</p> <p>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</p> <pre><code>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 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload