Call stored procedures with Entity Framework 6 code first approach in Asp.Net MVC 5

Question

We are using Ado.Net for working with the database from our C# code in ASP.NET MVC. I want to work with Entity Framework, so I need to use stored procedures with it, call stored procedures from EF.

There are few examples how to use stored procedures with Entity Framework, but none specific for ASP.NET MVC and code-first (at least I couldn't find any).

They are a good start but I need something more, better information and better examples!

I have this stored procedure:

Create Procedure spAddEmployee  
    @Name nvarchar(50),  
    @Gender nvarchar(20),  
    @Salary int,  
    @EmployeeId int Out  
as  
Begin  
    Insert into tblEmployees 
    values(@Name, @Gender, @Salary)  

    Select @EmployeeId = SCOPE_IDENTITY()  
 End

So @Name, @Salary, @Gender are input parameters, and @EmployeeId is an output parameter, it returns the ID of the newly added employee.

Can someone tell me how to use Entity Framework (code-first) to call this stored procedure with the parameters?

Solution 1

You can call a stored procedure in your DbContext class as follows.

this.Database.SqlQuery<YourEntityType>("storedProcedureName",params);

Update:

This is an Example how to call your SP in ActionResult:

public ActionResult ExecuteProcedure()
{
   using(var  db = new CueEntities())
   {
     var parameter = 1;
     var query =  db.Database.SqlQuery<TestProcedure>("TestProcedure @parameter1", 
                    new  SqlParameter("@parameter1", parameter)).ToList();          
        return Json(query,JsonRequestBehavior.AllowGet);     
    }
}

Second Update:

For Multiple Params you can easily go like this:

var param1 = new SqlParameter(); 
param1.ParameterName = "@Value1"; 
param1.SqlDbType = SqlDbType.Int; 
param1.SqlValue = val1;

var param2 = new SqlParameter(); 
param2.ParameterName = "@Value2"; 
param2.SqlDbType = SqlDbType.NVarChar; 
param2.SqlValue = val2;

var result = db.tablename.SqlQuery("SP_Name @Value1,@Value2", param1, param2 ).ToList();

Solution 2

Step 1:

Create your Stored Procedure script.

IF OBJECT_ID ( 'usp_GetUserByCompany', 'P' ) IS NOT NULL   
    DROP PROCEDURE usp_GetUserByCompany; 

GO
CREATE PROCEDURE usp_GetUserByCompany
    @__ProfileId [uniqueidentifier],
    @__CompanyId [uniqueidentifier],
    @__Email VARCHAR (MAX),
AS 
    SELECT *
    FROM [UserProfiles] AS [u]
    WHERE [u].[ProfileId] = @__ProfileId
        AND [u].[CompanyId] = @__CompanyId
        AND [u].[Email] = @__Email
GO

Step 2

Create the Model for your Class

public class UserProfile {
    public Guid Id { get;set; }
    public Guid CompanyId { get; set; }
    public Company Company { get;set; }
    public string Email { get;set; }
    ...
}

Step 3

Go to your ApplicationDbContext class

public class ApplicationDbContext {

    ...
    
    public virtual DbQuery<UserProfile> UserProfiles { get; set; }
}

Step 4

Create a migration for your Stored Procedure

dotnet ef migrations add Add_SP_GetUserProfileByCompany

Step 5

Inside the generated migration class implement the Stored Procedure script on Step 1

public partial class Add_SP_GetUserProfileByCompany : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        var usp_GetUserByCompany = @"
            IF OBJECT_ID ( 'usp_GetUserByCompany', 'P' ) IS NOT NULL   
                DROP PROCEDURE usp_GetUserByCompany; 
            
            GO
            CREATE PROCEDURE usp_GetUserByCompany
                @__ProfileId [uniqueidentifier],
                @__CompanyId [uniqueidentifier],
                @__Email VARCHAR (MAX),
            AS 
                SELECT *
                FROM [UserProfiles] AS [u]
                WHERE [u].[ProfileId] = @__ProfileId
                    AND [u].[CompanyId] = @__CompanyId
                    AND [u].[Email] = @__Email
            GO
        ";

        migrationBuilder.Sql(usp_GetUserByCompany);
    }
    ...
}

Step 6

In your code somewhere on the system or services etc.,

public List<UserProfile> GetUserProfileByCompanySP(Guid ProfileId, Guid CompanyId, string Email)
{
    var dbContext = new ApplicationDbContext;

    var parameters = new object[]
    {
        new SqlParameter() {ParameterName = "@__ProfileId", Direction = ParameterDirection.Input, SqlDbType = SqlDbType.UniqueIdentifier, Value = ProfileId},
        new SqlParameter() {ParameterName = "@__CompanyId", Direction = ParameterDirection.Input, SqlDbType = SqlDbType.UniqueIdentifier, Value = CompanyId},
        new SqlParameter() {ParameterName = "@__Email", Direction = ParameterDirection.Input, SqlDbType = SqlDbType.VarChar, Size = 64, Value = Email},
    };

    var output = dbContext.UserProfiles.FromSql("usp_GetUserByCompany @__ProfileId, @__CompanyId, @__Email", parameters).ToList();
    ...
}

Solution 3

You should use CodeFirstStoredProcs library. It is best suited with Code first approach and it support all the features for stored procedure. I am using it for many projects.

Also you can use Code first user function library to call user defined functions also. I have created library for it. CodeFirstFunctions

Comments

[arianit ax]

ok. but how do I work with it in the controller, in any action method?

[arianit ax]

and do I need to make anything special in the Model class??

[arianit ax]

but what with multiple parameters? do I need to make queries for each of them or can I just write them in one query command?

[arianit ax]

ok. going back to the start, the code inside the context class, should it be inside the OnBuild method or anywhere specific?

[arianit ax]

one other thing to clarify! as I understand from your answer, I can either use the mapping in the Context class or directly without mapping in the action method?

[arianit ax]

interesting, something new. can you please make you answer with an example on how to according to my question, with the stored procedure I have, and everything related to it. thanks

[Manprit Singh Sahota]

Sure. You can visit the project site URL codeproject.com/articles/179481/code-first-stored-procedures for detail.Still if you any query then provide your code and i will let you know.

[arianit ax]

All i have for now is that stored procedure. it is shortened but it covers, if properly answered, all my needs