run a stored procedure in EF's ExecuteStoreQuery Is this an EF bug?

entity-framework

Question

trying to execute the stored proc in EF using the following code:

var params = new object[] {new SqlParameter("@FirstName", "Bob")};
return this._repositoryContext.ObjectContext.ExecuteStoreQuery<ResultType>("GetByName", params);

but keep getting this error:

Procedure or function 'GetByName' expects parameter '@FirstName', which was not supplied.

and from sql profiler:

exec sp_executesql N'GetByName',N'@FirstName nvarchar(100),@FirstName=N'Bob'

what is wrong wit the above ExecuteStoreQuery code?

1
4
4/24/2012 12:01:06 AM

Accepted Answer

Ignoring the fact that params is a reserved word...

Think your query needs to be:

var params = new object[] {new SqlParameter("@FirstName", "Bob")};
return this._repositoryContext.ObjectContext.ExecuteStoreQuery<ResultType>("exec GetByName @FirstName", params);

Should also say that if that proc is a standard part of your database and data model then you should import it into your EDM so it's available directly on your context.

10
4/23/2012 11:59:43 PM

Popular Answer

Use the ExecuteFunction instead of ExecuteStoreQuery which is more suitable for the "ad-hoc" queries.

var parameters = new ObjectParameter[] {new ObjectParameter("FirstName", "Bob")};
return this._repositoryContext.ObjectContext.ExecuteFunction<ResultType>("GetByName", parameters);

The stored procedures can also be mapped as function in the context and thus can be used as typed method. Take a look at Using stored procedures with Entity Framework.



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