By Nick Olsen


2011-11-16 19:16:38 8 Comments

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?

6 comments

@Pavel Kovalev 2018-05-14 21:39:55

Okay, so here is a 2018 update: end to end solution which describes how to invoke stored procedure with table parameter from Entity Framework without nuget packages

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

1. Your Table Value prameter

Let's say you have a simple table type defined like this (just one column)

go
create type GuidList as table (Id uniqueidentifier)

2. Your Stored procedure

and a stored procedure with several parameters like:

go
create procedure GenerateInvoice
    @listIds GuidList readonly,
    @createdBy uniqueidentifier,
    @success int out,
    @errorMessage nvarchar(max) out
as
begin
    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 
        begin
            rollback tran;  
        end

        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

        return;
    end catch;

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

end
go

3. SQL Code to use this stored procedure

In SQL you would use something like that:

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',@[email protected],@CreatedBy='FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF'

4. C# Code to use this stored procedure

And here is how you can call that Stored Procedure from Entity Framework (inside WebAPI):

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

                //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]);
                    table.Add(tableRow);
                }

                //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 helps! 🙂

@souvik sett 2018-02-03 06:46:51

var sqlp = new SqlParameter("@param3", my function to get datatable);
sqlp.SqlDbType = System.Data.SqlDbType.Structured;
sqlp.TypeName = "dbo.mytypename";

  var v = entitycontext.Database.SqlQuery<bool?>("exec [MyStorProc] @param1,@param2,@param3,@param4", new SqlParameter[]
                    {
                        new SqlParameter("@param1",value here),
                        new SqlParameter("@param2",value here),

                        sqlp,
                        new SqlParameter("@param4",value here)

                    }).FirstOrDefault();

@Sid 2018-02-03 07:12:08

Please add some context around the code and format the code.

@souvik sett 2018-02-04 16:26:04

you can just copy paste it. I'm new here, I dont know what you are telling.

@Sid 2018-02-05 05:23:12

What I mean is the code is not formatted well and why this will work in your opinion should be mentioned.

@souvik sett 2018-02-06 09:45:25

Not "it will work", it is working already, I'm using it.

@Sid 2018-02-06 09:49:08

Sure, but a reader would like context around this.

@souvik sett 2018-02-06 09:58:26

I don't know how to describe here. Actually the param3 needed to be declared as System.Data.SqlDbType.Structured;. So I initiate that out side the parameter declaration scope and used the variable in that scope.

@Andrey Borisko 2012-09-28 23:13:15

I want to share my solution on this problem:

I have stored procedures with several table value parameters and I found out that if you call it this way:

var query = dbContext.ExecuteStoreQuery<T>(@"
EXECUTE [dbo].[StoredProcedure] @SomeParameter, @TableValueParameter1, @TableValueParameter2", spParameters[0], spParameters[1], spParameters[2]);
var list = query.ToList();

you get a list with no records.

But I played with it more and this line gave me an idea:

var query = dbContext.ExecuteStoreQuery<T>(@"
EXECUTE [dbo].[StoredProcedure] 'SomeParameterValue', @TableValueParameter1, @TableValueParameter2",  spParameters[1], spParameters[2]);
var list = query.ToList();

I changed my parameter @SomeParameter with its actual value 'SomeParameterValue' in command text. And it worked :) This means that if we have something else than SqlDbType.Structured in our parameters it doesn't pass them all correctly and we get nothing. We need to replace actual parameters with their values.

So, my solution looks as follows:

public static List<T> ExecuteStoredProcedure<T>(this ObjectContext dbContext, string storedProcedureName, params SqlParameter[] parameters)
{
    var spSignature = new StringBuilder();
    object[] spParameters;
    bool hasTableVariables = parameters.Any(p => p.SqlDbType == SqlDbType.Structured);

    spSignature.AppendFormat("EXECUTE {0}", storedProcedureName);
    var length = parameters.Count() - 1;

    if (hasTableVariables)
    {
        var tableValueParameters = new List<SqlParameter>();

        for (int i = 0; i < parameters.Count(); i++)
        {
            switch (parameters[i].SqlDbType)
            {
                case SqlDbType.Structured:
                    spSignature.AppendFormat(" @{0}", parameters[i].ParameterName);
                    tableValueParameters.Add(parameters[i]);
                    break;
                case SqlDbType.VarChar:
                case SqlDbType.Char:
                case SqlDbType.Text:
                case SqlDbType.NVarChar:
                case SqlDbType.NChar:
                case SqlDbType.NText:
                case SqlDbType.Xml:
                case SqlDbType.UniqueIdentifier:
                case SqlDbType.Time:
                case SqlDbType.Date:
                case SqlDbType.DateTime:
                case SqlDbType.DateTime2:
                case SqlDbType.DateTimeOffset:
                case SqlDbType.SmallDateTime:
                    // TODO: some magic here to avoid SQL injections
                    spSignature.AppendFormat(" '{0}'", parameters[i].Value.ToString());
                    break;
                default:
                    spSignature.AppendFormat(" {0}", parameters[i].Value.ToString());
                    break;
            }

            if (i != length) spSignature.Append(",");
        }
        spParameters = tableValueParameters.Cast<object>().ToArray();
    }
    else
    {
        for (int i = 0; i < parameters.Count(); i++)
        {
            spSignature.AppendFormat(" @{0}", parameters[i].ParameterName);
            if (i != length) spSignature.Append(",");
        }
        spParameters = parameters.Cast<object>().ToArray();
    }

    var query = dbContext.ExecuteStoreQuery<T>(spSignature.ToString(), spParameters);


    var list = query.ToList();
    return list;
}

The code surely could be more optimized but I hope this will help.

@Mike 2012-03-23 10:50:05

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

@Nick Olsen 2012-03-23 14:36:19

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.

@Mike 2012-03-23 15:13:37

Thanks, your question helped send me down the correct path :)

@ssilas777 2012-11-28 09:15:56

@Mike How this can be done in DataContext?

@Mike 2012-11-28 10:28:42

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

@Meghana Mathur A 2014-05-09 06:53:55

was it Db context or entity context? can someone help me?

@Mike 2014-05-09 07:02:35

my example uses the ObjectContext, but this can be completed on the DbContext.Database object.

@Toby Couchman 2014-06-13 09:13:28

I think that it's the creation of the ado DataTable which seems to pollute the most. See my answer below for creating/populating the table directly from your IEnumerable<T>

@Mike 2014-06-13 15:55:52

thats why i put that part of the work away into a nuget package, allowing you to do the easy bit :) github.com/Fodsuk/EntityFrameworkExtras

@Vaibhav 2014-12-09 10:28:49

Hello Mike, I was successfully able to invoke the SProc using your Nuget package. However, not able to map the result set to a similar model using the generic ExecuteStoreProcedure<T> method. The collection is returned but all the properties are null. Would you be able to help?

@Vaibhav 2014-12-09 11:00:51

Resolved it! The TResult convention is to have setters instead of fields. The following link was helpful.. thanks! msdn.microsoft.com/en-us/library/vstudio/…

@Toby Couchman 2014-06-13 09:08:52

The DataTable approach is the only way, but constructing a DataTable and populating it manually is fugly. I wanted to define my DataTable directly from my IEnumerable in a style similar to EF's fluent model builder thingy. So:

var whatever = new[]
            {
                new
                {
                    Id = 1,
                    Name = "Bacon",
                    Foo = false
                },
                new
                {
                    Id = 2,
                    Name = "Sausage",
                    Foo = false
                },
                new
                {
                    Id = 3,
                    Name = "Egg",
                    Foo = false
                },
            };

            //use the ToDataTable extension method to populate an ado.net DataTable
            //from your IEnumerable<T> using the property definitions.
            //Note that if you want to pass the datatable to a Table-Valued-Parameter,
            //The order of the column definitions is significant.
            var dataTable = whatever.ToDataTable(
                whatever.Property(r=>r.Id).AsPrimaryKey().Named("item_id"),
                whatever.Property(r=>r.Name).AsOptional().Named("item_name"),
                whatever.Property(r=>r.Foo).Ignore()
                );

I've posted the thing on dontnetfiddle: https://dotnetfiddle.net/ZdpYM3 (note that you can't run it there because not all of the assemblies are loaded into the fiddle)

@Cosmin Onea 2011-11-24 17:43:17

Change your string concatenation code to produce something like:

EXEC someStoredProcedureName @p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7

@dasblinkenlight 2013-02-06 11:31:32

OP said that EXEC someStoredProcedureName {0},{1},{2},{3},{4},{5},{6},{7} does not work, so parameterizing it wouldn't work either.

Related Questions

Sponsored Content

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

28 Answered Questions

[SOLVED] Fastest Way of Inserting in Entity Framework

17 Answered Questions

[SOLVED] Entity Framework vs LINQ to SQL

25 Answered Questions

1 Answered Questions

7 Answered Questions

[SOLVED] Entity Framework 5 Updating a Record

1 Answered Questions

0 Answered Questions

How can I pass table valued parameter to a stored procedure?

1 Answered Questions

[SOLVED] Stored Procedures in Entity Framework

Sponsored Content