Entity Framework: Code First Migrations With the Existing Database

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.