EF6ParameterSniffing我有一个动态查询太大放不下这里。可以肯定地说，在目前的形式中，它利用CLR过程根据传递的搜索参数的数量动态构建连接，然后获取该结果并将其连接到更详细的表以带回对最终用户很重要的属性.我已将整个查询转换为LINQtoEntities，我发现它生成的SQL足以完成这项工作，无论是通过EF6运行，还是查询超时。获取生成的SQL并在SSMS中运行它以在3秒或更短时间内运行。我只能想象我的问题是参数嗅探。我已经尝试更新数据库中每个表的统计信息，但这并没有解决问题。我的问题是：我可以通过EF以某种方式嵌入像“OPTIONRECOMPILE”这样的选项吗？在DB上执行它们之前，你可以使用EF6的拦截功能来操作它内部的SQL命令，比如在命令末尾添加选项（重新编译）：{}publicvoidNonQueryExecuted(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){}publicvoidReaderExecuted(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){}publicvoidReaderExecuting(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){addQueryHint(command);}publicvoidScalarExecuted(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){}publicvoidScalarExecuting(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){addQueryHint(command);}privatestaticvoidaddQueryHint(IDbCommandcommand){if(command.CommandType!=CommandType.Text||!(命令是SqlCommand))返回；if(command.CommandText.StartsWith("select",StringComparison.OrdinalIgnoreCase)&&!command.CommandText.Contains("option(recompile)")){command.CommandText=command.CommandText+"option(recompile)";要使用它，请在应用程序的开头添加以下行：DbInterception.Add(newOptionRecompileHintDbCommandInterceptor());我有一个类似的问题最后，我删除了缓存的查询计划：dbccfreeproccache([yourplanhandlehere])要获取计划句柄，您可以使用以下查询：SELECTqs.plan_handle,a.attrlist,est.dbid,textFROMsys.dm_exec_query_statsqsCROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)estCROSSAPPLY(SELECTepa.attribute+'='+convert(nvarchar(127),epa.value)+''来自sys.dm_exec_plan_attributes(qs.plan_handle)epaWHEREepa.is_cache_key=1ORDERBYepa.attributeFORXMLPATH(''))ASa(attrlist)WHEREest.textLIKE'%standardHourRate%'andest.textlike'%q__7%'andest.textlike'%UnitOverhead%'ANDest.textNOTLIKE'%sys.dm_exec_plan_attributes%'用适当的查询部分替换'like'子句的内容。您可以在这里看到我的整个问题：使用实体框架的SQL查询运行速度较慢，使用错误的查询计划事实证明，DB拦截器是非常全局的，我只希望在特定场景的特定情况下发生这种情况。在这里，我们正在为支持添加额外的查询提示打下基础，这些查询提示可以根据需要打开和关闭。由于我经常公开用于传递连接字符串的方法，因此我也包含了对此的支持。下面将为您的上下文提供一个标志，以通过扩展EF生成的分部类以编程方式启用/禁用提示。我们还将拦截器中的一小段重用代码扔到了它自己的方法中。小界面publicinterfaceIQueryHintable{boolHintWithRecompile{get;放;}}DB命令拦截器publicclassOptionHintDbCommandInterceptor:IDbCommandInterceptor{publicvoidNonQueryExecuting(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){AddHints(command,interceptionContext);}publicvoidNonQueryExecuted(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){}publicvoidReaderExecuted(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){}publicvoidReaderExecuting(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){AddHints(command,interceptionContext);}publicvoidScalarExecuted(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){}publicvoidScalarExecuting(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){AddHints(command,interceptionContext);}privatestaticvoidAddHints(DbCommand命令，DbCommandInterceptionContextinterceptionContext){varcontext=interceptionContext.DbContexts.FirstOrDefault();if(contextisIQueryHintable){varhints=(IQueryHintable)context;如果（提示。HintWithRecompile）{addRecompileQueryHint（命令）；}}}privatestaticvoidaddRecompileQueryHint(IDbCommandcommand){if(command.CommandType!=CommandType.Text||!(commandisSqlCommand))return;if(command.CommandText.StartsWith("select",StringComparison.OrdinalIgnoreCase)&&!command.CommandText.Contains("option(recompile)")){command.CommandText=command.CommandText+"option(recompile)";}}}扩展实体上下面以添加IQueryHintablepublicpartialclassSomeEntities:DbContext,IQueryHintable{publicboolHintWithRecompile{get;放;}publicSomeEntities(stringconnectionString,boolhintWithRecompile):base(connectionString){HintWithRecompile=hintWithRecompile;}publicSomeEntities(boolhintWithRecompile):base(){HintWithRecompile=hintWithRecompile;}publicSomeEntities(stringconnectionString):base(connectionString){}}注册数据库命令拦截器(global.asax)DbInterception.Add(newOptionHintDbCommandInterceptor());启用上下文范围using(vardb=newSomeEntities(hintWithRecompile:true)){}打开或关闭db.HintWithRecompile=true;//做点什么db.HintWithRecompile=false;我之所以调用这个HintWithRecompile是因为您可能还想实现HintOptimizeForUnknown或其他查询提示对于像@Greg这样的我来说，启用这个系统并不是一个选项，所以我编写了这个小实用程序类，它临时添加选项（重新编译）到在OptionRecompileScope中执行的查询.用法示例使用(newOptionRecompileScope(dbContext)){returndbContext.YourEntities.Where().ToList();}完成以上就是C#学习教程的全部内容：EF6参数嗅探分享，如果对大家有用需要了解更多C#学习教程，希望大家多多关注——公开课OptionRecompileScope:IDisposable{privatereadonlyOptionRecompileDbCommandInterceptor拦截器；publicOptionRecompileScope(DbContextcontext){interceptor=newOptionRecompileDbCommandInterceptor(context);DbInterception.Add(拦截器}ptor);(){DbInterception.Remove(拦截器);}privateclassOptionRecompileDbCommandInterceptor:IDbCommandInterceptor{privatereadonlyDbContextdbContext;内部OptionRecompileDbCommandInterceptor(DbContextdbContext){this.dbContext=dbContext;}publicvoidNonQueryExecuting(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){}publicvoidNonQueryExecuted(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){}publicvoidReaderExecuting(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){if(ShouldIntercept(command,interceptionContext)){AddOptionRecompile(command);}}publicvoidReaderExecuted(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){}publicvoidScalarExecuting(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){if(ShouldIntercept(command,interceptionContext)){AddOptionRecompile(command);}}publicvoidScalarExecuted(DbCommandcommand,DbCommandInterceptionContextinterceptionContext){}privatestaticvoidAddOptionRecompile(IDbCommandcommand){command.CommandText=command.CommandText+"选项(重新编译)";}privateboolShouldIntercept(IDbCommandcommand,DbCommandInterceptionContextinterceptionContext){returncommand.CommandType==CommandType.Text&&commandisSqlCommand&&interceptionContext.DbContexts.Any(interceptionDbContext=>ReferenceEquals(interceptionDbContext,dbContext));}}}本文收集自网络，不代表立场。如涉及侵权，请点击右侧联系管理员删除。如有转载请注明出处：
