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:

 

image

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:

 

image

To get the following menu:

image

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

image

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:

image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: