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.