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 idParameter = new ObjectParameter("Id", typeof(int));
var model = new BlogEntities();
var newIds = model.proc_BlogPostInsert(idParameter, "Post title");
int newId = newIds.FirstOrDefault().Id;
  1. Create a table called NewId containing one column named "Id" of type int.
  2. 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".
  3. Add one line of T-SQL to the end of your stored procedure to return an entity:
    SELECT @Id = SCOPE_IDENTITY() AS Id;
  4. 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

Share the love:

Comments: 26

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

Tim Acheson (30 Jul 09, 18:13)

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?

Tim Acheson (09 Dec 09, 10:48)

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.

Tim Acheson (01 Feb 10, 09:40)

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.

Claes-Philip Staiger (18 Jun 10, 09:26)

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?

Tim Acheson (19 Jun 10, 08:03)

Hi Claes, yes, you can achieve this by following the same basic steps outlined in my blog post above. :)


Tim, I don't think I agree with you that people would never need to do this with EF.

I have a stored proc that returns a rowset of data but also needs to return a 'current version' value (change tracking) as a bigint.

Note that you cannot do this with the return statement because that is always an integer. I do not want to pollute my returned data by having two rowsets, therefore I have to use a bigint output parameter.

I have found no way to do this in EF and, in fact, your example is not consistent with the title of the blog post, 'Entity Framework does support stored procedures with output parameters'.

How I get around the problem is to also use Linq2Sql in my database library and that does support output parameters. So for specific stored procs I am using Linq to Sql while using EF for everything else.

Tim Acheson (27 Sep 10, 12:59)

Hi Rob, EF isn't designed to work neatly with SPs with output params, although you can coerce it to do so with a simple tweak like the example in my original blog post above. The question you need to ask is, which entity does the output parameter belong to, and if there's no clear answer then perhaps that output parameter doesn't sit comfortably with an entity model.

To help me understand your situation, can you explain in more detail which entity(ies) your SP needs to return, and what the version number applies to?


"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."

Is that a serious comment? I don't design ANY database with ORM principles in mind - I design it with database principles in mind.

I'm stunned that EF doesn't have support for this most basic of features. Returning scalar values via 1-row resultsets with no discoverable metadata does not strike me as progress and I'm reluctant to pander to the needs of devs who think this stuff doesn't matter!

Don't mean to take my frustrations out on you Tim, I thank you for sharing the info.

@jamiet


"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"

Here's one. I'm using a sproc to insert a new row into a table with a generated primary key (using an identity, though in the future it will be a sequence).
I want to use an output param to return the generated value to the caller. I have no wish to resort to a resultset to return a scalar value!!

Tim Acheson (28 Jan 11, 09:53)

jamiet, I certainly share your desire to have support for output parameters in EF by default! MY statement about legacy databases was just a very polite attempt to emphasise that it would be helpful if EF was designed taking into consideration that people are using databases that weren't designed with EF in mind! But it can be useful to consider the ORM when designing a database. You might even prefer to adopt an SOA architecture and decide you don't want to use a relational database at all.

Your scenario of wanting to get the identity of a newly inserted row is obviously very typical. Many developers do this every day! EF does allow you to do that, but by default it doesn't do it with an output param. In many databases, this isn't done with an output param. Personally, I tend no to do this with an output param, but you can do. Have you tried the approach I suggest above to do what you need to do with EF?

rick piovesan (16 Mar 11, 15:00)

.. all right, I'll bite: and so how do you handle when you need to OUTPUT 3 values? eg, an INSERT sp that business requires to return Key, Version, and LastMode date ?

Tim Acheson (17 Mar 11, 15:14)

Hi Rick, fair question! I'd look at the model. This scenario is a little more than just an insert. You're arguably returning an entity possessing those fields, and an entity would be one way to handle it.

(What you've described isn't the norm, though. It's worth questioning why you want to do that on an insert and considering the architecture of the system. I'm an advocate of service oriented architecture (SOA), in which operations don't really need to work like this at all.)


"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."

Hi Tim, I'm fairly new to ORM and have been using Entity Framework for several months. Can you explain why you don't believe output parameters fit neatly with ORM principles? Also, I've been trying to find the differences between using output parameters vs. returning a row of results. Is there a performance benefit with using output parameters?

Tim Acheson (19 May 11, 12:32)

Hi David, To be less ambiguous, to me using a mixture of output parameters with returned data sets in a stored procedure doesn't fit comfortably with the notion of neatly mapping an object defined in code to a data object. It's not a strict rule of thumb. (Many developers don't like the idea of stored procedures of any kind for the ORM-based data access, but I think flexibility is good.) Output parameters may incur less performance overhead than that which may be associated with handling a data set, but it's not a big deal -- I've never heard of a system in which output params are used because of the performance benefits. Use what works best for you.


I struggle with the idea that someone designing a database would start with anything other than sound database design principles as the main consideration. As a DBA working in a environment where security is paramount, I would never give the application direct access to tables. Stored Procs should be used for all database access in these circumstances. Output parameters are 1 of several ways to return values, just as they are in a C# method. Why not support them? ORM tools are fine, but I'm often left with the impression that a significant driver is a desire not to be bothered with understanding all this tedious database stuff. If so, that is the road to a performance and security nightmare sometime in the future - probably long after the devloper has left!

Tim Acheson (03 Apr 12, 17:47)

Hi Andrew!

"start with ... database design principles as the main consideration"

Yep! Nobody's gonna argue with that! ;) Whether the db is relational, non-relational, SQL or NoSQL, flat file, or anything else, etc.

"I would never give the application direct access to tables. Stored Procs should be used for all database access"

I often hear that. I can appreciate where you're coming from. I would tend to agree. Security can also be controlled at table level. It's also good to have clearly-defined users and roles, with authentication via Active Directory. The application should authenticate as a specific user with access of a type and to only those objects needed for the current operation. If the application isn't writing to the db, it should be using a read-only user/role. Etc

"Output parameters are 1 of several ways to return values, just as they are in a C# method. Why not support them?"

Absolutely. Although, output params don't necessarily map intuitively to basic objects.

" ORM tools are fine, but I'm often left with the impression that a significant driver is a desire not to be bothered with understanding all this tedious database stuff."

I know what you mean. You only get the best from an ORM if you understand the underlying data access and in fact know how to write an ORM. There are clear advantages to an ORM, of course, like lazy loading, as well as productivity (not "re-inventing the wheel" even if you do possess the knowledge to do so).

Thanks for sharing your thoughts -- good points! :)


may be I'm missing something, seems to me I have to do more work when I deal with traditon db that works with aplication and tht the db has complex select returning columns from various table as resultset. is there a way that I map t such stored procedure without manual defining virtual enttity for each of theese type of store procedure


I came across this blog thinking there was an answer but what I found is a complete lack of understanding how high transactional systems can function through the use of highly optimized queries. The almost complete dismissal of the use of OUTPUT parameters, is more or less shocking.

ORM's work great in their little perfect world, where you are working with small tables and low transactional volume. I like to use ORM's almost completely for their ability to normalize the entire DAL. I would state that there is no way in hell I would ever put our billion dollar website in a position where applications have direct access to tables.

I am sorry but anonymous users don't have an AD account. Giving db_datareader and db_datawriter is what I would call DBA suicide. Access must be limited to what that Stored Procedure has been coded to do IMHO.

Show me in any ORM how you can specify an Index Hint or specify the maximum degrees of parrallelism or limit the amount of unnecessary concurrency with a NOLOCK hint? Only via a Stored Procedure, which doesn't seem to fit the ORM model if only as an after-thought.

Instead of being an apologist for the short-commings of a clearly lacking methodology, we should be demanding that MS fix this crap.

Tim Acheson (20 Mar 13, 08:31)

Hi Eric, I agree with every technical point you've made. Don't get me wrong -- I have no implicit objection to output parameters, they;re nice to have. As for " a complete lack of understanding how high transactional systems" etc, this topic is not mentioned in my post and would be beyond the scope of this discussion.

Pathak Trilok M (23 Sep 13, 06:58)

Do you have any Solution of the error says "cannot convert from 'long?' to 'System.Data.Objects.ObjectParameter'
"

Tim Acheson (04 Oct 13, 12:50)

@Pathak Try the framework's built-in conversions.

Parth Shah (14 Mar 15, 09:54)

I ran into this problem recently myself and found a solution to this problem on MSDN website. Posting it here just in case someone else ends up on this page: https://msdn.microsoft.com/en-us/library/vstudio/bb896334(v=vs.100).aspx.


You completed a number of fine points there. I did a search on the theme and found the majority of folks will agree with your blog. cakaddgcckgekeca

Tags:


  • Twitter
  • LinkedIn
  • Facebook
  • Windows Live / Messenger
  • Xbox Live
  • RSS
  • Email