Executing a stored procedure with an output parameter using Entity Framework
Entity Framework does support stored procedures with output parameters.
It's simple, as you will see in my code example below.
Allow me to debunk the widespread misconceptions about support in EF for stored procs with output params. A search for relevant keywords today yields almost 70,000 results in Google. There are thousands of web pages where people have asked how to do this. The top result was the MSDN forum, where the accepted answer to this question says "Sorry to give you bad new but EF does not support output parameters" and proposes reverting to conventional ADO.NET for this scenario, which is fine, but overlooks this feature of EF. Other typical answers from the top ten results include "in short: do not use entity framework yet", etc.
There's no reason why you would ever need to use an output parameter to return a value from a stored procedure with Entity Framework. However, if you want to do so, e.g. to gain the benefits of EF with a legacy database, there are various ways to harness this capability in Entity Framework. I'll demonstrate the simplest method here. (EF can provide several important benefits, e.g. improved performance; easier, more rapid development; etc.)
Example: using Entity Framework to execute a stored procedure and get the output parameter
var model = new BlogEntities();
var newIds = model.proc_BlogPostInsert(idParameter, "Post title");
int newId = newIds.FirstOrDefault().Id;
- Create a table called NewId containing one column named "Id" of type int.
- Update your model from the database. This gives you a NewId entity that you can use to get the Id of newly created records in any table with a primary key named "Id".
- Add one line of T-SQL to the end of your stored procedure to return an entity:
SELECT @Id = SCOPE_IDENTITY() AS Id; - You can then get the primary key of the newly created record either from the output parameter of the stored procedure, or from the returned entity. (I do both in my example, but obviously you'll want to remove the unnecessary extra code.) The latter is in line with standard practice in ORM, and hence more consistent with the way EF maps database objects to application entities.
In conclusion, it's easy to get the value of an output parameter of a stored procedure using EF. In new development projects where we want to adopt an ORM architecture, we have no reason to use output parameters in the way we might have previously. However, I do request that Microsoft adds more features to EF for handling stored proc output params for developers who want to use EF with legacy databases which weren't designed with ORM principles in mind.
23 July 2009
Tags: entity framework ef stored procedures insert output parameters stored procedure output parameter orm sql server ado.net tutorial example code
Comments: 8
Add Comment
So I need to tell the DBA that I have to create a useless new table just so I can return a value from a stored proc? Woah.. that's seriously messed up. Even without a DBA enforcing things that, uh, make sense.. I would never do this.
I seriously don't know how to import a stored proc that returns a scalar value or materializes to a class which is not in the EF model. Right now I am using EF extensions which does this just fine.
Please fix this... Thanks
I would agree with you that ADO.NET Entity Framework Extensions is an ideal way of achieving this objective -- along with many other useful features.
For new projects would I tend not to use output parameters for returning data sets mapped to entities. For me it output parameters dont fit neatly enough with ORM principles.
"There's no reason why you would ever need to use an output parameter to return a value from a stored procedure with Entity Framework."
Dude. Did you even read this before you typed it?
Hello Mason, yes, I've sent you an email asking for more detail from you, so I can respond to any specific points that you have in mind. :)
I am trying to implement paging using Entity Framework V4. The reason for this is, I need to query 3 tables when user search for a string. And I show the results in ExtJs listview. I need to get TotalRecords as output parameter, so that I can show that how many total records found in database for the given search string. So, my DBA wrote a stored proceud that take 'searc string', 'take', 'skip' and 'totalRecords output' parameters. But I am always getting NULL value for given out parameter. Any ideas on this will be highly appreciated.
The stored procedure sounds like a nice solution to that problem. You should be able to get around the problem of output parameters ihn EF using the approach I outline above. However, do consider a solution that doesn't involve an output parameter. Basic pagination can be achieved using EF and Linq using queries with order or skip. That's how I do the pagination of my list of blog posts on this very website. It's simple and effective,
Pagination is such a common task, I I feel that there should be a view and/or user control for this built-into ASP.NET. I submitted a feature request for a ready-made paging solution in ASP.NET MVC.
I´m making a advanced search with dynamic sql where I can send ID´s or various columns on several related objects. The sql is dymaicly buildand I´m using paging inside the stored procedure. I´m using the OUTPUT parameter to count the results from the query but I only retreive the paging size. The function in my BusinessLayer is as follows: GenericOrderSearch(string OrderIDList, string InvoiceIDList, string ContactName, string ContactAddress, string ContactCity, string ContactZipCode, string InvoiceTotal, string SaleName, string OrderStatusIDList, int pagesize, int startrow, string sort, ref int? count). See the count parameter (ref int?). And in my SP: "@count int OUTPUT". I can use this parameter using Linq to SQL straight off, but when refactoring my datalayer to EF4 I needed to create a ObjectParameter of my count paramater: ObjectParameter op = new ObjectParameter("count", typeof(int));. The thing is that it doesn´t work on EF. Does anyone have any suggestions or examples that actually works?
Hi Claes, yes, you can achieve this by following the same basic steps outlined in my blog post above. :)