Efcore: FromSql, stored procedures and named parameters

[dubeg]

One of the ways to execute a Stored Procedure that returns entities is by registering the entity's class in the DbContext, and then calling DbContext.Set<Entity>().FromSql(...). I am a newcomer to EF Core, and I was struggling to understand why my StoredProc wasn't returning anything.

Let's say we have a stored procedure as defined below:

CREATE PROCEDURE dbo.GetRequiredDocuments(
    @OrderNo INT,
    @Entity VARCHAR(10)
)
...

Now, here's how I tried executing this SP using EFCore 2.0:
```c#
var paramEntity = new SqlParameter("@Entity", entity);
var paramOrderNo = new SqlParameter("@OrderNo", orderNo);
return await this.Set().AsNoTracking()
//.FromSql("EXECUTE dbo.GetRequiredDocuments @OrderNo, @Entity;", paramOrderNo, paramEntity) // Works.
//.FromSql("EXECUTE dbo.GetRequiredDocuments @OrderNo, @Entity;", paramEntity, paramOrderNo) // Works.
//.FromSql("EXECUTE dbo.GetRequiredDocuments @Entity, @OrderNo;", paramEntity, paramOrderNo) // Doesn't work.
.ToListAsync();

At first, I expected that parameters would be rendered in the form `@ParamName = paramValue`. 
Ex:
```sql
EXECUTE dbo.GetRequiredDocuments @Entity = <entity>, @OrderNo = <orderNo>; -- Correct
EXECUTE dbo.GetRequiredDocuments @OrderNo = <orderNo>, @Entity = <entity>; -- Correct

However, it seems the parameters' names in the query string are simple placeholders, which are replaced by the parameters' values, so you need to take care of correctly ordering the parameters in the query string.

EXECUTE dbo.GetRequiredDocuments <orderNo>, <entity>; -- Correct
EXECUTE dbo.GetRequiredDocuments <entity>, <orderNo>; -- Incorrect

I thought that was odd. The benefit of named parameters is that it removes the concern of ordering parameters when calling a StoredProc. As it stands, passing an array of SqlParameter to FromSql is not much handier than simply doing:
c# .FromSql("EXECUTE dbo.GetRequiredDocuments @Entity={0}, @OrderNo={1}", entity, orderNo)
However, I think SqlParameter(s) take care of sanitizing their value.

Conclusion

I admit, my mistake was to incorrectly assume the way parameters are rendered. I got confused by some posts associating passing SqlParameter(s) to FromSql with the concept of named parameters.

What I'm asking, then, is a way to render parameters with their name in the SQL command string.
A special type similar to SqlParameter that FromSql could recognize to render params in the form @ParamName = @paramValue, perhaps?

[Tarig0]

I think it would make sense to have the provider handle how the mapping works. For example ODBC didn't have the variable name as an option. Definitely want add a vote for if the provider can to use the @{name} to map to a named variable if avaliable

[ajcvickers]

@dubeg The SQL syntax used in the FromSql call above is specifying the parameters be passed to the stored procedure in that order. This syntax does not do any named parameter binding. It would likely be possible to add support for named parameter binding to EF, but this would require telling EF that this is a stored procedure call, and then the application also providing different syntax--i.e. not calling the stored proc like it is above, but instead just passing the name. Note that this can already be done manually in ADO.NET by changed the command time and using the appropriate syntax. However, as of now we don't plan to implement this in EF because the concept count is increased for minimal additional value.

[Kabindas]

I know this topic is closed, but I could not help but say that I'm sad to see how you handle stored procedures and the absence of named parameters. It seems a step back from object programming in the direction of magic strings. Not cool :(

[retailonline]

Well. "the concept count is increased for minimal additional value." I disagree, it would add a lot of value if we can have named parameters.

[abcox]

It would be a great addition to support named param matching. Until then, I do not pass in object[] of parameters to FromSql. Rather, I parse the exec proc sql with params using a SqlParameter[] and extension.