强制提示从实体框架索引到SQL Server [英] Force hint to index from entity framework to SQL Server

问题描述

我在C＃中从Entity Framework调用SQL Server 10，并希望在请求中获取查询提示。数据库具有从Management Studio中运行的SQL正常运行的索引，但是在Visual Studio中使用Entity Framework从C＃调用命令时，查询计划程序在已有索引时选择完整扫描。

我正在创建动态谓词，以下列形式请求数据：

  data.attributeText = data。 vegaDB.attributeText.AsExpandable（），其中（parentPredicate.Compile（））ToList（）。 
  

其中 parentPredicate 是动态生成的，相当于：

 （parentID = p1）AND（attributeName ='name OR ...'）
  

SQL Server查询计划生成的

  SELECT 
 [Extent1]。[attributeID] AS [attributeID]，
 [Extent1]。[parentID] AS [parentID]，
 [Extent1]。[typeID] AS [typeID ]，
 [Extent1]。[attributeName] AS [attributeName]，
 [Extent1]。[attributeData] AS [attributeData] 
 FROM [dbo]。[attributeText] AS [Extent1] 
  

所以用索引替换 [Extent1] 直接sql调用使用的 [IX_parentID] 通过在初始c＃调用中执行查询提示的一些额外的命令似乎是解决方案。我已经看了一眼，但还没有成功。任何想法如何框架问题？

你认为这是正确的解决方案吗？

解决方案

尝试更新数据库中相关表的统计信息，如果统计数据过期，则非最佳查询计划可能用于所有查询。

I am calling SQL Server 10 from Entity Framework in C# and want to get a query hint into the request. The database has indexes which operated normally from SQL run in Management Studio, but when calling the command from C# using Entity Framework in Visual Studio, the query planner chooses a full scan when there is already an index.

I am creating dynamic predicates to request data in the following form:

data.attributeText = data.vegaDB.attributeText.AsExpandable().Where(parentPredicate.Compile()).ToList();

where parentPredicate is dynamically generated equivalent of:

(parentID = p1) AND (attributeName = 'name OR ... ')          

From which the SQL Server query plan generates:

SELECT 
[Extent1].[attributeID] AS [attributeID],  
[Extent1].[parentID] AS [parentID],  
[Extent1].[typeID] AS [typeID],  
[Extent1].[attributeName] AS [attributeName],  
[Extent1].[attributeData] AS [attributeData]  
FROM [dbo].[attributeText] AS [Extent1]

So replacing the [Extent1] with the index [IX_parentID], which the direct sql call uses, by some extra command which does a query hint in the initial c# call would seem the solution. I have had a look around but no success yet. Any idea how to frame the question?

Do u think this is the right solution?

解决方案

Try updating statistics for the related tables in your database, if statistics are outdated non-optimal query plans are likely to be used for all queries.

这篇关于强制提示从实体框架索引到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！