Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / web / ASP.NET

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

2.43/5 (4 votes)
18 May 2017CPOL1 min read 11.7K  
How to create a set of data models from a set of database tables.

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

Image 1

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

C#
[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.

C#
[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.

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

Data Model class for Shippers Table

C#
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

C#
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

C#
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

C#
[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

C#
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)