SQL Server has a large fanbase of stored procedures enthusiasts. Including obviously DBA's, but also developers. The latter foremost considering that with storedprocs you can easily shovel responsibility at those who are responsible for maintaining data-integrity.

Unfortunately, the ADO.NET Entity Framework in its current version has some limitations when working with stored procedures.

Before you start working with EF in combination with storedprocs, you need to be aware of the following issues:

1. One way to map them all - or just don't map
   When mapping a single stored procedures to an insert, update or delete function you must also map the other commands as well. This means that you cannot combine using stored procedures for, let's say, only a delete operation and not use them for Insert/Updates. It's either all without, or all by stored procedures.
   
   For example:
   
2. The point of no 'RETURN'
   You can run a stored procedure through code by first importing the function in your EF model (Function Import). With a big BUT: the return value of the stored procedure must have exactly the same column definition as the Entity itself. If the return value is just slightly off from the definition (think Int32, a Boolean or a whatever mix of fields you can think of) it will simply not work. It will compile but it will blow up in your face during runtime.
   
   For example, this does not comply:

   CREATE PROCEDURE [dbo].[deleteContract] ( @ContractId int = 1 ) AS

           delete from ContractLine where ContractId = @ContractId

                -- ... even more funky amazing operations here...    

           delete from [Contract] where ContractId = @ContractId

           -- Notice that the return value is not a by EF expected 'Contract' Entity i.e. row,

           -- but rather a simple Type. This will therefor fail:

           RETURN 1; 

Now the first issue is trivial. You use mapping, or you don't. The second issue is something that should be supported in an future update of the Entity Framework but simply just is not at this time. Or is it...

How to cope with it - using EFExtensions

Obviously you can easily overcome these limitations in the current version of Entity Framework by creating and executing a regular ADO.NET SqlCommand. You can even re-use the ObjectContexts' connection, create an EntityCommand and try to execute it (ExecuteReader/ExecuteScalar). But evenly so obvious - you don't really want this, do you?

Luckily, Colin Meek and Diego Vega (both part of developer team for SQL Server Data Programmability) fully acknowledged this limitation and published an extension library on MSDN's Code Gallery. It's called the Entity Framework Extensions and is - what I can only hope - a preview of the built-in functionality within the future version of Entity Framework.

Amongst many other features, it has built-in support for handling issue #2. By using this library, running a stored procedure using these Entity Framework Extensions is now as simple as the following snippet:

DataContext.CreateStoreCommand("DeleteContract", System.Data.CommandType.StoredProcedure,

    new System.Data.SqlClient.SqlParameter("ContractId", contractId));

In the above example we execute a storedproc named DeleteContract with parameter @ContractId = 1. All you need to do as a prerequisite is import the stored procedure as a Function Import in your EF model first.

Excellent work, guys!

Resources

• MSDN Code Gallery - ADO.NET Entity Framework Extensions
  http://code.msdn.microsoft.com/EFExtensions