Entity Framework Stored Procedure Table Value Parameter

Question

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?

Answer 1

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