How to call a stored procedure with Entity FrameWork

Before answering to this question it can be a good thing to point out the advantages and disadvantages of using stored procedures.

 

Advantages:

1) For some treatment it can increase the application performance.

2) It can be a way of preventing user from having direct access to the database.

3) It makes it easier to deal with complex SQL treatment.

 

Disadvantages:

1) The logic of your application is located in different places.

2) You have to know Transact Sql or PL SQL whether you use SQL Server or Oracle.

 

With Entity Framework I think the advantage 2 could be eliminated because we are working with Entities and before we can delete or create a record we have to create an object ,add it to the current context and then call a save or commit method. We are not performing any direct CRUD operation to a database table.

The advantage 3 is no more an advantage because with link to entities it is possible to do any complex operation.

 

Calling a stored procedure with Entity Framework :

 

If the stored procedure has just been created.

Right Click on the designer (Entity Data Model) and click “Update model from database”.

A database wizard popup appears and then we select the desired stored procedure. We click finish. In the model browser we right click on our stored procedure and choose “Add model import” in the contextual menu. That will create a function in our project with the correct parameter for the function. Now the function could be executed from the context.

Another way to call a store procedure is to call the context method SqlQuery.

http://www.devolution-it.com/