C# – Entity Framework Stored Procedure Table Value Parameter

cef-code-firstentity-frameworkstored-procedurestable-valued-parameters

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 ExecuteStoredProcedure 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);
}

The command string ends up like this:

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

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

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'.

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.

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 DbContext as well?

Best Answer

UPDATE

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

Check out the GitHub repository for code examples.

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);
    }
}

and the stored procedure looks like this:

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))
)

Constraints I found include:

  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
Related Topic