EF: When executing a command, parameters must be exclusively database parameters or values

Question

When executing stored procedure without return value but with a user-defined table type like this:

var dbStrings = new SqlParameter("@data", SqlDbType.Structured)
{
    TypeName = new StringListType().Name,
    Value = new StringListType().DataRecordsFrom(listOfStrings.Select(p => p.Value).ToList())
 };
_dbContext.Database.ExecuteSqlCommand("exec [dbo].[InsertRecords] @SID, @filerType, @data", sid.ToString(), "Field", dbStrings);

I get an error

When executing a command, parameters must be exclusively database parameters or values

How can I execute stored procedure through DbContext with multiple input parameters (some UDTT)?

this should help stackoverflow.com/a/46711751/1236044

jbl
– jbl

2018-08-08 16:31:53 +00:00
Commented Aug 8, 2018 at 16:31 — jbl
– jbl, Commented Aug 8, 2018 at 16:31

krs · Accepted Answer · 2018-08-08 15:53:50Z

3

In this case you need to (as error message tells you) create an array of SqlParameter and pass this when calling ExecuteSqlCommand.

var sqlParams = new SqlParameter[]
{
    new SqlParameter("@SID", sid.ToString()),
    new SqlParameter("@filterType", "Pin"),
    new SqlParameter("@data", SqlDbType.Structured)
    {
        TypeName = new StringListType().Name,
        Value = new StringListType().DataRecordsFrom(listOfStrings.Select(p => p.Value).ToList())
    }
};

_dbContext.Database.ExecuteSqlCommand("exec [dbo].[InsertRecords] @SID, @filterType, @data", sqlParams);

answered Aug 8, 2018 at 15:53

krs

5993 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

EF: When executing a command, parameters must be exclusively database parameters or values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related