Entity framework is a Object Relational Mapping framework or ORM.Normally we need to implement few different steps for accessing a database.Some of the steps we need to follow while accessing database are:
- Need to write code to connect to database.
- Populate the model classes with the database data.We need to manually map and populate the properties of the model with the column values in the table.
Above are the steps we need to implement every time we write data access logic.While using ORM such as Entity framework we do not need to implement the above steps.These are taken care of by the ORM.
Two of the important functionalities that Entity Framework provides us are:
Entity framework can create classes in c# from the database schema
It can also create database tables from the model classes
Entity Framework 6 introduces a feature to map CRUD operations to stored procedures.This mean we can specify which stored procedure will get executed for a specific CRUD operation.For example we can specify that procedure called insertSP will be executed for insert operation.
We can use Entity framework in our application by following different approaches such as Code first or database first.Here we will be using code first approach for accessing the database using Entity framework.
Declare Model class
We first need to declare the model class which will use:
public class Employee
{
public int Id { get; set; }
public string Name { get; set; }
public DateTime DateofJoining { get; set; }
}
Define DbContext derived class and override OnModelCreating method
Next we need to define the DataContext.DataContext acts as the mediator between our model classes and the database tables.We expose our model classes to the database using the entity framework DbSet class.So properties of this type maps to the database tables.
Since we want to use the stored procedures instead of the Sql queries we need to override the OnModelCreating method of the DbContext class.
public class EmplyeesContext : DbContext
{
public EmplyeesContext() : base("name=EmplyeesContext")
{
}
public DbSet<Employee> Employees { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Employee>().MapToStoredProcedures(x =>
{
x.Update(u => u.HasName("update_employee"));
x.Delete(d => d.HasName("delete_employee"));
x.Insert(i => i.HasName("insert_employee"));
});
}
}
There are two overloads of the OnModelCreating() method.One doesn’t take any parameters and allows to use the default stored procedures.There is a second overload which takes Action delegates as parameters.As used above we can customize the names of procedures to use for different CRUD operations.We can also map CRUD operations to existing stored procedures using this overload.
In the OnModelCreating method above we have mentioned the stored procedures name that we want the entity framework to generate for us.For example the procedure for update operation is called update_employee.By default entity framework generates procedures with the name TypeName_Insert,TypeName_Update and TypeName_Delete.
We have provided explicit procedure names using the HasName() method above.
A context is a pipeline to the database.It connects our application to the database
DbContext is entity framework class.In the DbContext class we declare property of type DbSet which maps the model class to database table.In the above class we have defined a property of type DbSet<Employee>.
If you have noticed we have declared a property named ID of type int in the Employee class.
public int Id { get; set; }
When we declare a property named ID in the class it acts as the primary key in the database table.
Once we have overridden the above method we need to enable and run the migrations using the following commands
Add Migration and Update database
PM>Enable-Migrations
Once we execute the above command in the powershell console we should see the following message
Code First Migrations enabled for project Organization.
Then we need to add migration using the following command
Add-Migration EmployeesSP
After the above migration run we should see a class deriving from the DbMigration class and which contains a method void Up().This method contains the SQL scripts for the different CRUD operations.For example for the insert procedure it contains the following code:
CreateStoredProcedure(
"dbo.insert_employee",
p => new
{
Name = p.String(),
DateofJoining = p.DateTime(),
},
body:
@"INSERT [dbo].[Employees]([Name], [DateofJoining])
VALUES (@Name, @DateofJoining)
DECLARE @Id int
SELECT @Id = [Id]
FROM [dbo].[Employees]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()
SELECT t0.[Id]
FROM [dbo].[Employees] AS t0
WHERE @@ROWCOUNT > 0 AND t0.[Id] = @Id"
);
After we have run the above command we need to update the database with EF migrations
PM>Update-Database
Use Context class for different CRUD operations
Now we can use the EmployeeContext class to perform the different CRUD operations using the stored procedures.
For example to add a new employee to the database we can use the following code:
using (EmplyeesContext context = new EmplyeesContext())
{
Employee employee = new Employee();
employee.Name = "Mark";
employee.DateofJoining = DateTime.Now;
context.Employees.Add(employee);
context.SaveChanges();
}
Here we are creating a new employee object and setting the values for the Name and DateofJoining properties.Once these properties are set we are adding the employee object to the Employees collection of the EmplyeesContext class.Finally we are calling the SaveChanges() method to insert the employee object in the database table.
Leave a Reply