CODEWITHSUNDEEP
asp.netasp.net-mvcentity-frameworkentity-framework-6
Melodie Gauthier
Melodie Gauthier

Reputation: 715

Use stored procedures in MVC 5 & Entity Framework 6 code-first

I am working on my first ASP.NET MVC 5 application and I'm not sure how to call my stored procedures. When I started the project, I was using ADO.NET Entity Data Model, EF Designer, when creating the model I chose the tables and the stored procedures I wanted and it worked fine. For example, if I wanted to use a stored procedure called 'usp_RhAffSelect' I would simply do:

using (EmployeModele db = new EmployeModele())
        {var AffectationEmploye = db.usp_RhAffSelect(employeID, sequence).ToList();
...

and the result from the select query would be stored in 'AffectationEmploye'

But then I had to change my model to code first instead of EF Designer. I selected the tables I wanted (which are RhAff,RhEmp and RhEve). So I have my MainModel.cs (which is my DbContext) that describes all my tables fields inside the OnModelCreating method. I also have 3 partial classes (RhAff.cs, RhEmp.cs and RhEve.cs) that also describes their respective fields.

Now I don't know how to use my stored procedures. It seems that I can use the MapToStoredProcedures but from my understanding I can only redefine insert, update and delete queries.

How can I call a select stored procedure for my models? Or any stored procedure in particular? When I was using EF Designer, I would simply right click on a table in my edmx file and would select 'Update from database', I would choose which stored procedure I wanted and was able to use them after.

Upvotes: 2

Views: 1610

Answers (1)

Sampath
Sampath

Reputation: 65870

You can do that as shown below.

Note: This is just an example.

SP

CREATE procedure [dbo].[CountOfOrders]
    @ProductId int
AS
SELECT Count(*) From Orders o 
INNER JOIN OrderDetails od ON od.OrderID = o.OrderID 
WHERE od.ProductID = @ProductId

How to call it :

SqlParameter param1 = new SqlParameter("@ProductID", 72); 
var totalOrders = await context.Database.SqlQuery<int>("CountOfOrders @ProductID", param1).SingleAsync();

Please read Entity Framework Code First and Stored Procedures article.

Upvotes: 0

Related Questions