.NET

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 on 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 ourself.

Example

Suppose I have an existing database with 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 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 in 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 align 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. (  
  9.     [EmployeeID] ASC  
  10. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  11. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  12.   
  13. GO  
  14.   
  15. SET ANSI_PADDING OFF  
  16. GO  
  17.   
  18. ALTER TABLE [dbo].[EmployeeExtendedDetails]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeExtendedDetails_Employee] FOREIGN KEY([EmployeeID])  
  19. REFERENCES [dbo].[Employee] ([ID])  
  20. ON DELETE CASCADE  
  21.   
  22. INSERT INTO EmployeeExtendedDetails (EmployeeID,DisplayName,Notes,PassportNumber,PassportExpiryDate)  
  23. Values(1,'Jignesh Trivedi', 'Test','1234R5T33', '2020-12-31')  
INSERT INTO EmployeeExtendedDetails (EmployeeID,DisplayName,Notes,PassportNumber,PassportExpiryDate)
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.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs