Excessive SQL queries for user authentication

[bikeoid]

There are an excessive number of SQL queries, even with in the same page. For example, to render the /Trolleys page generates 10 queries of the sequence

exec sp_executesql N'SELECT TOP (1) [Extent1].[Id] AS [Id], [Extent1].[Email] AS [Email], [Extent1].[EmailConfirmed] AS [EmailConfirmed], [Extent1].[PasswordHash] AS [PasswordHash], [Extent1].[SecurityStamp] AS [SecurityStamp], [Extent1].[PhoneNumber] AS [PhoneNumber], [Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed], [Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled], [Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc], [Extent1].[LockoutEnabled] AS [LockoutEnabled], [Extent1].[AccessFailedCount] AS [AccessFailedCount], [Extent1].[UserName] AS [UserName] FROM [dbo].[AspNetUsers] AS [Extent1] WHERE [Extent1].[Id] = @plinq0',N'@plinq0 nvarchar(4000)',@plinq0=N'ccea5518-acc1-403d-8b4c-c9b4fafe49d2'

exec sp_executesql N'SELECT [Extent1].[Id] AS [Id], [Extent1].[UserId] AS [UserId], [Extent1].[ClaimType] AS [ClaimType], [Extent1].[ClaimValue] AS [ClaimValue] FROM [dbo].[AspNetUserClaims] AS [Extent1] WHERE [Extent1].[UserId] = @plinq0',N'@plinq0 nvarchar(4000)',@plinq0=N'ccea5518-acc1-403d-8b4c-c9b4fafe49d2'

exec sp_executesql N'SELECT [Extent1].[LoginProvider] AS [LoginProvider], [Extent1].[ProviderKey] AS [ProviderKey], [Extent1].[UserId] AS [UserId] FROM [dbo].[AspNetUserLogins] AS [Extent1] WHERE [Extent1].[UserId] = @plinq0',N'@plinq0 nvarchar(4000)',@plinq0=N'ccea5518-acc1-403d-8b4c-c9b4fafe49d2'

exec sp_executesql N'SELECT [Extent1].[UserId] AS [UserId], [Extent1].[RoleId] AS [RoleId] FROM [dbo].[AspNetUserRoles] AS [Extent1] WHERE [Extent1].[UserId] = @plinq0',N'@plinq0 nvarchar(4000)',@plinq0=N'ccea5518-acc1-403d-8b4c-c9b4fafe49d2'

[micahlee]

Hey, saw this on #hackgreenville and I wanted to try and see if I could take a crack at it and begin contributing.

These duplicated queries are coming from CustomRoleProvider.cs:30-33

var roles = from ur in user.Roles
            from r in usersContext.Roles
            where ur.RoleId == r.Id
            select r.Name.Trim();

usersContext.Roles is still an IEnumerable<> here, so when it correlates the results, it fetches the roles from the database again for each role in user.Roles. One option to resolve this is to just change that line to from r in usersContext.Roles.ToArray() or similar, to store the dbRoles local for the LINQ expression. I'm happy to put together a PR, but you may have another design choice you'd prefer once you know what the source is.

[bikeoid]

Excellent, this would certainly explain things. Yes - please go ahead with a pull request - I wonder if there is a way to do this as an "inner query" that works with a single role rather than a list? Just musing, I still have to drop back to tutorials when constructing linq queries,. The only thing that matters in the end is efficient SQL queries.

[bikeoid]

Resolved from merged code