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

0
90

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.

LEAVE A REPLY