1 year ago

#63322

test-img

mo_maat

EF Core Execute SqlRaw for stored procedure with multiple user defined table types

I am getting an error when trying to execute a raw SQL query in EF Core 3.1. The error is:

Message "Input string was not in a correct format."

My stored procedure looks like this:

CREATE PROCEDURE GetData
    @IdsTbl IntTblType READONLY,
    @StringTbl StringTblType READONLY
AS

My .net setup:

var prmIds = new SqlParameter("@IdsTbl", SqlDbType.Structured);
prmIds.TypeName = "IntTblType";

DataTable idsTable = new DataTable();

DataColumn dc = new DataColumn("Id");
idsTable.Columns.Add(dc);

foreach (int item in filter.Ids)
{
    DataRow dr = idsTable.NewRow();
    dr[0] = item;
    idsTable.Rows.Add(dr);
}
prmIds.Value = idsTable;

var prmUsers = new SqlParameter("@StringTbl", SqlDbType.Structured);
prmUsers.TypeName = "StringTblType";

DataTable userNameTable = new DataTable();
DataColumn dcUserName = new DataColumn("Id");
userNameTable.Columns.Add(dcUserName);

foreach (string item in filter.UserName)
{
     DataRow dr = userNameTable.NewRow();
     dr[0] = item;
     userNameTable.Rows.Add(dr);
}
prmUsers.Value = userNameTable;

var result = await _context.DataModel.FromSqlRaw(
            //"EXECUTE GetData {0}, {1}", prmIds, prmUsers).AsNoTracking()
            "EXECUTE GetData @IdsTbl = {prmIds}, @StringTbl = {prmUsers}").AsNoTracking()
            //"EXECUTE GetData prmIds, prmUsers").AsNoTracking()
            .ToListAsync();

sql-server

ef-core-3.1

0 Answers

Your Answer

Accepted video resources