实体框架存储过程表值参数 [英] Entity Framework Stored Procedure Table Value Parameter

问题描述

我正在尝试调用接受表值参数的存储过程。我知道这不是直接支持在实体框架，但从我了解你可以使用ExecuteStoreQuery命令从ObjectContext。我有一个通用实体框架存储库，其中有以下ExecuteStoredProcecdure方法：

I'm trying to call a stored procedure that accepts a table value parameter. I know that this isn't directly supported in Entity Framework yet but from what I understand you can do it using the ExecuteStoreQuery command off of the ObjectContext. I have a generic entity framework repository where I have the following ExecuteStoredProcecdure method:

public IEnumerable<T> ExecuteStoredProcedure<T>(string procedureName, params object[] parameters)
{
    StringBuilder command = new StringBuilder();
    command.Append("EXEC ");
    command.Append(procedureName);
    command.Append(" ");

    // Add a placeholder for each parameter passed in
    for (int i = 0; i < parameters.Length; i++)
    {
        if (i > 0)
            command.Append(",");

        command.Append("{" + i + "}");
    }

    return this.context.ExecuteStoreQuery<T>(command.ToString(), parameters);
}

命令字符串结束如下：

EXEC someStoredProcedureName {0},{1},{2},{3},{4},{5},{6},{7}

我尝试在接受表值参数的存储过程上运行此方法，它会中断。我阅读了 here 这些参数需要使用SqlParameter类型和表值参数需要将SqlDbType设置为结构化。所以我做到了这一点，我收到一个错误：

I tried to run this method on a stored procedure that accepts a table valued parameter and it breaks. I read here that the parameters needed to be of type SqlParameter and the table valued parameter needs to have the SqlDbType set to Structured. So I did this and I get an error stating:

The table type parameter p6 must have a valid type name

所以，我将SqlParameter.TypeName设置为我在数据库上创建的用户定义类型的名称，然后当我运行查询我得到以下真正有用的错误：

So, I set the SqlParameter.TypeName to the name of the user defined type I created on the database and then when I run the query I get the following truly helpful error:

Incorrect syntax near '0'.

如果我回到ADO.NET并执行数据读取器，我可以获得运行的查询但是我希望能够使用数据上下文来工作。

I can get the query to run if I revert back to ADO.NET and and execute a data reader but I was hoping to get it to work using the data context.

有没有办法使用ExecuteStoreQuery传递表值参数？此外，我实际上是使用Entity Framework Code First，并将DbContext转换为ObjectContext以获取ExecuteStoreQuery方法。这是必要的还是可以对DbConext做的？

Is there a way to pass a table value parameter using ExecuteStoreQuery? Also, I am actually using Entity Framework Code First and casting the DbContext to an ObjectContext to get the ExecuteStoreQuery method available. Is this necessary or can I do this against the DbConext as well?

推荐答案

更新

我已经在Nuget软件包上添加了对此的支持 - https： //github.com/Fodsuk/EntityFrameworkExtras#nuget （EF4，EF5，EF6）

I've added support for this on Nuget Package - https://github.com/Fodsuk/EntityFrameworkExtras#nuget (EF4,EF5,EF6)

查看 GitHub 代码示例存储库。

稍微但对于尝试将用户定义的表传递到存储过程的人来说，它们并不重要。在玩Nick的榜样和其他Stackoverflow帖子后，我想出了这个：

Slightly off question, but none the less useful for people trying to pass user-defined tables into a stored procedure. After playing around with Nick's example and other Stackoverflow posts, I came up with this:

class Program
{
    static void Main(string[] args)
    {
        var entities = new NewBusinessEntities();

        var dt = new DataTable();
        dt.Columns.Add("WarningCode");
        dt.Columns.Add("StatusID");
        dt.Columns.Add("DecisionID");
        dt.Columns.Add("Criticality");

        dt.Rows.Add("EO01", 9, 4, 0);
        dt.Rows.Add("EO00", 9, 4, 0);
        dt.Rows.Add("EO02", 9, 4, 0);

        var caseId = new SqlParameter("caseid", SqlDbType.Int);
        caseId.Value = 1;

        var userId = new SqlParameter("userid", SqlDbType.UniqueIdentifier);
        userId.Value = Guid.Parse("846454D9-DE72-4EF4-ABE2-16EC3710EA0F");

        var warnings = new SqlParameter("warnings", SqlDbType.Structured);
        warnings.Value= dt;
        warnings.TypeName = "dbo.udt_Warnings";

        entities.ExecuteStoredProcedure("usp_RaiseWarnings_rs", userId, warnings, caseId);
    }
}

public static class ObjectContextExt
{
    public static void ExecuteStoredProcedure(this ObjectContext context, string storedProcName, params object[] parameters)
    {
        string command = "EXEC " + storedProcName + " @caseid, @userid, @warnings";

        context.ExecuteStoreCommand(command, parameters);
    }
}

，存储过程如下所示：

ALTER PROCEDURE [dbo].[usp_RaiseWarnings_rs]
    (@CaseID int, 
     @UserID uniqueidentifier = '846454D9-DE72-4EF4-ABE2-16EC3710EA0F', --Admin
     @Warnings dbo.udt_Warnings READONLY
)
AS

，用户定义的表格如下所示：

and the user-defined table looks like this:

CREATE TYPE [dbo].[udt_Warnings] AS TABLE(
    [WarningCode] [nvarchar](5) NULL,
    [StatusID] [int] NULL,
    [DecisionID] [int] NULL,
    [Criticality] [int] NULL DEFAULT ((0))
)

包括：

1. 您传入 ExecuteStoreCommand 的参数必须与您的存储过程中的参数


2. 即使具有默认值，您必须将每一列都传递到用户定义的表中。所以看来我的UDT上没有IDENTITY（1,1）NOT NULL列

1. The parameters you pass into ExecuteStoreCommand have to be in order with the parameters in your stored procedure
2. You have to pass every column in to your user-defined table, even if they are have defaults. So it seems i couldn't have a IDENTITY(1,1) NOT NULL column on my UDT

这篇关于实体框架存储过程表值参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！