One of the misconceptions about code-first workflow is, it is only useful when working with a greenfield project and not when you have an existing database, that is not correct at all, because you can successfully use code-first workflow even with an existing database in your project.
To understand the code-first workflow with an existing database follow the diagram shown below.
As you can see in the above diagram, by reverse engineering we can generate Models using an existing database and once our model is generated, we can use the code-first workflow for any subsequent changes, that is instead of changing database, we change our model and run the migration to bring the database up to date. You may ask what is the benefit of it with an existing database and why should we not opt for database-first workflow in the first place. Then ,these are the two obvious reasons
1. First, with the code-first workflow we get full versioning of the database in the form of migrations which helps in maintaining different versions of the application easily.
2. Second, being a developer we love coding rather than database designing and handling database changes with management studio , but code-first gives full control of database to a developer's hand, you can alter or modify your database schema with the code , not only that you can create views , triggers , stored procedures and every other database object easily without touching the database just from your code.
Let's create a code-first model , using an existing database. Suppose , you already have a database for a school management system, and one of your client wants a complete school management system software from you , now creating a database from scratch will be a waste of time , but at the same time you also know that you may need to modify the database as soon as you will start working on the project. Also as C# developer , handling database with SQL Server Management Studio is not that appealing to you , so you decided to go with the code-first work flow.
Step 1. Go to Visual Studio and create a new Web application project and select an empty MVC template, Name it as School Management System.
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 go to Package Manager Console for installing Entity Framework.
• Install Entity Framework using the install-package command.
Install-Package EntityFramework -Version 6.2.0
Step 3. We will generate our Entities into the model folder . So , follow below steps
• Right Click on the Models folder.
• Add ----> Add New Item.
• Under Add new Item window select Data tab and under that select Ado.Net Entity Data Model and click Add.
I have given it a meaningful name as SchoolManagementModel.
• Under the Entity Data Model wizard, select Code First From database option, cause we already have a database and click next and this is the key step that you need to remember while working with an existing database.
• 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.
• In the next window select your table and click finish ,At this point EF looks at our database and displays the tables so select all of them and click finish.
Now Entity framework will generate all Classes (Entities) and DbContext in the Models folder as you can see in the below figure.
Please notice that there is no EDMX file generated by EF. Instead entity Framework simply did the reverse Engineering to generate the models. Also notice the classes EF has generated are very much similar to the classes we write by our own while working with code-first workflow for an empty database.
At this moment our code-first model is ready and we are good to go with normal code-first workflow.
Step 9. Next, we need to go to package Manager console and Enable Migration. 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 10. Next we need to add a migration . so, 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. As, It is our first migration so we simply name it InitialMigration.
PM> Add-Migration InitialMigration
Notice Under the Migration folder a new migration file has been created, whose name is a combination of the date-time stamp and description, which is very similar to the change script that we would have 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 workflow we need to create this manually using SSMS, but here we got it without any work.
public partial class InitialMigration : DbMigration
{
public override void Up()
{
CreateTable(
"dbo.Attendance",
c => new
{
AttendanceID = c.Int(nullable: false, identity: true),
Class = c.String(maxLength: 20, fixedLength: true),
Session = c.String(maxLength: 10, fixedLength: true),
Section = c.String(maxLength: 10, fixedLength: true),
SubjectCode = c.String(maxLength: 20, fixedLength: true),
StaffID = c.String(maxLength: 15, fixedLength: true),
AttendanceDate = c.DateTime(),
AdmissionNo = c.String(maxLength: 15, fixedLength: true),
Status = c.String(maxLength: 10, fixedLength: true),
})
.PrimaryKey(t => t.AttendanceID)
.ForeignKey("dbo.Student", t => t.AdmissionNo)
.ForeignKey("dbo.Subject", t => t.SubjectCode, cascadeDelete: true)
.Index(t => t.SubjectCode)
.Index(t => t.AdmissionNo);
CreateTable(
"dbo.Student",
c => new
{
AdmissionNo = c.String(nullable: false, maxLength: 15, fixedLength: true),
EnrollmentNo = c.String(maxLength: 15, fixedLength: true),
StudentName = c.String(maxLength: 100, fixedLength: true),
FatherName = c.String(maxLength: 100, fixedLength: true),
MotherName = c.String(maxLength: 100, fixedLength: true),
FatherCN = c.String(maxLength: 15, fixedLength: true),
PermanentAddress = c.String(maxLength: 250, unicode: false),
TemporaryAddress = c.String(maxLength: 250, unicode: false),
ContactNo = c.String(maxLength: 15, fixedLength: true),
EmailID = c.String(maxLength: 250, unicode: false),
DOB = c.DateTime(),
Gender = c.String(nullable: false, maxLength: 10, fixedLength: true),
AdmissionDate = c.DateTime(nullable: false),
Session = c.String(nullable: false, maxLength: 15, fixedLength: true),
Caste = c.String(nullable: false, maxLength: 15, fixedLength: true),
Religion = c.String(nullable: false, maxLength: 15, fixedLength: true),
Photo = c.Binary(nullable: false, storeType: "image"),
Status = c.String(nullable: false, maxLength: 15, fixedLength: true),
Nationality = c.String(maxLength: 100, fixedLength: true),
Class = c.String(maxLength: 20, fixedLength: true),
Section = c.String(maxLength: 30, fixedLength: true),
SchoolID = c.Int(),
})
.PrimaryKey(t => t.AdmissionNo)
.ForeignKey("dbo.School", t => t.SchoolID)
.Index(t => t.SchoolID);
CreateTable(
"dbo.HostelFeePayment",
c => new
{
HFeePaymentID = c.String(nullable: false, maxLength: 15, fixedLength: true),
AdmissionNo = c.String(maxLength: 15, fixedLength: true),
TotalPaid = c.Int(),
ModeOfPayment = c.String(maxLength: 100, fixedLength: true),
PaymentModeDetails = c.String(maxLength: 200, unicode: false),
Fine = c.Int(),
DueFees = c.Int(),
Dateofpayment = c.String(maxLength: 30, fixedLength: true),
HostelFees = c.Int(),
})
.PrimaryKey(t => t.HFeePaymentID)
.ForeignKey("dbo.Student", t => t.AdmissionNo)
.Index(t => t.AdmissionNo);
CreateTable(
"dbo.School",
c => new
{
ID = c.Int(nullable: false, identity: true),
SchoolName = c.String(maxLength: 250, unicode: false),
Address = c.String(maxLength: 250, unicode: false),
ContactNo = c.String(maxLength: 15, fixedLength: true),
Email = c.String(maxLength: 30, fixedLength: true),
Fax = c.String(maxLength: 50, fixedLength: true),
Website = c.String(maxLength: 60, fixedLength: true),
})
.PrimaryKey(t => t.ID);
CreateTable(
"dbo.Subject",
c => new
{
SubjectCode = c.String(nullable: false, maxLength: 20, fixedLength: true),
SubjectName = c.String(maxLength: 250, unicode: false),
ClassName = c.String(maxLength: 20, fixedLength: true),
})
.PrimaryKey(t => t.SubjectCode);
CreateTable(
"dbo.BookIssueStaff",
c => new
{
TransactionID = c.String(nullable: false, maxLength: 15, fixedLength: true),
IssueDate = c.DateTime(),
DueDate = c.DateTime(),
AccessionNo = c.String(maxLength: 30, fixedLength: true),
StaffID = c.String(maxLength: 15, fixedLength: true),
Status = c.String(maxLength: 30, fixedLength: true),
})
.PrimaryKey(t => t.TransactionID)
.ForeignKey("dbo.Book", t => t.AccessionNo)
.ForeignKey("dbo.Employee", t => t.StaffID)
.Index(t => t.AccessionNo)
.Index(t => t.StaffID);
CreateTable(
"dbo.Book",
c => new
{
AccessionNo = c.String(nullable: false, maxLength: 30, fixedLength: true),
BookTitle = c.String(maxLength: 30, fixedLength: true),
Author = c.String(maxLength: 30, fixedLength: true),
JointAuthors = c.String(maxLength: 30, fixedLength: true),
Subject = c.String(maxLength: 30, fixedLength: true),
Department = c.String(maxLength: 30, fixedLength: true),
Barcode = c.String(maxLength: 70, fixedLength: true),
ISBN = c.String(maxLength: 30, fixedLength: true),
Volume = c.String(maxLength: 30, fixedLength: true),
Edition = c.String(maxLength: 30, fixedLength: true),
Publisher = c.String(maxLength: 30, fixedLength: true),
PlaceOfPublisher = c.String(maxLength: 30, fixedLength: true),
CD = c.String(maxLength: 10, fixedLength: true),
PublishingYear = c.Int(),
Reference = c.String(maxLength: 10, fixedLength: true),
AlmiraPosition = c.String(maxLength: 30, fixedLength: true),
Price = c.Int(),
SupplierName = c.String(maxLength: 30, fixedLength: true),
BillDate = c.DateTime(),
Remarks = c.String(unicode: false),
})
.PrimaryKey(t => t.AccessionNo);
CreateTable(
"dbo.Employee",
c => new
{
StaffID = c.String(nullable: false, maxLength: 15, fixedLength: true),
StaffName = c.String(maxLength: 30, fixedLength: true),
FatherName = c.String(maxLength: 30, fixedLength: true),
MotherName = c.String(maxLength: 30, fixedLength: true),
DOB = c.String(maxLength: 20, fixedLength: true),
PermanentAddress = c.String(maxLength: 100, unicode: false),
TemporaryAddress = c.String(maxLength: 100, unicode: false),
PhoneNo = c.String(maxLength: 10, fixedLength: true),
MobileNo = c.String(maxLength: 10, fixedLength: true),
DateOfJoining = c.String(maxLength: 30, fixedLength: true),
Qualification = c.String(maxLength: 70, fixedLength: true),
YearOfExperience = c.String(maxLength: 50, fixedLength: true),
Designation = c.String(maxLength: 100, unicode: false),
Email = c.String(maxLength: 30, fixedLength: true),
Picture = c.Binary(storeType: "image"),
Status = c.String(maxLength: 15, fixedLength: true),
BasicSalary = c.Int(),
Department = c.String(maxLength: 100, unicode: false),
Gender = c.String(maxLength: 10, fixedLength: true),
})
.PrimaryKey(t => t.StaffID);
CreateTable(
"dbo.BookReturnStudent",
c => new
{
ReturnID = c.String(nullable: false, maxLength: 15, fixedLength: true),
TransactionID = c.String(maxLength: 15, fixedLength: true),
ReturnDate = c.DateTime(),
Fine = c.Int(),
})
.PrimaryKey(t => t.ReturnID);
CreateTable(
"dbo.Class",
c => new
{
ID = c.Int(nullable: false, identity: true),
ClassName = c.String(maxLength: 20, fixedLength: true),
Section = c.String(maxLength: 20, fixedLength: true),
})
.PrimaryKey(t => t.ID);
CreateTable(
"dbo.CourseFee",
c => new
{
Id = c.Int(nullable: false, identity: true),
ClassID = c.Int(),
FeeID = c.Int(),
Fee = c.Double(),
Month = c.String(maxLength: 30, fixedLength: true),
})
.PrimaryKey(t => t.Id)
.ForeignKey("dbo.Class", t => t.ClassID)
.ForeignKey("dbo.Fee", t => t.FeeID)
.Index(t => t.ClassID)
.Index(t => t.FeeID);
CreateTable(
"dbo.Fee",
c => new
{
Id = c.Int(nullable: false, identity: true),
Feename = c.String(maxLength: 250, unicode: false),
})
.PrimaryKey(t => t.Id);
CreateTable(
"dbo.Department",
c => new
{
id = c.Int(nullable: false, identity: true),
DepartmentName = c.String(nullable: false, maxLength: 250, unicode: false),
})
.PrimaryKey(t => t.id);
CreateTable(
"dbo.Event",
c => new
{
EventID = c.Int(nullable: false, identity: true),
EventName = c.String(maxLength: 150, fixedLength: true),
StartingDate = c.String(maxLength: 30, fixedLength: true),
StartingTime = c.String(maxLength: 20, fixedLength: true),
EndingDate = c.String(maxLength: 30, fixedLength: true),
EndingTime = c.String(maxLength: 20, fixedLength: true),
ManagedBy = c.String(maxLength: 250, fixedLength: true),
Activities = c.String(maxLength: 250, fixedLength: true),
})
.PrimaryKey(t => t.EventID);
CreateTable(
"dbo.Exams",
c => new
{
ID = c.Int(nullable: false, identity: true),
ExamName = c.String(maxLength: 30, fixedLength: true),
ExamType = c.String(maxLength: 30, fixedLength: true),
})
.PrimaryKey(t => t.ID);
CreateTable(
"dbo.Grades",
c => new
{
GradeID = c.Int(nullable: false, identity: true),
Grade = c.String(maxLength: 50),
ScoreFrom = c.Decimal(precision: 18, scale: 2),
ScoreTo = c.Decimal(precision: 18, scale: 2),
Remark = c.String(maxLength: 250),
GradePoint = c.Decimal(precision: 18, scale: 2),
})
.PrimaryKey(t => t.GradeID);
CreateTable(
"dbo.HostelInfo",
c => new
{
ID = c.Int(nullable: false, identity: true),
Hostelname = c.String(nullable: false, maxLength: 150, fixedLength: true),
Hostel_Address = c.String(nullable: false, maxLength: 250, unicode: false),
Hostel_Phone = c.String(nullable: false, maxLength: 15, fixedLength: true),
ManagedBy = c.String(maxLength: 100, fixedLength: true),
Hostel_ContactNo = c.String(maxLength: 15, fixedLength: true),
HostelFees = c.Int(),
})
.PrimaryKey(t => t.ID);
CreateTable(
"dbo.MarksEntry",
c => new
{
M_ID = c.Int(nullable: false),
AdmissionNo = c.String(maxLength: 50, fixedLength: true),
StudentSchool = c.String(maxLength: 250),
Session = c.String(maxLength: 100),
StudentClass = c.String(maxLength: 150),
StudentSection = c.String(maxLength: 100),
EntryDate = c.DateTime(),
Result = c.String(maxLength: 20, fixedLength: true),
})
.PrimaryKey(t => t.M_ID);
CreateTable(
"dbo.Scholarship",
c => new
{
ScholarshipID = c.Int(nullable: false, identity: true),
ScholarshipName = c.String(maxLength: 30, fixedLength: true),
Description = c.String(maxLength: 200, unicode: false),
Amount = c.String(maxLength: 10, fixedLength: true),
})
.PrimaryKey(t => t.ScholarshipID);
CreateTable(
"dbo.Supplier",
c => new
{
ID = c.String(nullable: false, maxLength: 15, fixedLength: true),
SupplierName = c.String(maxLength: 30, unicode: false),
Address = c.String(maxLength: 200, fixedLength: true),
ContactNo = c.String(maxLength: 15, fixedLength: true),
EmailID = c.String(maxLength: 50, fixedLength: true),
})
.PrimaryKey(t => t.ID);
CreateTable(
"dbo.sysdiagrams",
c => new
{
diagram_id = c.Int(nullable: false, identity: true),
name = c.String(nullable: false, maxLength: 128),
principal_id = c.Int(nullable: false),
version = c.Int(),
definition = c.Binary(),
})
.PrimaryKey(t => t.diagram_id);
CreateTable(
"dbo.Transportation",
c => new
{
RouteID = c.Int(nullable: false, identity: true),
SourceLocation = c.String(maxLength: 250, unicode: false),
BusCharges = c.Int(),
})
.PrimaryKey(t => t.RouteID);
CreateTable(
"dbo.User_Registration",
c => new
{
ID = c.Int(nullable: false),
UserID = c.String(nullable: false, maxLength: 150, fixedLength: true),
Password = c.String(nullable: false, maxLength: 30, fixedLength: true),
Name = c.String(nullable: false, maxLength: 30, fixedLength: true),
Contact_No = c.String(nullable: false, maxLength: 20, fixedLength: true),
Email = c.String(maxLength: 30, fixedLength: true),
Date_of_joining = c.String(nullable: false, maxLength: 30, fixedLength: true),
usertype = c.String(nullable: false, maxLength: 30, fixedLength: true),
UnderUser = c.String(maxLength: 30, fixedLength: true),
})
.PrimaryKey(t => t.ID);
}
public override void Down()
{
DropForeignKey("dbo.CourseFee", "FeeID", "dbo.Fee");
DropForeignKey("dbo.CourseFee", "ClassID", "dbo.Class");
DropForeignKey("dbo.BookIssueStaff", "StaffID", "dbo.Employee");
DropForeignKey("dbo.BookIssueStaff", "AccessionNo", "dbo.Book");
DropForeignKey("dbo.Attendance", "SubjectCode", "dbo.Subject");
DropForeignKey("dbo.Student", "SchoolID", "dbo.School");
DropForeignKey("dbo.HostelFeePayment", "AdmissionNo", "dbo.Student");
DropForeignKey("dbo.Attendance", "AdmissionNo", "dbo.Student");
DropIndex("dbo.CourseFee", new[] { "FeeID" });
DropIndex("dbo.CourseFee", new[] { "ClassID" });
DropIndex("dbo.BookIssueStaff", new[] { "StaffID" });
DropIndex("dbo.BookIssueStaff", new[] { "AccessionNo" });
DropIndex("dbo.HostelFeePayment", new[] { "AdmissionNo" });
DropIndex("dbo.Student", new[] { "SchoolID" });
DropIndex("dbo.Attendance", new[] { "AdmissionNo" });
DropIndex("dbo.Attendance", new[] { "SubjectCode" });
DropTable("dbo.User_Registration");
DropTable("dbo.Transportation");
DropTable("dbo.sysdiagrams");
DropTable("dbo.Supplier");
DropTable("dbo.Scholarship");
DropTable("dbo.MarksEntry");
DropTable("dbo.HostelInfo");
DropTable("dbo.Grades");
DropTable("dbo.Exams");
DropTable("dbo.Event");
DropTable("dbo.Department");
DropTable("dbo.Fee");
DropTable("dbo.CourseFee");
DropTable("dbo.Class");
DropTable("dbo.BookReturnStudent");
DropTable("dbo.Employee");
DropTable("dbo.Book");
DropTable("dbo.BookIssueStaff");
DropTable("dbo.Subject");
DropTable("dbo.School");
DropTable("dbo.HostelFeePayment");
DropTable("dbo.Student");
DropTable("dbo.Attendance");
}
}
Unfold the migration , and observe it has everything about our model which is sufficient to run against an empty database in order to create all tables , but we already have a database , at this point if we run this migration , EF will throw an error cause database already contains all these changes and mapping would not be possible , to avoid the Error we will have to tell the EF to ignore the changes for now, and gives us an empty migration so that the database and our model will be in sync with each other when we will run the migration
To create an empty migration run below command
PM> Add-Migaration InitialMigration -IgnoreChange -Force
Notice here we have used two switches
1. IgnoreChange :- It tells EF to ignore all the changes that is in our current Model , for Entity Framework , it is as if everything is new here and he has to add these changes into the migration file but the switch IgnoreChange forces him to not consider these as a change .
2. Force :- as we have already given our previous migration the name of InitialMigration so to override it we use Force switch.
Now , EF will generate an empty migration as shown in below code.
public partial class InitialMigration : DbMigration
{
public override void Up()
{
}
public override void Down()
{
}
}
Step 9.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 against the server. Currently the migration is empty, so on running it, no change will take place to the database but our database and our models will come in Sync with each other
From here , for any subsequent changes we just need to add a new migration and run that against the database .