Contents
Mapping in Entity Framework CodeFirst
Mapping in Entity Framework CodeFirst
Introduction
What is Entity Framework? And what is the Code First approach?
Microsoft’s Entity Framework is Open Source Object Relational Mapping (ORM) that enables us with domain-specific objects and Relational data.
In Entity Framework we use LINQ to access the database Retrieve and manipulate data in the ORM as strongly typed objects.
In Entity Framework we use LINQ to access the database Retrieve and manipulate data in the ORM as strongly typed objects.
CodeFirst
CodeFirst is the approach where create the classes for the particular table according to which tables are generated in the SQL database.
For this demonstration we shall use Code First with Existing Database Approach.
Database Structure
This demonstration will include a database which show the relationship between Vendor, VendorProduct, Product and Customer.
Here is the SQL Script to create the above table:
Create Database EFCodefirstDB GO Use EFCodefirstDB GO Create Table Product (ProductId int primary key, ProductName varchar(50) ) Insert into Product values (1,'Shoes') Insert into Product values (2,'Shirt') GO Create Table Vendor (VendorId int primary key, VendorName varchar(50) ) Insert into Vendor values (1,'Vendor1') Insert into Vendor values (2,'Vendor2') GO Create table VendorProduct (VendorProductId int primary key, VendorId_fk int references Vendor(VendorId), ProductId_fk int references Product(ProductId) ) Insert into VendorProduct values(1,1,1) Insert into VendorProduct values(2,1,2) Insert into VendorProduct values(3, 2,2) GO Create table Customer (CustomerId int primary key, CustomerName nvarchar(50), CustomerCode nvarchar(50), Productid_fk int references Product(ProductId) ) Insert into Customer values(1,'Sagar',1001,1) Insert into Customer values(2,'John',2001,2) GO
Here is a Diagram for Database Structure
Here Where VendorId, VendorProductId, ProductId and CustomerId are primary keys.
Also VendorId_fk and Productid_fk are foreign keys which reference Vendor and Product tables.
Table Relationship
A relational database in SQL consists of information and data organized into different tables.
There are three types of Relationships in Relational Databases between different tables.
These relationships are used to prevent entry of data which is inconsistent and enforce referential integrity.
One-to-One Relationship :- In this relationship a row from the particular table is only associated with one row from another table and not more than that.
One-to-Many Relationships :- In this relationship one row from the particular table can be associated with one row or more rows from another table.
Many-to-Many Relationships :-In this relationship one or more row from the particular table can be associated with one or more rows from another table.
In our Example Vendor and Product have Many-to-Many Relationship where one product can be sold by multiple vendors.
Customer and Product have One-to-One Relationship where one customer can have only one product.
Setting up the Project
Steps to Setting up the Project as follows :-
✔ Create a simple Console Application under Visual C#
✔ Add Entity Framework dependencies via Nuget Package Manager to the Project
✔ Ensure that you have already ran the SQL Script in the above Context in SQL Server
✔ Create a folder called Model and Add an ADO.Net Entity Data Model to it.
✔ Configure the ADO.Net Entity Data Model and with Empty CodeFirst Option in wizard.
✔ Create Corresponding Models and Mapping Code as Shown in the Next Section.
Mapping Code
Assuming you are Successful in creating and setting up the project as shown above it should have the following code in Models:
Customer.cs
namespace EFCFExample.Model
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Spatial;
[Table("Customer")]
public partial class Customer
{
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int CustomerId { get; set; }
[StringLength(50)]
public string CustomerName { get; set; }
[StringLength(50)]
public string CustomerCode { get; set; }
public int? Productid_fk { get; set; }
public virtual Product Product { get; set; }
}
}
Product.cs
namespace EFCFExample.Model
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Spatial;
[Table("Product")]
public partial class Product
{
public Product()
{
Customers = new HashSet();
VendorProducts = new HashSet();
}
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int ProductId { get; set; }
[StringLength(50)]
public string ProductName { get; set; }
public virtual ICollection Customers { get; set; }
public virtual ICollection VendorProducts { get; set; }
}
}
Vendor.cs
[Table("Vendor")]
public partial class Vendor
{
public Vendor()
{
VendorProducts = new HashSet();
}
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int VendorId { get; set; }
[StringLength(50)]
public string VendorName { get; set; }
public virtual ICollection VendorProducts { get; set; }
}
}
VendorProduct.cs
[Table("VendorProduct")]
public partial class VendorProduct
{
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int VendorProductId { get; set; }
public int? VendorId_fk { get; set; }
public int? ProductId_fk { get; set; }
public virtual Product Product { get; set; }
public virtual Vendor Vendor { get; set; }
}
Add Connection String to App.Config file between the <connectionStrings></connectionStrings>
The Mapping Code for DbContext created should be as follows with relationships pertaining as per specified above:
public virtual DbSet Customers { get; set; }
public virtual DbSet Products { get; set; }
public virtual DbSet Vendors { get; set; }
public virtual DbSet VendorProducts { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity()
.Property(e => e.ProductName)
.IsUnicode(false);
modelBuilder.Entity()
.HasMany(e => e.Customers)
.WithOptional(e => e.Product)
.HasForeignKey(e => e.Productid_fk);
modelBuilder.Entity()
.HasMany(e => e.VendorProducts)
.WithOptional(e => e.Product)
.HasForeignKey(e => e.ProductId_fk);
modelBuilder.Entity()
.Property(e => e.VendorName)
.IsUnicode(false);
modelBuilder.Entity()
.HasMany(e => e.VendorProducts)
.WithOptional(e => e.Vendor)
.HasForeignKey(e => e.VendorId_fk);
}
Your Program.cs file should contain the following code for a simple demonstration:
class Program
{
static void Main(string[] args)
{
AllRecordsofCustomers();
SelectCustwithProd();
VendorProductList();
}
static void AllRecordsofCustomers()
{
using(ProductDbContext _db = new ProductDbContext())
{
var cust = _db.Customers.SqlQuery("Select * from Customer").ToList();
Console.WriteLine("All the Records for Customers are as follows :");
Console.WriteLine("*********************************");
foreach(var data in cust)
{
Console.WriteLine("{0}\t{1}\t{2}",data.CustomerId,
data.CustomerCode,data.CustomerName);
}
Console.WriteLine("*********************************");
Console.ReadLine();
}
}
}
}
Output :-
So a Mapping Code demonstration for Code first approach in Entity Framework is concluded.
