Convert a Database Schema to related Data Models for Entity Framework Code First development

Introduction

In this article I shall start from a table diagram and will show how to create necessary data models from it for entity framework code first development.

Consider the following table diagram

The above diagram shows a database schema with five tables. In the following discussion we shall see how to create necessary Data Models to setup mapping with these tables.

Data Model class for Employees Table

[Table("Employees")]
    public class Employee
    {
        [Key]
        public int EmployeeID { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public string Title { get; set; }
        public string TitleOfCourtesy { get; set; }
        [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]
        public DateTime BirthDate { get; set; }
        [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]
        public DateTime HireDate { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string Region { get; set; }
        public string PostalCode { get; set; }
        public string Country { get; set; }
        public string HomePhone { get; set; }
        public string Extension { get; set; }
        public byte[] Photo { get; set; }
        public string Notes { get; set; }
        public int? ReportsTo { get; set; }
        public string PhotoPath { get; set; }
        [ForeignKey("ReportsTo")]
        public virtual Employee ReportsEmployees { get; set; }
        public virtual ICollection<Orders> Orders { get; set; }
    }

Here [Key] attribute sets the primary key.

The following two lines are used to setup a recursive relationship, that means reference itself. The foreign key column name is ReportsTo.

[ForeignKey("ReportsTo")]
public virtual Employee ReportsEmployees { get; set; }

[Table("Employees")] attribute is used to rename the database table.

The following line shows that Employees table has a one to many relationship with Orders.

public virtual ICollection<Orders> Orders { get; set; }

Data Model class for Shippers Table

public class Shippers
    {
        [Key]
        public int ShipperID { get; set; }
        public string CompanyName { get; set; }
        public string Phone { get; set; }
        public virtual ICollection<Orders> Orders { get; set; }
    }

Here [Key] is used for primary key setup and Shippers has a one to many relation with Orders.

Data Model class for Orders Table

public class Orders
{
    [Key]
    public int OrderID { get; set; }
    public int? CustomerID { get; set; }
    public int? EmployeeID { get; set; }
    public DateTime OrderDate { get; set; }
    public DateTime RequiredDate { get; set; }
    public DateTime ShippedDate { get; set; }
    public int? ShipVia { get; set; }
    public Decimal Freight { get; set; }
    public string ShipName { get; set; }
    public string ShipAddress { get; set; }
    public string ShipCity { get; set; }
    public string ShipRegion { get; set; }
    public string ShipPostalCode { get; set; }
    public string ShipCountry { get; set; }
    public virtual Employee Employee { get; set; }
    [ForeignKey("ShipVia")]
    public virtual Shippers Shipper { get; set; }
    public virtual ICollection<OrderDetails> OrderDetails { get; set; }
}

Orders table has a many to one relation with Employees and Shippers, and has a one to many relation with OrderDetails.

[ForeignKey("ShipVia")] shows the foreign key.

Data Model class for Products Table

public class Products
{
    [Key]
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int? SupplierID { get; set; }
    public int? CategoryID { get; set; }
    public string QuantityPerUnit { get; set; }
    public decimal UnitPrice { get; set; }
    public Int16 UnitsInStock { get; set; }
    public Int16 UnitsOnOrder { get; set; }
    public Int16 ReorderLevel { get; set; }
    public bool Discontinued { get; set; }
    public virtual ICollection<OrderDetails> OrderDetails { get; set; }
}

Products table has a one to many relation with OrderDetails.

Data Model class for Order Details Table

[Table("Order Details")]
public class OrderDetails
{
    public int OrderID { get; set; }
    public int ProductID { get; set; }
    public decimal UnitPrice { get; set; }
    public Int16 Quantity { get; set; }
    public Single Discount { get; set; }
    public virtual Orders Order { get; set; }
    public virtual Products Product { get; set; }
}

Order Details table has a many to one relation with both Orders and Products table. That means Orders and Products table has a many to many relation.

To setup the composite primary key for Order Details table, I have wrote the following Fluent API

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{

    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<OrderDetails>()
        .HasKey(e => new { e.OrderID, e.ProductID });
}

Conclusion

Fluent API also can be used to setup the mapping with tables. I have used Data Annotation for most mapping setup and used Fluent API for single time here.