Comment optimiser cette requête générée par AspNetIdenty/Entity Framework ?

Question

Quand on regarde dans notre base de données, cette requête consomme beaucoup de ressources :

(@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

Plan de requête https://www.brentozar.com/pastetheplan/?id=SyAARIuDU

Il semble que ce soit une requête générée par Entity Framework pour AspNetIdentity. Je n'ai pas réussi à trouver le code correspondant à cette requête. Une idée pour le trouver ?

Answer 1

Ce code génère une telle requête.

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

Remerciez https://stackoverflow.com/users/134204/panagiotis-kanavos pour avoir été juste et persévérant. Je regardais au mauvais endroit.

Les leçons à tirer : Ou condition dans Entity Framework - ou en général - SQL - est une mauvaise idée.