entity-framework-stored-procedure-table-value-parameter

http://stackoverflow.com/questions/8157345/entity-framework-stored-procedure-table-value-parameter

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 store proc. 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.ExecuteStoreProcedure("usp_RaiseWarnings_rs", userId, warnings, caseId);       } }   public static class ObjectContextExt {     public static void ExecuteStoreProcedure(this ObjectContext context, string storeProcName, params object[] parameters)     {         string command = "EXEC " + storeProcName + " @caseid, @userid, @warnings";           context.ExecuteStoreCommand(command, parameters);     }   } and the Store proc looks like the following... 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 the following... 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 store proc 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 shareimprove this answer edited Jun 24 '14 at 11:24 answered Mar 23 '12 at 10:50 Mike 1,6381525

This is exactly what we ended up doing. Sorry I didn't update the post with the solution. Thanks for taking time to do it! I have awarded you the correct answer. – Nick Olsen Mar 23 '12 at 14:36 Thanks, your question helped send me down the correct path :) – Mike Mar 23 '12 at 15:13 @Mike How this can be done in DataContext? – ssilas777 Nov 28 '12 at 9:15 I'm not sure ssilas777, I would assume the underlying data access components of the DataContext are similar to either a DbContext or ObjectContext. I'll look at supporting this with the NuGet package i've put up on Nuget :) – Mike Nov 28 '12 at 10:28 was it Db context or entity context? can someone help me? – Meghana Mathur A  May 9 '14 at 6:53