How to optimize this AspNetIdenty/Entity Framework generated query?

Vimvq1987_79109 0
Viewed 144

When we look into our database, this query is eating a lot of resources:

(@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000))SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[Email] AS [Email], 
    [Project1].[EmailConfirmed] AS [EmailConfirmed], 
    [Project1].[PasswordHash] AS [PasswordHash], 
    [Project1].[SecurityStamp] AS [SecurityStamp], 
    [Project1].[PhoneNumber] AS [PhoneNumber], 
    [Project1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed], 
    [Project1].[TwoFactorEnabled] AS [TwoFactorEnabled], 
    [Project1].[LockoutEndDateUtc] AS [LockoutEndDateUtc], 
    [Project1].[LockoutEnabled] AS [LockoutEnabled], 
    [Project1].[AccessFailedCount] AS [AccessFailedCount], 
    [Project1].[UserName] AS [UserName]
    FROM ( SELECT 
        [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 (@p__linq__0 IS NULL) OR (( CAST(LEN(@p__linq__0) AS int)) = 0) OR (( CAST(CHARINDEX(UPPER(@p__linq__1), UPPER([Extent1].[UserName])) AS int)) > 0)
    )  AS [Project1]
    ORDER BY row_number() OVER (ORDER BY [Project1].[UserName] ASC)
    OFFSET 0 ROWS FETCH NEXT 2147483647 ROWS ONLY

Query plan https://www.brentozar.com/pastetheplan/?id=SyAARIuDU

It looks like this is Entity Framework-generated query for AspNetIdentity. I failed to find out which code is corresponding to this query. Any idea how to look it up?

entity-framework sql-server sql performance
1 Answers

This code is generating such query. 

var users = _userManager().Users.Where(u => string.IsNullOrEmpty(usernameToMatch) || u.UserName.Contains(usernameToMatch)).OrderBy(u => u.UserName).Skip(pageIndex * pageSize).Take(pageSize);

Thank https://stackoverflow.com/users/134204/panagiotis-kanavos for being right and persistent. I was looking at the wrong place.

Lessons to be learned: Or condition in Entity Framework - or generally - SQL - is a bad idea.

Vimvq1987_791090
Related