Inserting,Updating and Deleting entity using Stored Procedures

If you have used entity framework with stored procedure, you would realize it has a great designer and runtime support. Yes there are few gotchas and loose ends that needs to be fixed but it does the job 99% of the time. In this blog, I will show you how to use stored procedures to perform crud operations on a single entity.

Suppose we have a model containing Article entity as shown below.

You can by default use the dynamic insert,update and delete statements EF generates to insert the article entity into the database. If you already have existing stored procedures that are performing crud for your tables, you can map those stored procedures using the EF designer to the respective operations on each entity. This means that when EF wants to insert, update or delete an entity it will call your stored procedure to perform that operation.

The first step is creating stored procedure for inserting, updating and deleting article entity. Code below shows our stored procedure.

On the insertArticle stored procedure, we are returning Articleid identity column using a select statement. This is something you have to understand is a limitation of EF with stored procedures. Currently the identity column returned from the stored procedure must be returned through a select statement. It cannot be returned via an output parameter.

Then using the Update Model wizard import the above stored procedures into your model. Next right click Article entity and choose Stored Procedure Mapping. Map the stored procedures as shown in the figure below.

The interesting mapping is the InsertArticle stored procedure. Notice there is a section for Result Column binding. In this section you specify any values generated at the database layer to be mapped back to the object. In our case ArticleId is generated at the database layer and therefore we say map the ArticleId column returned from the stored procedure to ArticleId property on Article entity.

When I have to insert, update or delete Article entity, my code does not changes at all. Instead when I call SaveChanges, EF will call correct stored procedure depending on the state of my entity. Code below shows inserting, updating and deleting Article entity.