sql - 如何优化这个 AspNetIdenty/Entity Framework 生成的查询？

Question

当我们查看我们的数据库时，这个查询正在消耗大量资源:

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

查询计划 https://www.brentozar.com/pastetheplan/?id=SyAARIuDU

看起来这是 Entity Framework 生成的 AspNetIdentity 查询。我未能找出与此查询对应的代码。知道如何查找吗？

Answer 1

此代码正在生成此类查询。

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

感谢 https://stackoverflow.com/users/134204/panagiotis-kanavos因为正确和坚持。我看错地方了。

要吸取的教训:或者 Entity Framework 中的条件 - 或者通常 - SQL - 是一个坏主意。