Value Parameter for Entity Framework Stored Procedure Table

c# ef-code-first entity-framework stored-procedures table-valued-parameters


I'm attempting to invoke a stored method that takes a parameter for a table value. Although Entity Framework doesn't currently natively allow this, from what I gather, you can still achieve it by utilizing theExecuteStoreQuery the command off ofObjectContext . I have the following in a generic entity framework repository.ExecuteStoredProcedure method:

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

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

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

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

The command string is completed as follows:

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

This technique fails when used to a stored procedure that takes a table-valued argument. According to what I read in here, the parameters have to be of typeSqlParameter moreover, the table-valued parameter must have theSqlDbType set toStructured . I performed this, and I received the following error:

The table type parameter p6 must have a valid type name

I thus update the SqlParameter.TypeName to the name of the user-defined type I generated on the database, and when I execute the query, I get the really useful error as follows:

Incorrect syntax near '0'.

If I go back to ADO.NET and run a data reader, I can get the query to run, but I was expecting to use the data context to make it work.

Can a table value argument be sent usingExecuteStoreQuery ? Additionally, I am really casting the Entity Framework Code First andDbContext to anObjectContext to obtainExecuteStoreQuery accessible technique Is this required, or may I disregard the rules andDbContext and also?

10/23/2017 12:06:24 PM

Accepted Answer


Support for this has been added to the Nuget Package (EF4,EF5,EF6)

For code samples, see the GitHub repository.

Though somewhat off-topic, this information is nonetheless helpful for anyone attempting to pass user-defined tables into a stored procedure. I came up with this after experimenting with Nick's example and other Stackoverflow posts:

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

        var dt = new DataTable();

        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 saved process is described as follows:

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

and this is how the user-defined table appears:

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

The limitations I discovered include:

  1. the criteria you provideExecuteStoreCommand must match up with the stored procedure's parameters.
  2. Even if a column has default values, you must include them in the user-defined table. It would seem that my UDT couldn't contain an IDENTITY(1,1) NOT NULL field.
12/24/2016 9:37:48 AM

Popular Answer

I'll give you a 2018 revision now. end-to-end explanation of how to use Entity Framework's stored method with table argument, absence of Nuget packages

I'm using SQL Server 2012, VS 2017, and EF 6.xx.

1. The value of your table parameter

Imagine you have a simple table type specified like this (just one column)

create type GuidList as table (Id uniqueidentifier)

2. The method you stored

and a stored method with several inputs, such as:

create procedure GenerateInvoice
    @listIds GuidList readonly,
    @createdBy uniqueidentifier,
    @success int out,
    @errorMessage nvarchar(max) out
    set nocount on;

    begin try
    begin tran;  

    -- Your logic goes here, let's say a cursor or something:
    -- declare gInvoiceCursor cursor forward_only read_only for
    -- bla bla bla
    --  if (@brokenRecords > 0)
    --  begin
    --      RAISERROR(@message,16,1);
    --  end

    -- All good!
    -- Bonne chance mon ami!

    select @success = 1
    select @errorMessage = ''

    end try
    begin catch  
        --if something happens let's be notified
        if @@trancount > 0 
            rollback tran;  

        declare @errmsg nvarchar(max)
        set @errmsg =       
            (select 'ErrorNumber: ' + cast(error_number() as nvarchar(50))+
            'ErrorSeverity: ' + cast(error_severity() as nvarchar(50))+
            'ErrorState: ' + cast(error_state() as nvarchar(50))+
            'ErrorProcedure: ' + cast(error_procedure() as nvarchar(50))+
            'ErrorLine: ' + cast(error_number() as nvarchar(50))+
            'error_message: ' + cast(error_message() as nvarchar(4000))
        --save it if needed

        print @errmsg

        select @success = 0
        select @errorMessage = @message

    end catch;

    --at this point we can commit everything
    if @@trancount > 0 
        commit tran;  


How to utilize this stored procedure in SQL, step 3

You may use something like that in SQL:

declare @p3 dbo.GuidList
insert into @p3 values('f811b88a-bfad-49d9-b9b9-6a1d1a01c1e5')
exec sp_executesql N'exec GenerateInvoice @listIds, @CreatedBy, @success',N'@listIds [dbo].[GuidList] READONLY,@CreatedBy uniqueidentifier',@listIds=@p3,@CreatedBy='FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF'

4. Use this stored procedure in C#

Here's how you use Entity Framework's WebAPI to invoke that stored procedure:

    [AuthorizeExtended(Roles = "User, Admin")]
    public async Task<IHttpActionResult> GenerateInvoices(BillingToDoGenerateInvoice model)
            using (var db = new YOUREntities())
                //Build your record
                var tableSchema = new List<SqlMetaData>(1)
                    new SqlMetaData("Id", SqlDbType.UniqueIdentifier)

                //And a table as a list of those records
                var table = new List<SqlDataRecord>();

                for (int i = 0; i < model.elements.Count; i++)
                    var tableRow = new SqlDataRecord(tableSchema);
                    tableRow.SetGuid(0, model.elements[i]);

                //Parameters for your query
                SqlParameter[] parameters =
                    new SqlParameter
                        SqlDbType = SqlDbType.Structured,
                        Direction = ParameterDirection.Input,
                        ParameterName = "listIds",
                        TypeName = "[dbo].[GuidList]", //Don't forget this one!
                        Value = table
                    new SqlParameter
                        SqlDbType = SqlDbType.UniqueIdentifier,
                        Direction = ParameterDirection.Input,
                        ParameterName = "createdBy",
                        Value = CurrentUser.Id
                    new SqlParameter
                        SqlDbType = SqlDbType.Int,
                        Direction = ParameterDirection.Output, // output!
                        ParameterName = "success"
                    new SqlParameter
                        SqlDbType = SqlDbType.NVarChar,
                        Size = -1,                             // "-1" equals "max"
                        Direction = ParameterDirection.Output, // output too!
                        ParameterName = "errorMessage"

                //Do not forget to use "DoNotEnsureTransaction" because if you don't EF will start it's own transaction for your SP.
                //In that case you don't need internal transaction in DB or you must detect it with @@trancount and/or XACT_STATE() and change your logic
                await db.Database.ExecuteSqlCommandAsync(TransactionalBehavior.DoNotEnsureTransaction,
                    "exec GenerateInvoice @listIds, @createdBy, @success out, @errorMessage out", parameters);

                //reading output values:
                int retValue;
                if (parameters[2].Value != null && Int32.TryParse(parameters[2].Value.ToString(), out retValue))
                    if (retValue == 1)
                        return Ok("Invoice generated successfully");

                string retErrorMessage = parameters[3].Value?.ToString();

                return BadRequest(String.IsNullOrEmpty(retErrorMessage) ? "Invoice was not generated" : retErrorMessage);
        catch (Exception e)
            return BadRequest(e.Message);

I hope it's useful! 🙂

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow