Entity Framework Using Stored Procedures

Entity Framework is really designed to access your tables directly, but you can use stored procedures.  This is typical where you have a legacy system where there is lots of business logic embedded in your stored procedures and you can’t afford to do a rewrite.

Taking the Northwind database, I added stored procedures to handle the typical CRUD operations to the Product table:

Create procedure usp_GetProducts
as
Select * from Products
GO

Create procedure usp_UpdateProduct
@productId int,
@productName nvarchar(40)
as
Update Products
Set ProductName = @productName
Where ProductID = @productId
GO

Create procedure usp_DeleteProduct
@productId int
as
Delete Products
Where ProductID = @productId
GO

Create procedure usp_InsertProduct
@productName nvarchar(40)
as
Insert Products
Values(
@productName,null,null,null,null,
null,null,null,0)

GO

I then wired up a EF model in Visual Studio using the EF designer support pointing at the tables so I wouldn’t have to create the entities by hand:

I then added the four stored procedures that I just created – note that the .edmx designer does not show the sps graphically.

Taking the Product entity, I then selected the “Stored Procedure Mapping” context menu:

To get the following menu:

It was easy enough then to add the stored procedures that I just created to the entity:

The problem is then with the R of CRUD – there is no designer support for Selecting the Products.  You have to do this in code like so:

public NorthwindRepository()
{
    using (NorthwindEntities entities = new NorthwindEntities())
    {
        String commandText = "usp_GetProducts";
        var productQuery = entities.ExecuteStoreQuery<Product>(commandText,null);
        foreach (Product product in productQuery)
        {
            //TODO
        }
    }
}

Once you have that, you can use stored procedures in your project.  Note that many stored procedures are not simple select statements but bring in extra fields like this:

Create procedure usp_GetProducts
as
Select
P.ProductID,
P.ProductName,
P.SupplierID,
S.CompanyName
from Products as P
Inner join Suppliers as S
on P.SupplierID = S.SupplierID
GO

Is that is the case, you CAN’T just use the entity based on the table and pump in that stored procedure – the fields must match exactly – name, data types, etc.  If they don’t match, you get a exception like this: