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 address tables. Now I want to create an application using the Code First model for the database.

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.

Step 2. Select the “Code First from Database” option and click "Next".

Step 3. Set up a connection with the database and click on "Next".

Step 4. Select the item to be added to the model and click on the "Finish" button.

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 definition (Model. cs)

namespace CodeFirstonExsitingDB.Models
{
    using System;
    using System.Data.Entity;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Linq;

    public partial class Model : DbContext
    {
        public Model()
            : base("name=EntityModel")
        {
        }

        public virtual DbSet<Address> Addresses { get; set; }
        public virtual DbSet<Employee> Employees { get; set; }
        public virtual DbSet<EmployeeDetail> EmployeeDetails { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Address>()
                .Property(e => e.AddressLine1).IsUnicode(false);

            modelBuilder.Entity<Address>()
                .Property(e => e.AddressLine2).IsUnicode(false);

            modelBuilder.Entity<Address>()
                .Property(e => e.AddressLine3).IsUnicode(false);

            modelBuilder.Entity<Address>()
                .Property(e => e.City).IsUnicode(false);

            modelBuilder.Entity<Address>()
                .Property(e => e.State).IsUnicode(false);

            modelBuilder.Entity<Address>()
                .Property(e => e.Pincode).IsUnicode(false);

            modelBuilder.Entity<Employee>()
                .Property(e => e.Code).IsUnicode(false);

            modelBuilder.Entity<Employee>()
                .Property(e => e.Name).IsUnicode(false);

            modelBuilder.Entity<Employee>()
                .HasOptional(e => e.EmployeeDetail).WithRequired(e => e.Employee);

            modelBuilder.Entity<Employee>()
                .HasMany(e => e.Addresses).WithMany(e => e.Employees).Map(m => m.ToTable("EmployeeAddress").MapLeftKey("EmployeeId").MapRightKey("AddressId"));

            modelBuilder.Entity<EmployeeDetail>()
                .Property(e => e.Email).IsUnicode(false);

            modelBuilder.Entity<EmployeeDetail>()
                .Property(e => e.PhoneNo).IsUnicode(false);
        }
    }
}

Domain (POCO) classes

public partial class Address
{
    public Address()
    {
        Employees = new HashSet<Employee>();
    }

    public int Id { get; set; }

    [StringLength(50)]
    public string AddressLine1 { get; set; }

    [StringLength(50)]
    public string AddressLine2 { get; set; }

    [StringLength(50)]
    public string AddressLine3 { get; set; }

    [StringLength(20)]
    public string City { get; set; }

    [StringLength(20)]
    public string State { get; set; }

    [StringLength(10)]
    public string Pincode { get; set; }

    public virtual ICollection<Employee> Employees { get; set; }
}

[Table("Employee")]
public partial class Employee
{
    public Employee()
    {
        Addresses = new HashSet<Address>();
    }

    public int Id { get; set; }

    [StringLength(20)]
    public string Code { get; set; }

    [StringLength(50)]
    public string Name { get; set; }

    public virtual EmployeeDetail EmployeeDetail { get; set; }

    public virtual EmployeeExtendedDetails EmployeeExtendedDetail { get; set; }

    public virtual ICollection<Address> Addresses { get; set; }
}

[Table("EmployeeDetail")]
public partial class EmployeeDetail
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    [StringLength(255)]
    public string Email { get; set; }

    [StringLength(50)]
    public string PhoneNo { get; set; }

    public virtual Employee Employee { get; set; }
}

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.

List<Employee> emp = null;

using (Model context = new Model())
{
    emp = context.Employees.ToList();
}

Test 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.

CREATE TABLE [dbo].[EmployeeExtendedDetails]
(
    [EmployeeID] [int] NOT NULL,
      NULL,
    [Notes] [nvarchar](max) NULL,
    [PassportNumber] [nvarchar](20) NULL,
    [PassportExpiryDate] [date] NULL,
    CONSTRAINT [PK_EmployeeExtendedDetails] PRIMARY KEY CLUSTERED
    (
        [EmployeeID] ASC
    ) WITH (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[EmployeeExtendedDetails] WITH CHECK ADD CONSTRAINT [FK_EmployeeExtendedDetails_Employee] FOREIGN KEY ([EmployeeID])
REFERENCES [dbo].[Employee]([ID])
ON DELETE CASCADE

INSERT INTO EmployeeExtendedDetails (EmployeeID, DisplayName, Notes, PassportNumber, PassportExpiryDate)
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.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstonExsitingDB.Models
{
    [Table("EmployeeExtendedDetails")]
    public class EmployeeExtendedDetails
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Key]
        public int EmployeeId { get; set; }

        [StringLength(50)]
        public string DisplayName { get; set; }

        public string Notes { get; set; }

        [StringLength(20)]
        public string PassportNumber { get; set; }

        public DateTime PassportExpiryDate { get; set; }

        public virtual Employee Employee { get; set; }
    }
}

Step 2. Create a DbSet property for the newly created Domain class within the DbContext class.

public partial class Model : DbContext
{
    // Other members and methods

    public virtual DbSet<EmployeeExtendedDetails> EmployeeExtendedDetails { get; set; }
}

Step 3. Create a navigation property in the relational class.

public partial class Employee
{
    // Other properties and methods here...

    public virtual EmployeeExtendedDetails EmployeeExtendedDetail { get; set; }
}

Step 4. Add relations in the OnModelCreating virtual method if any.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // Other configurations...

    modelBuilder.Entity<Employee>()
                .HasOptional(e => e.EmployeeExtendedDetail)
                .WithRequired(e => e.Employee);
}

Step 5. Run the application and check the 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.