不能使用where子句在Entity Framework中使用数据库索引 [英] Can't use DB indexes with Entity Framework using where clauses

问题描述

我们正在使用实体框架处理具有5000万个条目的表（在Microsoft SQL Server中）。

  public异步Task< List< TableName>> MyMethod（int fooId，int count）
 {
使用（var context = new Context（ConnectionString））
 {
 return 
等待context.TableName.AsNoTracking（） 
 .Where（_ => _.FooId == fooId）
 .OrderByDescending（_ => _.DateCreated）
 .Take（count）
 .ToListAsync（） ; 
} 
} 
  

实体框架将其翻译为（美化）：

 声明@fooId int = 42 
选择顶部（100）* 
 FROM TableName 
 WooE FooId = @fooId 
 ORDER BY DateCreated DESC 
  

FooId和DateCreated列都获得了索引，但是SQL Server仍然会进行全表扫描，这需要很长时间。这是因为在语句外部分配了42（似乎与选择性有关）。如果您写了

  WHERE FooId = 42 
   
 
 
有没有一种方法可以使实体框架优化生成的查询？目前，我唯一的方法似乎是对我的代码中的大型表使用原始SQL查询：-/ 
是否有更好的解决方法？
 
 
 
编辑： 
评论中要求的更多详细信息：
实体框架生成的非美化查询：
 选择顶部（100）
 [Project1]。[DateCreated] AS [DateCreated]，
 [Project1]。[FooId] AS [FooId] 
 FROM（SELECT 
 [Extent1] 。[DateCreated] AS [DateCreated]，
 [Extent1]。[FooId] AS [FooId] 
来自[dbo]。[TableName] AS [Extent1] 
 WHERE [Extent1]。 FooId] = @ p__linq__0 
）如[Project1] 
 ORDER BY [Project1]。[DateCreated] DESC 
 
-p__linq__0：'42'（Type = Int32，IsNullable = false）
  
索引创建脚本：
 在[dbo]上创建命名空间索引[IX_TableName_FooId]。[TableName] 
（
 [FooId] ASC 
）有（PAD_INDEX = OFF， STATISTICS_NORECO MPUTE = OFF，SORT_IN_TEMPDB = OFF，DROP_EXISTING = OFF，ONLINE = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON，FILLFACTOR = 85）ON [次要] 
 GO 
  
为我的表创建脚本：
 创建表[dbo ]。[TableName]（
 [DateCreated] [datetime] NOT NULL，
 [FooId] [int] NULL 
）ON [PRIMARY] 
  
  
解决方案
如果代码完全像这样执行：
 
 
 声明@fooId int = 42 
选择顶部（100）* 
从TableName 
那里FooId = @fooId 
 ORDER BY DateCreated DESC 
  
 
 
 ie没有参数，只有局部变量，估计的行数是C ^ 1/2（C =表基数），具有非唯一值。 
 
 
 
您可以尝试使用 option（recompile）传递真实值。 / p> 
 
 
这将导致在已分配变量时重新编译您的语句，即执行计划将考虑您有效传递的值。
 
 
 
要查看实际执行的操作以及估算的行数和实际行数，您应提供实际的执行计划。
 
We are using entity framework to work with a table with 50 million entries (at a Microsoft SQL Server).
public async Task<List<TableName>> MyMethod(int fooId, int count)
{
    using (var context = new Context(ConnectionString))
    {
        return
            await context.TableName.AsNoTracking()
                .Where(_ => _.FooId== fooId)
                .OrderByDescending(_ => _.DateCreated)
                .Take(count)
                .ToListAsync();
    }
}
Entity Framework translates this to (beautified):
declare @fooId int = 42
SELECT TOP (100) *
FROM TableName
WHERE FooId = @fooId
ORDER BY DateCreated DESC
The columns FooId and DateCreated both got an index, but SQL Server does a full table scan anyway, which takes very long. This is caused because 42 is assigned outside of the statement (and seems to be related to selectivity). The indexes would be used if you wrote
WHERE FooId = 42
Is there a way to get Entity Framework to optimize the generated query? At the moment, my only way seems to be using raw SQL queries for huge tables in my code :-/
Is there a better workaround?

Edit:
More details as requested in the comments:
Non beautified query that is generated by entity framework:
SELECT TOP (100) 
    [Project1].[DateCreated] AS [DateCreated], 
    [Project1].[FooId] AS [FooId]
    FROM ( SELECT 
        [Extent1].[DateCreated] AS [DateCreated], 
        [Extent1].[FooId] AS [FooId]
        FROM [dbo].[TableName] AS [Extent1]
        WHERE [Extent1].[FooId] = @p__linq__0
    )  AS [Project1]
    ORDER BY [Project1].[DateCreated] DESC

-- p__linq__0: '42' (Type = Int32, IsNullable = false) 
The create script for the index:
CREATE NONCLUSTERED INDEX [IX_TableName_FooId] ON [dbo].[TableName]
(
    [FooId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [SECONDARY]
GO
Create script for my table:
CREATE TABLE [dbo].[TableName](
    [DateCreated] [datetime] NOT NULL,
    [FooId] [int] NULL
) ON [PRIMARY]
解决方案
If your code is executed exactly like this:

declare @fooId int = 42
SELECT TOP (100) *
FROM TableName
WHERE FooId = @fooId
ORDER BY DateCreated DESC

i.e. has no parameters but local variables only, the estimated number of rows is C^1/2 (C = table cardinality) with non unique value. It will imply full scan.

You can try to pass the real value by using option(recompile).

This will cause your statement to be recompiled when the variable is already assigned, i.e. execution plan will consider a value that you effectivly passed.

To see what is actually executed and what was the estimation and real number of rows you should provide the actual execution plan.

                        
这篇关于不能使用where子句在Entity Framework中使用数据库索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！