Using SQL Server named parameters with ExecuteStoreQuery and ExecuteStoreCommand

Question

I am trying to use SQL Server named parameters with ObjectContext.ExecuteStoreQuery and ObjectContext.ExecuteStoreCommand when calling a stored procedure or a function directly.

SQL Server named parameters are not the same with the Entity Framework named parameters - they allow me to execute a query similar with this one:

EXEC sp_GetData @firstParameter = 1, @thirdParameter = 2, @secondParameter = 1

The order of the parameters in this query does not matter as opposed with Entity Framework named parameters that are evaluated in order.

I want to use SQL Server named parameters with Entity Framework so that if the order of the parameters is changed in the stored procedure the Entity Framework code calling it is not affected.

Answer 1

In order to get this to work as you would expect, then you need to set up the query text as a parameterized query. The tricky part is that you just need to make sure your parameters are named differently than the SP parameters:

var cmdText = "[DoStuff] @Name = @name_param, @Age = @age_param";
var @params = new[]{
   new SqlParameter("name_param", "Josh"),
   new SqlParameter("age_param", 45)
};

ObjectContext.ExecuteStoreQuery<MyObject>(cmdText, @params);

Answer 2

FYI - do not name your stored procedures to start with sp_ as that's how System stored procs are named. The result is that the SQL server will look through all system stored procs first before finding your definition and will seem slower in production with many stored procs and calls of them.