调用表值函数时添加查询提示 [英] Adding a query hint when calling Table-Valued Function
问题描述
选项（重新编译）添加到其中，因为它所执行的执行计划是不是最佳的。在SQL Server Management Studio中运行查询，它将如下所示：选择新新新旗新新新旗新新旗新新旗旗新新旗新新旗旗新新旗新新旗旗新新旗旗新新旗旗新新旗新新旗新新旗旗新新旗200新新新旗新新旗新新旗200新新新旗新新旗200新新新旗新新旗2001- EF部分看起来像：
*
从dbo.fDE_myquery（0，0，3309，'7/1/2013'，'7/1/2014'，0，0）
选项（重新编译）
var query = from ct in ctx.fDE_myQuery（aBool，anotherBool，StartDate，
EndDate，someInt，moreBool）
select f;
我看到这个问题：
a href =https://stackoverflow.com/questions/9890699/how-do-i-control-parameter-sniffing-and-or-query-hints-in-entity-framework>如何控制参数嗅探和200新新新新旗新新旗新新旗旗新新旗新新旗新新旗旗新新旗新新旗200新新新旗新新旗200新新新旗新新旗新新旗旗新新旗新新旗新新旗旗新新旗旗新p新新新旗新新旗旗新1992： / em>使用实体框架实现建议的解决方案（使用计划指南）。如果这是唯一的解决方案，那么您如何使实体框架能够使用计划指南？
解决方案我遇到这个：
https：//entityframework.codeplex .com / wikipage？title =拦截
看来你可以这样做：
public class HintInterceptor：DbCommandInterceptor
{
public override void ReaderExecuting（System.Data.Common.DbCommand命令，DbCommandInterceptionContext< System.Data.Common.DbDataReader> interceptionContext）
{
command.CommandText + =option（recompile）;
base.ReaderExecuting（command，interceptionContext）;
}
}
并注册它（我在
Application_Startglobal.asax.cs）：DbInterception.Add（new HintInterceptor（））;
它将让您更改
$ 200新新新新旗新新新旗新新旗新新旗200新新新新旗新新旗200新新新旗新新200新新旗新新旗200新新新新旗新新200新新旗新新200新新旗新新旗新200新新旗新200新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新旗新CommandText。 X- 20045 X- 200 X- 200 200 X- 200 200 X- 200 X- 20045 X- 200 /我想我可以用上下文来做一些事情，以确定提示是否合适，或者更糟的情况是我可以检查CommandText本身。interceptorContext，您可以获得DbContexts，所以我定义了一个如下所示的界面： $ b X-454545454545 X- 20045 X-454545 X-454545 X-454545 X-454545 X-454545 X-454545 X- 20045 X- 20045 X- 20045 X- 20045 X- 20045 X-组; }
bool ApplyHint {get;组; }
}
然后创建一个派生自我原来的DbContext的类（由EF生成）并实现上述界面。然后我改变了我的拦截器看起来像这样：
public class HintInterceptor：DbCommandInterceptor
{
public override void ReaderExecuting（System.Data.Common.DbCommand命令，DbCommandInterceptionContext< System.Data.Common.DbDataReader> interceptionContext）
{
if（interceptionContext.DbContexts.Any（db => db是Dal.IQueryHintContext ））
{
var ctx = interceptionContext.DbContexts.First（db => db为Dal.IQueryHintContext）为Dal.IQueryHintContext;
if（ctx.ApplyHint）
{
command.CommandText + = string.Format（option（{0}），ctx.QueryHint）;
}
}
base.ReaderExecuting（command，interceptionContext）;
}
}
现在使用它，我创建一个使用我的派生类而不是原始的，将 QueryHint 设置为任何我想要的（在这种情况下为重新编译）并设置 ApplyHint 在执行命令之前将其重新设置为false。
自我介绍，我最终定义了一个这样的界面：
public interface IQueryHintContext
{
string QueryHint {get;组; }
bool ApplyHint {get;组; }
}
如此扩展我的数据库环境（你当然可以，使用部分类扩展EF生成的类）：
public class MyEntities_Ext：MyEntities，IQueryHintContext
{
public string QueryHint {get;组; }
public bool ApplyHint {get;组; }
}
然后，打开，关闭部分很容易处理，我定义了这一点：
public class HintScope：IDisposable
{
public IQueryHintContext Context {get;私人集合}
public void Dispose（）
{
Context.ApplyHint = false; X-4545454545 X-454545 X-454545 X-454545 X-454545 X-454545 X-454545 X-454545 X-454545 X-4545 X- 20045 X- 20045 X- 20045 X-
Context.ApplyHint = true;
Context.QueryHint = hint;
}
}
现在使用它，我可以做到这一点：
using（var ctx = new MyEntities_Ext（））
{
//没有任何代码， t需要查询提示
// ....
//现在我们要查询hint
使用（var qh = new HintScope（ctx，recompile））
{
//需要重新编译提示的查询
}
//返回非提示代码
}
查询提示，因此您不需要每次都指定字符串 recompile ，并且会导致打字错误），但它解决了我的直接问题。 I'm calling a Table-Valued Function from entity framework and need to be able to add the option (recompile) to it because the execution plan it picks up is not optimal. Running the query in SQL Server Management Studio, it would look something like this:
select
*
from dbo.fDE_myquery(0, 0, 3309, '7/1/2013', '7/1/2014', 0, 0)
option (recompile)
By from EF, there's no way to add that hint, AFAIK. The EF part looks something like:
var query = from f in ctx.fDE_myQuery(aBool, anotherBool, StartDate,
EndDate, someInt, moreBool)
select f;
I saw this question:
How do I control parameter sniffing and/or query hints in entity framework?
But it's old, and the accepted solution doesn't really give enough information on how to actually implement the suggested solution (use plan guides) with entity framework. If that is the only solution, how do you get entity framework to use a plan guide anyway?
I came across this:
https://entityframework.codeplex.com/wikipage?title=Interception
And it appears that you can do something like this:
public class HintInterceptor : DbCommandInterceptor
{
public override void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
{
command.CommandText += " option (recompile)";
base.ReaderExecuting(command, interceptionContext);
}
}
And register it like this (I did it in Application_Start of global.asax.cs):
DbInterception.Add(new HintInterceptor());
And it will let you alter the CommandText. The only problem is that it's now attached for every reader query which might be a problem as some of them might be negatively impacted by that hint. I'm guessing I can do something with the context to figure out if the hint is appropriate or not, or worse case I could examine the CommandText itself.
Doesn't quite seem the most elegant or fine-grained solution.
Edit: From the interceptorContext, you can get the DbContexts, so I defined an interface that looks like this:
public interface IQueryHintContext
{
string QueryHint { get; set; }
bool ApplyHint { get; set; }
}
And then created a class that derives from my original DbContext (generated by EF) and implements the above interface. Then I changed my interceptor to look like this:
public class HintInterceptor : DbCommandInterceptor
{
public override void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
{
if (interceptionContext.DbContexts.Any(db => db is Dal.IQueryHintContext))
{
var ctx = interceptionContext.DbContexts.First(db => db is Dal.IQueryHintContext) as Dal.IQueryHintContext;
if (ctx.ApplyHint)
{
command.CommandText += string.Format(" option ({0})", ctx.QueryHint);
}
}
base.ReaderExecuting(command, interceptionContext);
}
}
Now to use it, I create a context using my derived class instead of the original, set QueryHint to whatever I want it to be (recompile in this case) and set ApplyHint right before I execute the command and set it back to false afterwards.
To make this all a little more self-contained, I ended up defining an interface like this:
public interface IQueryHintContext
{
string QueryHint { get; set; }
bool ApplyHint { get; set; }
}
And extended my db context like this (you could, of course, just use a partial class to extend the EF generated class as well):
public class MyEntities_Ext : MyEntities, IQueryHintContext
{
public string QueryHint { get; set; }
public bool ApplyHint { get; set; }
}
And then, to make the turn-on, turn-off part a little easier to handle, I defined this:
public class HintScope : IDisposable
{
public IQueryHintContext Context { get; private set; }
public void Dispose()
{
Context.ApplyHint = false;
}
public HintScope(IQueryHintContext context, string hint)
{
Context = context;
Context.ApplyHint = true;
Context.QueryHint = hint;
}
}
Now to use it, I can do just this:
using (var ctx = new MyEntities_Ext())
{
// any code that didn't need the query hint
// ....
// Now we want the query hint
using (var qh = new HintScope(ctx, "recompile"))
{
// query that needs the recompile hint
}
// back to non-hint code
}
This maybe slightly overkill and could be developed further (for example, using an enum for available hints instead of a string - or subclassing a recompile query hint so you don't need to specify the string recompile every time and risk a typo), but it solved my immediate problem.
这篇关于调用表值函数时添加查询提示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
