Using SQL Server named parameters with ExecuteStoreQuery and ExecuteStoreCommand

entity-framework sql-server

Question

I'm attempting to utilize named SQL Server parameters withObjectContext.ExecuteStoreQuery and ObjectContext.ExecuteStoreCommand while directly using a stored method or function.

The named parameters in SQL Server are different from those in Entity Framework, but they still let me run a query like this one:

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

In contrast to Entity Framework named parameters, which are evaluated in sequence, the order of the arguments in this query is irrelevant.

I want to utilize SQL Server named parameters with Entity Framework so that the code in Entity Framework that calls the stored procedure won't be impacted if the parameters' order changes.

1
11
10/23/2012 6:14:03 PM

Accepted Answer

The query text has to be configured as a parameterized query in order for this to perform as expected. Making ensuring that your parameters are called differently from SP parameters is the tough part.

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);
21
10/25/2012 6:56:33 PM

Popular Answer

As a heads up, avoid naming your stored procedures with the prefix sp_ as that is how System stored procedures are named. Because there are numerous stored procs and calls to them, the SQL server will first search through all system stored procs before locating your definition, which will make it seem slower in production.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow