1 year ago
#63322
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