How do I call stored procedures in EF Core 6 using named parameters with FromSqlRaw-CodePudding

I want to execute a stored procedure with a minimum of 3 to 20 parameters. I am converting the property values from a request model to an SQL string and a list of parameters. Then make the call and expect a result but when I check the Created SQL from debug view, I observe that the EF tries to call the sp with non-named style parameters.

For brevity assume the first 3 properties contain values, and other properties have null values.

RequestModel 
{
   string Property1 { get; set; }
   string Property2 { get; set; }
   string Property3 { get; set; }
   ...
}

ResponseModel 
{
   string Response1 { get; set; }
   string Response2 { get; set; }
   ...
}

var sqlString = "exec sp_dummy @prop1, @prop2, @prop3";
var params = new SqlParameter[] { new SqlParameter("@prop1", Property1),  new SqlParameter("@prop2", Property2),  new SqlParameter("@prop3", Propery3) };

return await ResponseModel.FromSqlRaw(sqlString, params).ToListAsync();

And when I check the generated SQL from EF Core, it is as follows:

DECLARE @prop1 nvarchar(10) = N'Prop1Data';
DECLARE @prop2 nvarchar(10) = N'Prop2Data';
DECLARE @prop3 nvarchar(10) = N'Prop3Data';

exec dummy_sp @prop1, @prop2, @prop3

What I want to achieve is as follows:

exec dummy_sp @prop1 = N'Prop1Data' , @prop2 = N'Prop2Data', @prop3 = N'Prop3Data'

CodePudding user response：

I have achieved this by using dbcontext's ExecuteSqlRaw method, instead of FromSqlRaw extension method.

Database.ExecuteSqlRaw(sql,params);