[转]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 = newBlogEntities();
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