As we know there are various approaches for the Entity Framework available with which we can connect with the database from the ASP.NET web application. There are generally three approaches available, given below:
- Code First approach – In this approach manually Plain Old CLR object classes will be created. Relationship between those classes will be defined by means of code. When application executes for the first time Entity framework will generate Data Access Layer and Database with tables, column and relations automatically in the database server.
- Model First approach – In this approach Model classes and relationship between them will be defined manually using Model designer in Visual studio and Entity Framework will generate Data Access Layer and Database with tables, columns, relations automatically.
- Database First approach – Create database with tables, columns, relations etc. and Entity framework will generates corresponding Model classes (Business entities) and Data Access Layer code.
After considering the below points we can decide that what approach we would have select in our project
Code first:
1. Very popular because hardcore programmers don’t like any kind of designers and defining mapping in EDMX xml is too complex.
2. Full control over the code (no auto generated code which is hard to modify).
3. General expectation is that you do not bother with DB. DB is just storage with no logic. EF will handle creation and you don’t want to know how it does the job.
4. Small model changes will not lead to any data loss
5. It’s harder to maintain a database then using a visual design tool
6. Knowledge of C# is required to create databases
Model first:
1. You can use a visual designer to create a database scheme
2. You will "draw" your model and let workflow to generate your database script and template to generate your POCO (plain old CLR object) entities. You will lose part of control on both your entities and database but for small easy projects you will be very productive.
3. If you want additional features in POCO (plain old CLR object) entities you must either modify template or use partial classes.
4. When you change the model and generate SQL to sync the database then this will always lead to data loss except when you modify the script manually.
Database first:
1. Very popular if you have DB designed by DBAs, developed separately or if you have existing DB.
2. You will let EF create entities for you and after modification of mapping you will generate CLR entities.
3. If you want additional features in POCO (plain old CLR object) entities you must either modify template or use partial classes.
4. Not easy to sync database changes. Let’s say you change your database on your local machine then you need external tools to sync your changes with a remote database. This can be a major disadvantage.
In this article let me show you the implementation of Entity framework Code First Approach
Right click the project — > Manage Nuget packages. Search for Entity Framework and click on install.
Now we will be creating the data access layer. Create a folder named as “DAL” within the solution
Within the folder we will be creating a class named as “EmployeeDAL”, below is the code:
namespace EntityFrameworkDemo.DAL
{
public class EmployeeDAL : DbContext
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Employee>().HasKey(e => e.EmployeeId);
modelBuilder.Entity<Employee>().Property(e => e.EmployeeId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
modelBuilder.Entity<Department>().HasKey(d => d.DepartmentId);
modelBuilder.Entity<Department>().Property(d => d.DepartmentId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
modelBuilder.Entity<Department>().HasRequired(d => d.Employee).WithMany(e => e.Departments).HasForeignKey(d => d.EmployeeId);
base.OnModelCreating(modelBuilder);
}
public DbSet<Employee> Employees { get; set; }
public DbSet<Department> Departments { get; set; }
}
}
In web.config we will have to mention the connection string, the name attribute value will be same as DAL class name:
<connectionStrings>
<add name="EmployeeDAL" connectionString="Data Source=PC258340;Initial Catalog=EmployeeDB1;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>
Here is my Employee, Department & EmployeeBusinessLayer classes within the “Models” folder
public class Employee
{
public int EmployeeId { get; set; }
public string Name { get; set; }
public int Salary { get; set; }
public int DepartmentId { get; set; }
private DateTime? createdDate;
public DateTime CreatedDate
{
get
{
if (createdDate == null)
{
createdDate = DateTime.Now;
}
return createdDate.Value;
}
private set { createdDate = value; }
}
//[ForeignKey("DepartmentId")]
//public virtual Department DefaultDepartment { get; set; }
public ICollection<Department> Departments { get; set; }
}
public class Department
{
public int DepartmentId { get; set; }
public string DeptName { get; set; }
public int EmployeeId { get; set; }
public Employee Employee { get; set; }
}
public class EmployeeBusinessLayer
{
int num = 0;
public int EditEmployee(List<Employee> lstEM)
{
EmployeeDAL empDal = new EmployeeDAL();
foreach (var item in lstEM)
{
empDal.Entry(item).State = System.Data.Entity.EntityState.Modified;
}
num = empDal.SaveChanges();
return num;
}
public int SaveEmployee(List<Employee> lstEM)
{
EmployeeDAL empDal = new EmployeeDAL();
foreach (var item in lstEM)
{
empDal.Employees.Add(item);
}
num = empDal.SaveChanges();
return num;
}
public int SaveDepartment(List<Department> lstDM)
{
EmployeeDAL empDal = new EmployeeDAL();
foreach (var item in lstDM)
{
empDal.Departments.Add(item);
}
num = empDal.SaveChanges();
return num;
}
Here is my controller class
public class HomeController : Controller
{
public ActionResult Index()
{
return View();
}
[HttpGet]
public string GetDepartments()
{
EmployeeDAL objEmployeeDAL = new EmployeeDAL();
return JsonConvert.SerializeObject(objEmployeeDAL.Departments);
}
[HttpGet]
public string GetEmployees()
{
EmployeeDAL objEmployeeDAL = new EmployeeDAL();
return JsonConvert.SerializeObject(objEmployeeDAL.Employees);
}
[HttpGet]
public string CreateEmployees()
{
EmployeeDAL objEmployeeDAL = new EmployeeDAL();
EmployeeBusinessLayer empBL = new EmployeeBusinessLayer();
List<Employee> lstEM = new List<Employee>();
Employee objEmployeeModel = new Employee();
objEmployeeModel.Name = "Tarun1";
objEmployeeModel.DepartmentId = 1;
objEmployeeModel.Salary = 100000;
lstEM.Add(objEmployeeModel);
objEmployeeModel = new Employee();
objEmployeeModel.Name = "Tarun2";
objEmployeeModel.DepartmentId = 2;
objEmployeeModel.Salary = 200000;
lstEM.Add(objEmployeeModel);
return empBL.SaveEmployee(lstEM).ToString();
}
[HttpGet]
public string CreateDepartment()
{
EmployeeDAL objEmployeeDAL = new EmployeeDAL();
EmployeeBusinessLayer empBL = new EmployeeBusinessLayer();
List<Department> lstDM = new List<Department>();
Department objDepartmentModel = new Department();
objDepartmentModel.DeptName = "Department 1";
objDepartmentModel.EmployeeId = 1;
lstDM.Add(objDepartmentModel);
objDepartmentModel = new Department();
objDepartmentModel.DeptName = "Department 2";
objDepartmentModel.EmployeeId = 2;
lstDM.Add(objDepartmentModel);
return empBL.SaveDepartment(lstDM).ToString();
}
}
Rebuild & Run the application
Browse http://localhost:56445/Home/CreateEmployees will be creating the EmployeeDB database, Employee & department tables automatically with proper relationship. Also insert Employee data into the newly created Employee table.
Browse http://localhost:56445/Home/CreateDepartmentto insert data into the Department table.
Configure/Mapping Properties with the Fluent API
The OnModelCreating() method under the LibraryContext class uses the Fluent API to map and configure properties in the table. So let’s see each method used in the OnModelCreating() method one by one.
- HasKey() : The Haskey() method configures a primary key on table.
- Property() : The Property method configures attributes for each property belonging to an entity or complex type. It is used to obtain a configuration object for a given property. The options on the configuration object are specific to the type being configured; IsUnicode is available only on string properties for example.
- HasDatabaseGeneratedOption : It configures how values for the property are generated by the database.
- DatabaseGeneratedOption.Identity : DatabaseGeneratedOption is database annotation. It enumerates a database generated option. DatabaseGeneratedOption.Identity is used to create an auto-increment column in the table by unique value.
- The foreign key relation is defined between Publisher and the Book using the following expression:
modelBuilder.Entity<Department>().HasRequired(d => d.Employee).WithMany(e => e.Departments).HasForeignKey(d => d.EmployeeId);
To have one to many relationship between the Master & association tables here are the changes I have done
public class Employee
{
public int EmployeeId { get; set; }
public string Name { get; set; }
public int Salary { get; set; }
public int DepartmentId { get; set; }
private DateTime? createdDate;
public DateTime CreatedDate
{
get
{
if (createdDate == null)
{
createdDate = DateTime.Now;
}
return createdDate.Value;
}
private set { createdDate = value; }
}
[ForeignKey("DepartmentId")]
public virtual Department DefaultDepartment { get; set; }
}
public class Department
{
public int DepartmentId { get; set; }
public string DeptName { get; set; }
}
public class EmployeeDAL : DbContext
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Employee>().HasKey(e => e.EmployeeId);
modelBuilder.Entity<Employee>().Property(e => e.EmployeeId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
modelBuilder.Entity<Department>().HasKey(d => d.DepartmentId);
modelBuilder.Entity<Department>().Property(d => d.DepartmentId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
//modelBuilder.Entity<Department>().HasRequired(d => d.Employee).WithMany(e => e.Departments).HasForeignKey(d => d.EmployeeId);
base.OnModelCreating(modelBuilder);
}
public DbSet<Employee> Employees { get; set; }
public DbSet<Department> Departments { get; set; }
}
In my next article I will be explaining you the details implementation on Entity framework Model first approach.
Happy Coding
Tarun Kumar Chatterjee
Leave a comment