At times, we may need to call stored procedures when using Entity Framework Code First in Asp.Net MVC applications. There are occasions where the data access logic is very complex or we already have a Stored Procedure which we want to use it in our new application that uses EF Code First. This simple article will help us to call and execute stored procedures when using EF Code First for data access.
I assume you have created an Asp.Net MVC 5.0 project using Visual Studio 2015 or 2012 and have all the Nuget packages required for Asp.Net MVC including latest Entity Framework package.
For simple understanding, we will use a simple Employee-Department model to demonstrate using stored procedures with Entity Framework Code First in Asp.Net MVC application.
The EF Context class for the Employee-Department model below.
public class DBModel : DbContext
{
public DBModel()
: base("name=DBModel")
{
}
public DbSet<Department> Departments { get; set; }
public DbSet<Employee> Employees { get; set; }
}
Calling Stored Procedure that return Custom Entity
Assume, we need to call a stored procedure that joins multiple table and returns a result with columns that does not map to any of our entities in our project. The procedure below.
CREATE PROCEDURE [dbo].[GetEmployeesSummary]
AS
SELECT
EmployeeId,
FirstName + ' ' + LastName As Name,
DepartmentName
FROM Employees e INNER JOIN Departments d
ON e.DepartmentId = d.DepartmentId
To get the result of this stored procedure we first need to define a C# class that matches the column names and types the stored procedure returns. The class definition that matches the above stored procedure is below.
public class EmployeeSummary
{
public int EmployeeId { get; set; }
public string Name { get; set; }
public string DepartmentName { get; set; }
}
To call the stored procedure, we need to use the DBContext.Database.SqlQuery<T>(“query”) method. This method returns the result without the self-tracking feature of Entity Framework. Code below.
DBModel db = new DBModel();
IList<EmployeeSummary> empSummary = db.Database.SqlQuery<EmployeeSummary>("GetEmployeesSummary").ToList();
Calling Stored Procedures with DML Statements (INSERT, UPDATE, DELETE)
Let’s create simple update stored procedure to update Employee table data. The stored procedure below.
CREATE PROCEDURE [dbo].[UpdateEmployee]
@EmployeeId int,
@FirstName Varchar(50),
@LastName Varchar(50)
AS
UPDATE Employees
SET FirstName = @FirstName,
LastName = @LastName
WHERE EmployeeId = @EmployeeId
To call stored procedures with DML statements (or equivalent of ExecuteNonQuery()), we have DBContext.Database.ExecuteSqlCommand("query", params object) method. The below code will call the “UpdateEmployee” stored procedure by adding input parameters.
SqlParameter Id = new SqlParameter("@EmployeeId", EmployeeId);
SqlParameter fname = new SqlParameter("@FirstName", FirstName);
SqlParameter lname = new SqlParameter("@LastName", LastName);
int result = db.Database.ExecuteSqlCommand("UpdateEmployee @EmployeeId, @FirstName, @LastName", Id, fname, lname);
Note – We also have equivalent asynchronous method “ExecuteSqlCommandAsync()” for this.
Calling Stored Procedures as Part of Context Class
We can include the above operations as a method in the context class (DBModel class) and access it from our controller action methods. The below methods can be part of DBModel class to call the above 2 stored procedures.
public virtual IList<EmployeeSummary> GetEmployeeSummary()
{
IList<EmployeeSummary> empSummary = ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<EmployeeSummary>("GetEmployeesSummary").ToList<EmployeeSummary>();
return empSummary;
}
public virtual int UpdateEmployee(int EmployeeId, string FirstName, string LastName)
{
return Database.ExecuteSqlCommand("UpdateEmployee @p0, @p1, @p2", EmployeeId, FirstName, LastName);
}
Note – We can also use DBContext.Database. SqlQuery() and ExecuteSqlCommand() methods here. This is an alternate way to call stored procedures. Please note, the DBContext is a wrapper of ObjectContext class and it has methods like ExecuteStoreQuery(), ExecuteSqlCommand() with corresponding async methods for calling stored procedures. The results returned by this methods too are not tracked by EF context.
Calling Stored Procedures with Context Tracking
By default, the above methods return objects that are not tracked by the EF context class. If we want the EF context to track the entities then you should consider using DBContext.TableName.SqlQuery() method.
db.Employees.SqlQuery("GetEmployee").ToList();
Please note that with above method we can only return entities that are defined for code first and you cannot return a custom entity like above methods.