问题是大多数 EF 执行的数据库调用都使用DbCommand https://msdn.microsoft.com/en-us/library/system.data.common.dbcommand(v=vs.110).aspx with CommadType https://msdn.microsoft.com/en-us/library/system.data.common.dbcommand.commandtype(v=vs.110).aspx Text
,因此虽然 SqlServer 识别 SP 调用,但它通过以下方式将它们作为文本执行sp_executesql
.
为了获得所需的行为,命令应该这样设置:
DbCommand command = ...;
command.CommandText = "StoredProcedureName";
command.CommandType = CommadType.StoredProcedure;
不幸的是 EF 没有提供指定命令类型的标准方法。我建议的解决方案基于:
- 自定义 SP 调用 SQL 语法使用
CallPrefix StoredProcedureName
为了不影响正常通话
- EF命令拦截 http://www.entityframeworktutorial.net/EntityFramework6/database-command-interception.aspx在执行命令之前删除前缀并更改命令类型。
这是实现:
using System.Data;
using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;
public static class Sp
{
public const string CallPrefix = "CallSP ";
public static string Call(string name) { return CallPrefix + name; }
public class CallInterceptor : DbCommandInterceptor
{
public static void Install()
{
DbInterception.Remove(Instance);
DbInterception.Add(Instance);
}
public static readonly CallInterceptor Instance = new CallInterceptor();
private CallInterceptor() { }
static void Process(DbCommand command)
{
if (command.CommandType == CommandType.Text && command.CommandText.StartsWith(Sp.CallPrefix))
{
command.CommandText = command.CommandText.Substring(Sp.CallPrefix.Length);
command.CommandType = CommandType.StoredProcedure;
}
}
public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
Process(command);
base.ReaderExecuting(command, interceptionContext);
}
}
}
您所需要做的就是将上述类添加到您的项目中,调用Sp.CallInterceptor.Install()
一次,例如在你的DbContext
静态构造函数:
public class YourDbContext : DbContext
{
static YourDbContext()
{
Sp.CallInterceptor.Install();
}
// ...
}
然后像这样更改你的 SP 调用(使用你的示例):
from:
return DataContext.Database.SqlQuery<CaseList>("EXEC GetCaseList @CaseStage",
new SqlParameter("@CaseStage", paramList.CaseStageID)).ToList();
to:
return DataContext.Database.SqlQuery<CaseList>(Sp.Call("GetCaseList"),
new SqlParameter("@CaseStage", paramList.CaseStageID)).ToList();
这将生成(对于paramList.CaseStageID == 9
):
EXEC GetCaseList @CaseStage = 9