Post
Ask Question

Entity Framework: Code First Migrations With the Existing Database

Introduction

 
Code First allows us to represent our model using code language (C#, VB.Net, F#, and so on) classes. Also, additional configurations can be applied using the Fluent API or by applying attributes to our classes. We can migrate our existing database with the Code First model. This article explains how to do Code First migrations with the existing database.
 
Pre-Requisites
  • Visual Studio 2012 or Visual Studio 2013
  • Entity Framework version 6.1 or later
  • Entity Framework Tools for Visual Studio
  • Basic knowledge of Code First migration
Reverse Engineer Code First
 
The Reverse Engineer Code First command (Entity Framework tool) is used to generate the POCO classes, mapping (configuration), and DbContext class based on an existing database. The Entity Framework tool can help us to generate the POCO class from an existing database. This tool is just generating code that we would also write manually by ourselves.
 
Example
 
 Suppose I have an existing database with the employee, employee address, employee detail, and addresses tables. Now I want to create an application using the Code First model for the database.
 
system image
 
The following is the procedure to create a Code First model on the existing database.
 
Step 1: Create a Model using Entity Data Model Wizard.
 
empty data model
 
Step 2: Select the “Code First from Database” option and click "Next".
 
code first from database
 
Step 3: Set up a connection with the database and click on "Next".
 
data connection
 
Step 4: Select the item to be added to the model and click on the "Finish" button.
 
select table
 
The DbContext class and domain classes have been added to the project. The context class exposes DbSet<TEntity> for each type of imported model and it has also the default constructor that calls a base class constructor with "name=connection string name" syntax. The "name=" syntax contains a connection string name that is stored in the config file's ConnectionString node. If the connection string is not present in the config file, Entity Framework throws an error.
 
dbcontext class
 
DbContext definition (Model.cs)
  1. namespace CodeFirstonExsitingDB.Models  
  2. {  
  3.     using System;  
  4.     using System.Data.Entity;  
  5.     using System.ComponentModel.DataAnnotations.Schema;  
  6.     using System.Linq;  
  7.   
  8.     public partial class Model : DbContext  
  9.     {  
  10.         public Model()  
  11.             : base("name=EntityModel")  
  12.         {  
  13.         }  
  14.   
  15.         public virtual DbSet<Address> Addresses { get; set; }  
  16.         public virtual DbSet<Employee> Employees { get; set; }  
  17.         public virtual DbSet<EmployeeDetail> EmployeeDetails { get; set; }  
  18.   
  19.         protected override void OnModelCreating(DbModelBuilder modelBuilder)  
  20.         {  
  21.             modelBuilder.Entity<Address>()  
  22.                 .Property(e => e.AddressLine1).IsUnicode(false);  
  23.   
  24.             modelBuilder.Entity<Address>()  
  25.                 .Property(e => e.AddressLine2).IsUnicode(false);  
  26.   
  27.             modelBuilder.Entity<Address>()  
  28.                 .Property(e => e.AddressLine3).IsUnicode(false);  
  29.   
  30.             modelBuilder.Entity<Address>()  
  31.                 .Property(e => e.City).IsUnicode(false);  
  32.   
  33.             modelBuilder.Entity<Address>()  
  34.                 .Property(e => e.State).IsUnicode(false);  
  35.   
  36.             modelBuilder.Entity<Address>()  
  37.                 .Property(e => e.Pincode).IsUnicode(false);  
  38.   
  39.             modelBuilder.Entity<Employee>()  
  40.                 .Property(e => e.Code).IsUnicode(false);  
  41.   
  42.             modelBuilder.Entity<Employee>()  
  43.                 .Property(e => e.Name).IsUnicode(false);  
  44.   
  45.             modelBuilder.Entity<Employee>()  
  46.                 .HasOptional(e => e.EmployeeDetail).WithRequired(e => e.Employee);  
  47.   
  48.             modelBuilder.Entity<Employee>()  
  49.                 .HasMany(e => e.Addresses).WithMany(e => e.Employees).Map(m => m.ToTable("EmployeeAddress").MapLeftKey("EmployeeId").MapRightKey("AddressId"));  
  50.   
  51.             modelBuilder.Entity<EmployeeDetail>()  
  52.                 .Property(e => e.Email).IsUnicode(false);  
  53.   
  54.             modelBuilder.Entity<EmployeeDetail>()  
  55.                 .Property(e => e.PhoneNo).IsUnicode(false);  
  56.         }  
  57.     }  
  58. }  
Domain (POCO) classes
  1. public partial class Address  
  2.  {  
  3.         public Address()  
  4.         {  
  5.             Employees = new HashSet<Employee>();  
  6.         }  
  7.         public int Id { get; set; }  
  8.         [StringLength(50)]  
  9.         public string AddressLine1 { get; set; }  
  10.         [StringLength(50)]  
  11.         public string AddressLine2 { get; set; }  
  12.         [StringLength(50)]  
  13.         public string AddressLine3 { get; set; }  
  14.         [StringLength(20)]  
  15.         public string City { get; set; }  
  16.         [StringLength(20)]  
  17.         public string State { get; set; }  
  18.         [StringLength(10)]  
  19.         public string Pincode { get; set; }  
  20.         public virtual ICollection<Employee> Employees { get; set; }  
  21. }  
  22.   
  23. [Table("Employee")]  
  24. public partial class Employee  
  25. {  
  26.     public Employee()  
  27.     {  
  28.         Addresses = new HashSet<Address>();  
  29.     }  
  30.     public int Id { get; set; }  
  31.     [StringLength(20)]  
  32.     public string Code { get; set; }  
  33.     [StringLength(50)]  
  34.     public string Name { get; set; }  
  35.     public virtual EmployeeDetail EmployeeDetail { get; set; }  
  36.     public virtual EmployeeExtendedDetails EmployeeExtendedDetail { get; set; }  
  37.     public virtual ICollection<Address> Addresses { get; set; }  
  38. }  
  39.   
  40. [Table("EmployeeDetail")]  
  41. public partial class EmployeeDetail  
  42. {  
  43.     [DatabaseGenerated(DatabaseGeneratedOption.None)]  
  44.     public int Id { get; set; }  
  45.   
  46.     [StringLength(255)]  
  47.     public string Email { get; set; }  
  48.     [StringLength(50)]  
  49.     public string PhoneNo { get; set; }  
  50.     public virtual Employee Employee { get; set; }  
  51. }  
The Domain classes (POCO classes) contain an annotation that aligns our classes with the existing database. In the preceding example, the address table has the AddressLine1 column with a maximum length of 50 in the database, so this property has been marked with the StringLength (50) attribute.
 
Test Code
 
Now using the Dbcontext class, we can query on the model. In the following example, I retrieved all the employees from the database.
  1. List<Employee> emp = null;  
  2. using (Model context = new Model())  
  3. {  
  4.    emp = context.Employees.ToList();  
  5. }  
Test Code output
 
Code output
 
 
What if our Database Changes?
 
The Code First to Database wizard using the Entity Framework tool is designed to generate a set of classes at a starting point that we can then modify. If our database schema changes then we can either edit it manually or perform another reverse engineer and overwrite all the classes.
 
Example
 
Suppose I want to add a new table in the database and also update my model depending on the database, so we have two options; either delete the existing model and recreate it using the Entity Framework tool or update our dbcontext manually.
 
In this example, I will explain the procedure to update our model manually.
 
The following is a database script to add a new table and insert some dummy data.
  1. CREATE TABLE[dbo]. [EmployeeExtendedDetails](  
  2.     [EmployeeID][int] NOT NULL,  
  3.     [DisplayName][nvarchar](100) NULL,  
  4.     [Notes][nvarchar](max) NULL,  
  5.     [PassportNumber][nvarchar](20) NULL,  
  6.     [PassportExpiryDate][date] NULL,  
  7.     CONSTRAINT[PK_EmployeeExtendedDetails] PRIMARY KEY CLUSTERED(  
  8.         [EmployeeID] ASC  
  9.     ) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]  
  10. ) ON[PRIMARY] TEXTIMAGE_ON[PRIMARY]  
  11.   
  12. GO  
  13.   
  14. SET ANSI_PADDING OFF  
  15. GO  
  16.   
  17. ALTER TABLE[dbo]. [EmployeeExtendedDetails] WITH CHECK ADD CONSTRAINT[FK_EmployeeExtendedDetails_Employee] FOREIGN KEY([EmployeeID])  
  18. REFERENCES[dbo]. [Employee]([ID])  
  19. ON DELETE CASCADE  
  20.   
  21. INSERT INTO EmployeeExtendedDetails(EmployeeID, DisplayName, Notes, PassportNumber, PassportExpiryDate)  
  22. Values(1, 'Jignesh Trivedi', 'Test', '1234R5T33', '2020-12-31')  
  23.   
  24. INSERT INTO EmployeeExtendedDetails(EmployeeID, DisplayName, Notes, PassportNumber, PassportExpiryDate)  
  25. Values(1, 'Jignesh Trivedi', 'Test', '1234R5T33', '2020-12-31') 
Step 1: Adds POCO class or modifies the existing POCO class.
  1. using System;  
  2. using System.ComponentModel.DataAnnotations;  
  3. using System.ComponentModel.DataAnnotations.Schema;  
  4.   
  5. namespace CodeFirstonExsitingDB.Models  
  6. {  
  7.     [Table("EmployeeExtendedDetails")]  
  8.     public class EmployeeExtendedDetails  
  9.     {  
  10.         [DatabaseGenerated(DatabaseGeneratedOption.None)]  
  11.         [Key]  
  12.         public int EmployeeId { get; set; }  
  13.         [StringLength(50)]  
  14.         public string DisplayName { get; set; }  
  15.         public string Notes { get; set; }  
  16.         [StringLength(20)]  
  17.         public string PassportNumber { get; set; }  
  18.         public DateTime PassportExpiryDate { get; set; }  
  19.         public virtual Employee Employee { get; set; }  
  20.     }  
  21. }  
Step 2: Create a DbSet property for the newly created Domain class within the DbContext class.
  1. public partial class Model : DbContext  
  2. {  
  3.    ….  
  4.    ….  
  5.       public virtual DbSet<EmployeeExtendedDetails> EmployeeExtendedDetails { get; set; }  
  6. }  
Step 3: Create a navigation property in the relational class.      
  1. public partial class Employee  
  2. {  
  3.    ….  
  4.    ….  
  5.      public virtual EmployeeExtendedDetails EmployeeExtendedDetail { get; set; }  
  6. }  
Step 4: Add relations in the OnModelCreating virtual method if any.
  1. protected override void OnModelCreating(DbModelBuilder modelBuilder)  
  2. {  
  3.    ….  
  4.    ….  
  5.           modelBuilder.Entity<Employee>()  
  6.                          .HasOptional(e => e.EmployeeExtendedDetail)  
  7.                          .WithRequired(e => e.Employee);  
  8. }  
Step 5: Run the application and check the output.
 
check output
 

Summary

 
In this article, we study the Code First approach with an existing database. Here we used Entity Framework Tools to reverse-engineer a domain class that is mapped with an existing table of the database and can be used to store and retrieve the data.


Recommended Free Ebook
C# Corner Ebook

DateTime in C#

 Download Now!
Similar Articles