There are three workflows to build a domain model using Entity Framework.
1. Database First
2. Code First and
3. Model First
Database-First workflow
• With Database-First, we of course start with the database.
• So, we design our database and tables using a visual designer of SQL Server Management Studio or by writing SQL script.
• EF generates domain classes based on the database.
• This is the traditional approach that a lot of developers having using for years.
Code-First workflow
• With code first workflow of course we start with the code.
• we create our domain classes.
• EF generates database tables.
• This approach has been introduced in recent years and is still new for many developers.
Model-First workflow
• With Model- First we use Visual Designer in visual Studio to model our classes and their associations, it looks like a UML diagram of some kind.
• Based on this diagram EF generates domain classes and database.
• Frankly, I have hardly seen anybody use Model-First to develop their application, the reason might be the Visual designer which is very poor for modeling classes. I personally feel you should skip learning this workflow and being genuine I am not going to cover this anywhere in the course.
So, our two main choices are database first and code first. Let's discuss each of these workflows and try to find out their strength and weaknesses.
Database-First workflow in action
Here we design our database first and EF generates domain classes based on the database, so let's get started and learn the necessary steps.
Step 1. Create a database and some table.
Open SSMS and using visual designer create a database, name it as Organization and create two tables called Employee and Department. If you are not a fan of a visual designer and prefer SQL script to generate Databases and tables, then copy and paste the below code as a new query and run the script.
Create database Organization
go
use Organization
go
CREATE TABLE [dbo].[Department] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR (50) NULL,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[Employee] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR (50) NULL,
[Salary] INT NULL,
[DepartmentId] INT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId]) REFERENCES [dbo].[Department] ([Id])
);
Step 2.Go to visual studio and create a new project , Name it as DatabaseFirstDemo
The primary focus here is to learn database-first work flow , so for now , i am going to stick to a console app because we don't want to get distracted by the complications of web or desktop app , so to keep things simple and to focus on EF , create a console application.
• Open visual studio.
• Go to Files ---->New---->Project
• Under New Project window select console application.
• Give the project a name (DatabaseFirstDemo)
• Click Ok
Step 3.Install Entity Framework.
With the new project the first step is to Install EF. To do that , we use package manager console.
• Go to Tools.
• Select Nuget Package Manger and Then Package Manager Console.
• Install Entity Framework using the install-package command.
Install-Package EntityFramework -Version 6.2.0
Step 4. Add Ado.Net Entity Data Model.
• Right Click the project.
• Add ----> Add New Item.
• Under Add new Item window select Data tab and under that select Ado.Net Entity Data Model and click Add.
This is going to be our conceptual Model that represents the mapping between our database tables and domain classes. I have given it a meaningful name as OrganizationModel.
• Under the Entity Data Model wizard, select EF Designer From database , cause we already have a database and click next.
• In the next window select or specify the connection to your database.
1. Specify Server Name
2. Choose Database.
3. Test the connection and
4. Click Ok.
• Change the connection name to OrganizationDbContext and click on Next.
• In the next window select your table and give the model namespace a meaningful name as OrganizationModel and click finish.
Notice :- At this point EF looks at our database and displays the tables, views , stored procedure and Functions , currently we only had tables so we just ticked the table and left the rest as is.
At this point , you will get a security warning like "Running this template can harm your computer" , don't worry about it and just click next. It gives us the warning because visual studio tries to run a template to generate the code.
Unfolding Edmx
EF generates domain models by running some templates, these templates are present under an edmx file, the extension of templates are ".tt". Let's uncover these templates. As you Notice in the above figure, we have two templates.
1. OrganizationModel.Context.tt and
2. OrganizationModel.tt
Here the "tt" stands for template , the first template is responsible for generating DbContext and DBSet while the second template is responsible for generating our domain Entities. This is the reason when you expand the first template that is your context.tt you will find your DbContext class, similarly on expanding Model.tt you will find all your domain Entities.
DbContext Class
At this point, if we expand our context class, we see the actual generated code. This is a plain C# code that EF generates for us. This class derives from the DbContext, so we call it our context class. Remember DbContext is an abstraction over the database. It provides a simple API to load the data from a database or save data to the database.
It has properties of type DbSet. A DbSet represents a Table in our Database. As in our database we had two tables Employee and Department, EF generated two Dbset namely DbSet of Employee and DbSet of Department.
Domain Classes
On expanding the other template that is model.tt we can see our domain Entities. Each domain Entities has properties based on our table columns.
So , the key thing here is , we had started with a database , we had just created our database and imported that into entity data model and EF took care of all the rest of the work. So, Every time we have a need to change our database we will come to our Edmx and refresh it and EF will automatically update the domain classes.
How to manage different version of application with database-First workflow
is where database-First work flow is not reliable , If you are maintaining different versions of your application , then obviously you need to maintain different versions of your database so that each version of your application could target to the appropriate version of the database , to achieve this we manually need to create a change script each time we change our database. we give the change script an appropriate name like a combination of date time stamp and description for example 12312020-AddedEmployeeAndDepartment. Now we can run this change script against any server and any database to bring it to that version.
Code First Work Flow
Step 1. Go to Visual Studio and create a new console application , Name it as CodeFirstDemo.
Step 2.Similar to Database-First workflow , as it is a new project we need to install Entity Framework First. To do that , we use package manager console.
• Go to Tools.
• Select Nuget Package Manger and Then Package Manager Console.
• Install Entity Framework using the install-package command.
Install-Package EntityFramework -Version 6.2.0
Step 3. Now, with the Code-First of Course we start with the code , so we do not need to go to Management Studio and create tables , instead here we create our domain classes all by ourselves. Add two class Files in your project with the name of Employee and Department respectively as shown in below code.
public class Department
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection Employees { get; set; }
}
public class Employee
{
public int Id { get; set; }
public string Name { get; set; }
[ForeignKey("Department")]
public int DepId { get; set; }
public Department Department { get; set; }
}
Notice the class looks exactly the same as the one EF generated for us as a part of Database-First work Flow , but here we have created it by hand. Each employee belongs to a Department so we use Department as a property in Employee Class , similarly a Department can have multiple Employees so we have used ICollection of Employee. This is needed for the association , we will discuss more about relationship and association in our later article.
Step 3. Now, In Companion to our Domain Classes we also need a DbContext Class , so that we could interact with the database later in our project.
Basically we have to create a class which derives from DbContext and exposes one or more DbSet . In our case, DbSet of Employee and Department.
So , add a new class file and Name it as OrganizationDbContext and copy -paste the below code.
public class OrganizationDbContext:DbContext
{
public DbSet Employees { get; set; }
public DbSet Departments { get; set; }
}
Step 4. Next , we need to specify the connection string , so that EF could connect to server and generate database and tables for us.
Go to app.config and add a connection String, name it as the name of your DbContext , because EF works on convention over configuration.
<add name="OrganizationDbContext" connectionString="Data Source=SACHIN-PC\SQLEXPRESS;Initial Catalog=OrganizationDB;Integrated
Security=True" providerName="System.Data.SqlClient"/>
Please Notice , here we have explicitly specified one additional property to the connection string that is provider name as Sytem.Data.SqlClient. This is required to work with code First.
Step 5. Next we need to go to package Manager console and Enable Migrations. This is the code that we run once for the life time of the project.
PM> Enable-Migrations
Notice , a migration folder has been added to our project.
Step 6. Next we need to add a migration . Go to your Package Manager Console and use Command Add-Migration and give it a meaningful name based on the changes you have made to your model class. It is our first migration so we simply name it as AddedEmployeeAndDepartment.
PM> Add-Migration AddedEmployeeAndDepartment
Notice:- Under Migration folder a new migration file has been created , the name is a combination of date time stamp and description , which is very similar to our change script that we had created to keep track of changes in our database and to manage different versions of it while working with database-first workflow. In Database-First work flow we had created that manually but here we did the same but in more automated fashion.
Step 7. Run the migration against database . To run the migration go to package manager console and run below code.
PM> update-database
when we run the migration , the EF looks at our database and figures out which migration it needs to run on the server to bring it up to date , it converts the migration into SQL code and runs into the database.
Now , let's see the results , Go back to SSMS and Notice EF has created a Database OrganizationDb based on the name of database we had specified in the connectionstring , notice along with the table it also created a MigrationHistory object , so that it could run it on server whenever it needs. The table structure looks exactly the same as we had created manually while working with database first approach.
How to manage database changes
After adding or modifying anything in database we just need to come back to our Edmx file and refresh it.
• Now, Right Click anywhere in the EDMX designer area and
• Click on update model from database
How to manage different versions of the application with Code-First work Flow
This is where code-First workflow rocks, Remember in the database-First workflow to manage different versions of the database we had created the change script manually, but with Code-First workflow we get it automatically in the form of Migrations, we can run any migration anytime to bring the database to that version.
Unfolding Migration Folder
A migration Folder contains two things
1. A configuration folder, which is used to configure the association between tables using Fluent API and To seed the database, which we will discuss in later articles.
2. The actual migration files.
Let's check what does a Migration.cs file contain.
public partial class AddedEmployeeAndDepartment : DbMigration
{
public override void Up()
{
CreateTable(
"dbo.Departments",
c => new
{
Id = c.Int(nullable: false, identity: true),
Name = c.String(),
})
.PrimaryKey(t => t.Id);
CreateTable(
"dbo.Employees",
c => new
{
Id = c.Int(nullable: false, identity: true),
Name = c.String(),
Department_Id = c.Int(),
})
.PrimaryKey(t => t.Id)
.ForeignKey("dbo.Departments", t => t.Department_Id)
.Index(t => t.Department_Id);
}
public override void Down()
{
DropForeignKey("dbo.Employees", "Department_Id", "dbo.Departments");
DropIndex("dbo.Employees", new[] { "Department_Id" });
DropTable("dbo.Employees");
DropTable("dbo.Departments");
}
}
1. The name of the migration class is what we specify while adding the migration using the package manager console.
2. Surprisingly enough it doesn't contain any SQL query rather it contains two methods with plain c# code.
3. The Up() method simply contains the changes that we do in our Context class or Domain models.
4. The Down() method contains the opposite of whatever is there in Up() method.
5. Notice EF uses convention over configuration so the column with the Name Id automatically becomes the primary key for the table not only this EF makes it an Identity column as well.
6. In simple terms when EF looks at our model classes then based on the Name and the Type of properties, it figures out how the database should look like for example it knows that Id is typically used for the primary key, and often it is an identity column so it automatically marks the column as Identity, similarly based on Association, aggregation, and composition it automatically adds the required foreign keys.
How to manage changes in domain Model or Context Class
whenever we make a change in Context Class or Domain Classes ,we just need to add a new Migration using Add-Migration command and run it against the database by using the update-database command , we obviously use package manager console to run these commands.
The Steps that we perform only once throughout the life time of the application development.
Installing Entity Framework and Enabling migrations require to perform only once in the life time of the application development.
How to use DbContext to Add data to the database
The below steps applies to both database-First approach and Code-First Approach.
Step 1. Create an Instance of DbContext Class which in our case is OrganizationDbContext.
Step 2. Create a new Employee Object or Department Object.
Step 3. Add this object to the related DbSet object ,which represents an In-Memory collection of objects.
Step 4. Commit the changes to database using dbcontext SaveChanges() method.
static void Main(string[] args)
{
using (OrganizationDbContext db = new OrganizationDbContext())
{
var emp=new Employee()
{
Name="Sachin",
Department= new Department(){Name="HR"}
};
db.SaveChanges();
}
}
Notice : in the above example we have directly added Department object to Employee , on running the application the EF will Add a new record to Employee table as well as the Department table , this is the power of EF.
The above approach is used when you have no data in the other table , as we had no department in department table so we used the above approach , now we have one department with Id=1 and Name as HR so to add other employee we can simply use DepartmentId of Employee as shown below.
static void Main(string[] args)
{
using (OrganizationDbContext db = new OrganizationDbContext())
{
var emp=new Employee()
{
Name="Michael",
DepId=1
};
db.Employees.Add(emp);
db.SaveChanges();
}
}