Entity Framework Tutorial Vedeo Series

Entity Framework Tutorial

In this video series we will discuss all the features of entity framework with examples. We will discuss
1. Schema First Approach
2. Model First Approach
3. Code First Approach

We will also discuss using entity framework in asp.net webforms and asp.net mvc applications.




Part 1 - What is Entity Framework

Part 2 - Entity Framework Model First Approach

Part 3 - Entity Framework Code First Approach

Part 4 - Customizing table, column and foreign key column names when using entity framework code first approach

Part 5 - How to handle model changes in entity framework




Part 6 - How to seed database with test data using entity framework

Part 7 - Using stored procedures with entity framework

Part 8 - Using stored procedures with entity frameowrk code first approach

Part 9 - Overriding stored procedure defaults with entity framework code first approach

Part 10 - Entity splitting in entity framework

Part 11 - Entity splitting in entity framework with code first approach

Part 12 - Table splitting in entity framework

Part 13 - Table splitting in entity framework with code first approach

Part 14 - Conditional Mapping in entity framework

Part 15 - Conditional Mapping in entity framework with code first

Part 16 - Self referencing association in entity framework

Part 17 - Self referencing association in entity framework wth code first

Part 18 - Table Per Hierarchy (TPH) inheritance in entity framework

Part 19 - Table Per Hierarchy (TPH) inheritance in entity framework with code first

Part 20 - Table Per Type (TPT) inheritance in entity framework

Part 21 - Table Per Type (TPT) inheritance in entity framework code first

Part 22 - Many to many relationship in entity framework

Part 23 - Many to many relationship in entity framework code first

Part 24 - Entity for BridgeTable in many to many relationship

Part 25 - Entity for BridgeTable in many to many relationship code first
Part 1 - What is Entity Framework

What is Entity Framework
Entity Framework is an ORM framework. ORM stands for Object Relational Mapping.

What is Object Relational Mapping framework
Object Relational Mapping framework automatically creates classes based on database tables, and the vice versa is also true, that is, it can also automatically generate necessary SQL to create database tables based on classes.




Let's understand what entity framework can provide with an example. Assume we have the following 2 tables
Entity framework example

Entity framework example in asp.net

We want to display the above data from both the tables in a webform as shown below.
asp.net entity framework example

To achieve this
1. We need to create Department and Employee classes
2. Write ADO.NET code to retrieve data from the database
3. Once the data is retrieved we need to create Department and Employee objects and populate them with data.

Entity Framework can do all of the above automatically, if we provide it with the database schema.

Installing NuGet Package Manager
1. From Visual Studio 2010 Tools menu, select Extension Manager
2. Click on Online Gallery in the Extension Manager window
3. Search for NuGet
4. Finally Download NuGet Package Manager and install
Installing NuGet Package Manager

Please note: You must restart visual studio for the changes to take effect.

Step 1: Create a new "Empty ASP.NET Web Application" with name=Demo.

Step 2: Installing Entity Framework
a) Click on Tools - NuGet Package Manager - Manage NuGet Packages for solution
b) Click on "Online" tab in "Manage NuGet Packages" window
c) Type "EntityFramework" in the search textbox on the top right hand corner
d) Finally click on the "Install" button.
Installing Entity Framework

At this point Entity Framework version 6.1 is installed and a reference to EntityFramework assembly is also automatically added.
Installing Entity Framework 6.0

Step 3: Create "Departments" and "Employees" tables.
Create table Departments
(
     ID int primary key identity,
     Name nvarchar(50),
     Location nvarchar(50)
)

Create table Employees
(
     ID int primary key identity,
     FirstName nvarchar(50),
     LastName nvarchar(50),
     Gender nvarchar(50),
     Salary int,
     DepartmentId int foreign key references Departments(Id)
)

Step 4: Populate the tables created in Step 3, with data
Insert into Departments values ('IT', 'New York')
Insert into Departments values ('HR', 'London')
Insert into Departments values ('Payroll', 'Sydney')

Insert into Employees values ('Mark', 'Hastings', 'Male', 60000, 1)
Insert into Employees values ('Steve', 'Pound', 'Male', 45000, 3)
Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000, 1)
Insert into Employees values ('Philip', 'Hastings', 'Male', 45000, 2)
Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000, 2)
Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000, 3)
Insert into Employees values ('John', 'Stanmore', 'Male', 80000, 1)

Step 5: Right click on the project in solution explorer and add ADO.NET Entity Data Model. Change the name from Model1.edmx to EmployeeModel.edmx
Step 6: Select "Generate from database" and click "Next"
Step 7: Choose Your Data Connection
a) Click on "New Connection" button
b) Select "Microsoft SQL Server" as Data source, and ".Net Framework Data Provider for SQL Server" option from "Data provider" dropdownlist. Click Continue.
c) On "Connection Properties" screen, specify SQL Server Name. If you are using local installation of SQL Server then use (local) or . in the "server name" dropdownlist.
d) Specify the Authentication you want to use.
e) Select the database from "Select or enter database name" dropdownlist.
f) Finally "Test connection" and click "OK"
g) At this point we should be back on "Choose Your Data Connection" window. Make sure "Save entity connection settings in Web.Config as" checkbox is selected and change the name of the connection string to "EmployeeDBContext" and then Click "Next"

Step 8: On "Choose Your Database Objects" screen, select "Departments" and "Employees" tables. Change the Model Namespace to "EmployeeModel" and click "Finish". At this point you should have EmployeeModel.edmx created.
Entity framework data model example

EmployeeModel.Designer.cs file is also generated. This file contains Employee and Department classes. Tables are mapped to classes and columns are mapped to class properties.

Step 9: Add a webform. Drag and drop a GridView and an EntityDataSource control on the webform.

Step 10: Build the solution. Flip the WebForm1.aspx to design mode.
a) Right click on EntityDataSource control and select "Show smart tag" option from the context menu.
b) Click on "Configure Data Source" link
c) Select "Named Connection" radiobutton and select "EmployeeDBContext" from the dropdownlist.
d) Select "EmployeeDBContext" option from "DefaultContainerName" dropdownlist and click "Next"
e) On "Configure Data Selection" screen, select "Departments" from "EntitySetName" dropdownlist and click "Finish"
f) Right click on "GridView1" control and select "Show smart tag" option.
g) Click on "Auto Format" link and select "Colorful" option from "AutoFormat" window and click "OK".
h) Select "EntityDataSource1" from "Choose Data Source" dropdownlist.
I) Click on "Eidt Columns" link and add a "Template Field". Set HeaderText=Employees and click OK.
j) Now click "Edit Templates" link.
k) Drag and drop a GridView control
l) Select "Edit DataBindings" link
m) Select "Custom binding" radiobutton and type Eval("Employees") in "Code expression" textbox and click OK.
n) Select "End Template Editing" option from "GridView1" smart tasks pane.

Step 11: Right click on "EntityDataSource1" control and select "Properties". In properties window set Include=Employees

Run the web application and notice that Departments and Employees are displayed as expected. We have achieved all this without writing a single line of code.

In this demo, we have used schema first approach of entity framework. We can also use Model First or Code First approaches. We will discuss these in our subsequent .
using entity framework in asp.net example
Part 2 - Entity Framework Model First Approach
Suggested
Part 1 - What is Entity Framework




In this we will discuss using Model First Approach of Entity Framework. This is continuation to Part 1. Please watch Part 1 before proceeding.

Entity Framework supports the following three approaches
1. Schema First Approach - Discussed in Part 1
2. Model First Approach - In this video.
3. Code First Approach - Next Video




In the Model First Approach, we first create the Entity Model. That is we create
1. Entities
2. Relationships between entities
3. Inheritance hierarchies etc.

We do all this directly on the design surface of the EDMX file. We will be continuing with the same example that we started in Part 1 of Entity Framework Tutorial.

Step 1: First delete the "EmployeeModel.edmx" file from the existing solution

Step 2: Add a new "ADO.NET Entity Data Model" file with name = EmployeeModel.

Step 3: On "Choose Model Contents" screen, select "Empty Model" and click "Finish"
model-first example in entity framework

Step 4: At this point, we have a blank EmployeeModel.edmx file added to the solution. Now, right click on the designer surface and then select : Add - Entity. Set,
Entity Name = Department
Base Type = None
Entity Set = Departments
Create Key property = Select the check box
Property Name = Id
Property Type = Int32

Finally click OK.
Creating entity with model first approach

Step 5: Right click on "Department" entity and select "Add - Scalar Property". Change the property name to "Name". Right click on the "Name" property that we just added and select "properties" from the context menu. Notice that the "Type" of "Name" property is set to "String".

Step 6: Similarly add "Location" property for the "Department" entity. At this point, the "Department" entity should look as shown below.
Department entity

Step 7: Add "Employee" entity and the following scalar properties.
FirstName (Type = string)
LastName (Type = string)
Gender (Type = string)
Salary (Type = int)

Step 8: At this point, Department and Employee entities should look as shown below.
Department and employee entities

Step 9: We need to add "Employees" navigation property for the "Department" entity, and "Department" navigation property for the "Employee" entity. To do this, right click on the design surface and select "Add - Association". In the "Add Association" window, set values as shown in the image below and click "OK"
How to add navigation property to an entity

Step 10: At this point, we should have the following created.
a) Employees navigation property in the Department entity
b) Department navigation property in the Employee entity
c) DepartmentId scalar property
Department and employee entities with navigation properties

Step 11: Right click on the design surface, and select "Genedrate Database from Model..." option

Step 12: On "Choose Your Data Connection" screen, click "Next"

Step 13: At this point, we should have the required SQL generated to create
a) Departments table
b) Employees table
c) Primary key constraints for Departments and Employees tables
d) Foreign key constraint
e) Indexes

Step 14: Click Finish. Now, we should have "EmployeeModel.edmx.sql" file generated with the required SQL. If you already have "Departments" and "Employees" tables in the database, delete them. Right click on the file and select "Execute SQL" option from the context menu.

Step 15: Populate the "Departments" and "Employees" tables with sample data using the script below.
Insert into Departments values ('IT', 'New York')
Insert into Departments values ('HR', 'London')
Insert into Departments values ('Payroll', 'Sydney')

Insert into Employees values ('Mark', 'Hastings', 'Male', 60000, 1)
Insert into Employees values ('Steve', 'Pound', 'Male', 45000, 3)
Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000, 1)
Insert into Employees values ('Philip', 'Hastings', 'Male', 45000, 2)
Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000, 2)
Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000, 3)
Insert into Employees values ('John', 'Stanmore', 'Male', 80000, 1)

Step 16: Run the application by pressing CTRL + F5. We will get the following runtime error.
The specified default EntityContainer name 'EmployeeDBContext' could not be found in the mapping and metadata information.
Parameter name: defaultContainerName

Step 17: Open WebForm1.aspx in source mode, and set ConnectionString and DefaultContainerName properties of EntityDataSource control as shown below.
<asp:EntityDataSource ID="EntityDataSource1" runat="server"
    ConnectionString="name=EmployeeModelContainer"
    DefaultContainerName="EmployeeModelContainer"
    EnableFlattening="False"
    EntitySetName="Departments" Include="Employees">
</asp:EntityDataSource>

At this point we should get the output we expect.
Part 3 - Entity Framework Code First Approach
Suggested Videos
Part 1 - What is Entity Framework
Part 2 - Entity Framework Model First Approach




Entity Framework supports
1. Database first or schema first approach - Discussed in Part 1
2. Model first appraoch - Discussed in Part 2
3. Code first approach - In this video




Code-first approach allows us to create our custom classes first and based on those custom classes entity framework can generate database automatically for us. Let's understand this with an example. We will be modifying the example we worked with in Part 2.

Step 1: Delete EmployeeModel.edmx && EmployeeModel.edmx.sql files from the solution explorer.

Step 2: Add a class file to the project. Name it Employee.cs. Copy and paste the following code.
public class Employee
{
    // Scalar Properties
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Gender { get; set; }
    public int Salary { get; set; }

    // Navigation Property
    public Department Department { get; set; }
}

Step 3: Add a class file to the project. Name it Department.cs. Copy and paste the following code.
public class Department
{
    // Scalar Properties
    public int Id { get; set; }
    public string Name { get; set; }
    public string Location { get; set; }

    // Navigation Property
    public List<Employee> Employees { get; set; }
}

Step 4: Add a class file to the project. Name it EmployeeDBContext.cs. Copy and paste the following code.
// EmployeeDBContext class must inherit from DbContext
// present in System.Data.Entity namespace
public class EmployeeDBContext : DbContext
{
    public DbSet<Department> Departments { get; set; }
    public DbSet<Employee> Employees { get; set; }
}

Step 5: Add a class file to the project. Name it EmployeeRepository.cs. Copy and paste the following code.
public class EmployeeRepository
{
    public List<Department> GetDepartments()
    {
        EmployeeDBContext employeeDBContext = new EmployeeDBContext();
        return employeeDBContext.Departments.Include("Employees").ToList();
    }
}

Step 6: Add the database connection string in web.config file.
<connectionStrings>
  <add name="EmployeeDBContext"
       connectionString="server=.; database=Sample; integrated security=true;"
       providerName="System.Data.SqlClient"/>
</connectionStrings>

Please Note: If ProviderName is not specified the following runtime error will be thrown.
The connection string 'EmployeeDBContext' in the application's configuration file does not contain the required providerName attribute."

Step 7: Configure Object Data Source control
a) Delete EntityDataSource control, that is already there in WebForm1.aspx.
b) Drag and Drop ObjectDataSource control.
c) Right click on ObjectDataSource control and select "Show Smart Tag" option from the context menu
d) Click on "Configure Data Source..." link
e) On "Choose a Business Object" screen, select "EmployeeRepository" and click "Next"
f) On "Define Data Methods" screen, select GetDepartments() method and click "Finish"

Step 8: Configure GridView control
a) Right click on GridView control and select "Show Smart Tag" option from the context menu
b) Select "ObjectDataSource1" from "Choose Data Source" dropdownlist
c) Click "No" to "Refresh Fields and Keys for GridView1" when prompted

Step 9: Rebuild the solution.

Step 10: Delete the already existing database from SQL Server Management Studio.

Step 11: Run the application by pressing CTRL + F5. Notice that we don't have any data displayed on WebForm1. This is because we don't have any data in the Departments and Employees tables. At this point we have the following created automatically.
a) Sample database
b) Departments table
c) Employees table

Step 12: Use the SQL script to populate the tables with data.
Insert into Departments values ('IT', 'New York')
Insert into Departments values ('HR', 'London')
Insert into Departments values ('Payroll', 'Sydney')

Insert into Employees values ('Mark', 'Hastings', 'Male', 60000, 1)
Insert into Employees values ('Steve', 'Pound', 'Male', 45000, 3)
Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000, 1)
Insert into Employees values ('Philip', 'Hastings', 'Male', 45000, 2)
Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000, 2)
Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000, 3)
Insert into Employees values ('John', 'Stanmore', 'Male', 80000, 1)

Step 13: Refresh the Web Form and we should see the data we expect.
Part 4 - Customizing table, column and foreign key column names when using entity framework code first approach
Suggested Videos
Part 1 - What is Entity Framework
Part 2 - Entity Framework Model First Approach
Part 3 - Entity Framework Code First Approach




In this vide we will discuss, customizing table, column and foreign key column names when using entity framework code first approach. This is continuation to Part 3. Please watch Part 3 before proceeding.




In Part 3, we have discussed generating Departments and Employees tables using Entity Framework Code first approach.

Entity Framework generated the following Employees table. Notice the column names. Department_Id column has an underscore in it's name. Let's say we want the column to be generated as DepartmenId (without an underscore)
Customizing foreign key column name using Entity Framework Code first approach

Entity Framework generated the above Employees table based on the following custom Employee class that we created.
public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Gender { get; set; }
    public int Salary { get; set; }
    public Department Department { get; set; }
}

To achieve this use the ForeignKey attribute present in System.ComponentModel.DataAnnotations.Schema namespace. Modify the Employee class as shown below.
public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Gender { get; set; }
    public int Salary { get; set; }
    public int DepartmentId { get; set; }
    [ForeignKey("DepartmentId")]
    public Department Department { get; set; }
}

Rebuild the solution, and run the application.

You will get the following error. We will discuss the reasons for this error and how to fix it the right way in a later video session.
The model backing the 'EmployeeDBContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).

For now to get around the error, delete the Sample database using SQL Server Management Studio, and then try to run the application again. A blank webform will be displayed. Now check the Employees tables using SQL Server Management Studio and notice that the DepartmentId column is created without an underscore as expected.
Influencing foreign key column naming in EF code first

To customize the table name, use Table attribute and to customize column name use Column attribute.

For example, to change
Table name from Employees to tblEmployees and
FirstName column to First_Name

We would modify the Employee class as shown below.
[Table("tblEmployees")]
public class Employee
{
    public int Id { get; set; }
    [Column("First_Name")]
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Gender { get; set; }
    public int Salary { get; set; }
    public int DepartmentId { get; set; }
    [ForeignKey("DepartmentId")]
    public Department Department { get; set; }
}

Entity Framework would then generate the following. Notice the table name and First_Name column.
Change Table and Column Name Mappings Entity Framework
Part 5 - How to handle model changes in entity framework
Suggested Videos
Part 2 - Entity Framework Model First Approach
Part 3 - Entity Framework Code First Approach
Part 4 - Customizing table & column names




In this video we will discuss, how to handle model changes after the database is already created. This is continuation to Part 4. Please watch Part 4 before proceeding.




At the moment the Employee class is as shown below
[Table("tblEmployees")]
public class Employee
{
    public int Id { get; set; }
    [Column("First_Name")]
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Gender { get; set; }
    public int Salary { get; set; }
    public int DepartmentId { get; set; }
    [ForeignKey("DepartmentId")]
    public Department Department { get; set; }
}

The following is the table Entity Framework has generated based on the above Employee class
The model backing the DBContext context has changed since the database was created. Consider using Code First Migrations to update the database

Now let us add JobTitle property to the Employee class. The modified Employee class is shown below.
[Table("tblEmployees")]
public class Employee
{
    public int Id { get; set; }
    [Column("First_Name")]
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Gender { get; set; }
    public int Salary { get; set; }
    public int DepartmentId { get; set; }
    [ForeignKey("DepartmentId")]
    public Department Department { get; set; }
    public string JobTitle { get; set; }
}

At this point if we run the application, we get the following error.
The model backing the 'EmployeeDBContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).

This is because the model (i.e Employee class) has changed since the database was created. This means the Model and the database are no longer in sync and hence we get the error. To check if the model has changed since the database was created, entity framework uses __MigrationHistory table that is auto-generated.

To fix this error, we have to tell entity framework what to do when the model changes.

Add Global.asax file to the web application project. Include the following code in Application_Start() method. Here, we are telling the entity framework to drop and recreate database every time the model changes.
Database.SetInitializer(new DropCreateDatabaseIfModelChanges<EmployeeDBContext>());

Another option is, to drop and recreate the database always. To drop and recreate the database always we would change the code in Application_Start() method as shown below.
Database.SetInitializer(new DropCreateDatabaseAlways<EmployeeDBContext>());

Please Note: Database class is present in System.Data.Entity namespace.

Run the application, and notice that the database is dropped and recreated. But the webform does not display any data, as there is no data in Departments and tblEmployees tables. For now let's manually populate the tables using the followng SQL script.
Insert into Departments values ('IT', 'New York')
Insert into Departments values ('HR', 'London')
Insert into Departments values ('Payroll', 'Sydney')

Insert into tblEmployees values ('Mark', 'Hastings', 'Male', 60000, 1, 'Developer')
Insert into tblEmployees values ('Steve', 'Pound', 'Male', 45000, 3, 'Manager')
Insert into tblEmployees values ('Ben', 'Hoskins', 'Male', 70000, 1, 'Developer')
Insert into tblEmployees values ('Philip', 'Hastings', 'Male', 45000, 2, 'Recruiter')
Insert into tblEmployees values ('Mary', 'Lambeth', 'Female', 30000, 2, 'Recruiter')
Insert into tblEmployees values ('Valarie', 'Vikings', 'Female', 35000, 3, 'Manager')
Insert into tblEmployees values ('John', 'Stanmore', 'Male', 80000, 1, 'Developer')

Referesh the webform. Notice that JobTitle is not displayed on the WebForm. To fix this add a boundfield to the GridView control that displays Employees details as shown below.
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"
                DataSource='<%# Eval("Employees") %>'>
    <Columns>
        <asp:BoundField DataField="FirstName" HeaderText="First Name" />
        <asp:BoundField DataField="LastName" HeaderText="Last Name" />
        <asp:BoundField DataField="Gender" HeaderText="Gender" />
        <asp:BoundField DataField="Salary" HeaderText="Salary" />
        <asp:BoundField DataField="JobTitle" HeaderText="Job Title" />
    </Columns>
</asp:GridView>
Part 6 - How to seed database with test data using entity framework
Suggested Videos
Part 3 - Entity Framework Code First Approach
Part 4 - Customizing table & column names
Part 5 - How to handle model changes in entity framework




So far in this video series, we have been manually populating the database with test data using the insert sql script. Entity Framework can automate this. We will be working with the example we worked with in Part 5. Here are the steps.




Step 1: Right click on the project in solution explorer and add a class file with name = EmployeeDBContextSeeder.cs

Step 2: Copy and paste the following code in EmployeeDBContextSeeder.cs file
using System.Collections.Generic;
using System.Data.Entity;
namespace EntityFrameworkDemo
{
    public class EmployeeDBContextSeeder :
        DropCreateDatabaseIfModelChanges<EmployeeDBContext>
    {
        protected override void Seed(EmployeeDBContext context)
        {
            Department department1 = new Department()
            {
                Name = "IT",
                Location = "New York",
                Employees = new List<Employee>()
                {
                    new Employee()
                    {
                        FirstName = "Mark",
                        LastName = "Hastings",
                        Gender = "Male",
                        Salary = 60000,
                        JobTitle = "Developer"
                    },
                    new Employee()
                    {
                        FirstName = "Ben",
                        LastName = "Hoskins",
                        Gender = "Male",
                        Salary = 70000,
                        JobTitle = "Sr. Developer"
                    },
                    new Employee()
                    {
                        FirstName = "John",
                        LastName = "Stanmore",
                        Gender = "Male",
                        Salary = 80000,
                        JobTitle = "Project Manager"
                    }
                }
            };

            Department department2 = new Department()
            {
                Name = "HR",
                Location = "London",
                Employees = new List<Employee>()
                {
                    new Employee()
                    {
                        FirstName = "Philip",
                        LastName = "Hastings",
                        Gender = "Male",
                        Salary = 45000,
                        JobTitle = "Recruiter"
                    },
                    new Employee()
                    {
                        FirstName = "Mary",
                        LastName = "Lambeth",
                        Gender = "Female",
                        Salary = 30000,
                        JobTitle = "Sr. Recruiter"
                    }
                }
            };
            Department department3 = new Department()
            {
                Name = "Payroll",
                Location = "Sydney",
                Employees = new List<Employee>()
                {
                    new Employee()
                    {
                        FirstName = "Steve",
                        LastName = "Pound",
                        Gender = "Male",
                        Salary = 45000,
                        JobTitle = "Sr. Payroll Admin",
                    },
                    new Employee()
                    {
                        FirstName = "Valarie",
                        LastName = "Vikings",
                        Gender = "Female",
                        Salary = 35000,
                        JobTitle = "Payroll Admin",
                    }
                }
            };

            context.Departments.Add(department1);
            context.Departments.Add(department2);
            context.Departments.Add(department3);

            base.Seed(context);
        }
    }
}

Step 3: Copy and paste the following line in Application_Start() method Global.asax file
Database.SetInitializer(new EmployeeDBContextSeeder());

Step 4: Remove the following Table and Column attributes from Employee.cs file.
[Table("tblEmployees")]
[Column("First_Name")]

At this point Employee class should look as shown below
public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Gender { get; set; }
    public int Salary { get; set; }
    public int DepartmentId { get; set; }
    [ForeignKey("DepartmentId")]
    public Department Department { get; set; }
    public string JobTitle { get; set; }
}

Step 5: Run the application and notice that the Sample database, Departments and Employees tables are created and populated with test data automatically.
Part 7 - Using stored procedures with entity framework
Suggested Videos
Part 4 - Customizing table & column names
Part 5 - How to handle model changes in entity framework
Part 6 - How to seed database with test data using entity framework




In this video we will discuss using our own custom stored procedures to perform Insert, Update and Delete operations using entity framework. We will be using the following Employees table for this demo.




Using stored procedures with entity frameowrk

Step 1: Use the following SQL Script to create and populate Employee table.
Create table Employees
(
     ID int primary key identity,
     Name nvarchar(50),
     Gender nvarchar(50),
     Salary int
)

Insert into Employees values ('Mark', 'Male', 60000)
Insert into Employees values ('Steve', 'Male', 45000)
Insert into Employees values ('Ben', 'Male', 70000)
Insert into Employees values ('Philip', 'Male', 45000)
Insert into Employees values ('Mary', 'Female', 30000)
Insert into Employees values ('Valarie', 'Female', 35000)
Insert into Employees values ('John', 'Male', 80000)

Step 2: Create Insert, Update and Delete stored procedures
Create procedure InsertEmployee
@Name nvarchar(50),
@Gender nvarchar(50),
@Salary int
as
Begin
     Insert into Employees values (@Name, @Gender, @Salary)
End
Go

Create procedure UpdateEmployee
@ID int,
@Name nvarchar(50),
@Gender nvarchar(50),
@Salary int
as
Begin
     Update Employees Set Name = @Name, Gender = @Gender,
     Salary = @Salary
     where ID = @ID
End
Go

Create procedure DeleteEmployee
@ID int
as
Begin
     Delete from Employees where ID = @ID
End
Go

Step 3: Create a new empty asp.net web application

Step 4: Add a new ADO.NET Entity Data Model.
a) On Choose Model Contents screen select "Generate from database" option and click Next
Using insert update delete stored procedures with entity frameowrk

b) On "Choose Your Data Connections" screen give a meaningful name for the connection string that will be stored in the web.config file. I have named it EmployeeDBContext. Click Next.
executing stored procedures in entity framework

c) On "Choose Your Database Objects" screen, select Employees Table and the 3 stored procedures (InsertEmployee, UpdateEmployee, DeleteEmployee). Provide a meaningful name for the Model namespace. I have named it EmployeeModel. CLick Finish.
executing insert update delete stored procedures with entity frameowrk

At this point on the ADO.NET Entity Model designer surface, we should be able to see the Employee entity but not the stored procedures.

To view the stored procedures,
1. Right click on entity model designer surface and select "Model Broswer" from the context menu.

2. Expand Stored Procedures folder
model browser in entity framework

Step 5: Add a web form to the project. Drag and drop the following 3 controls and build the solution.
1. GridView
2. DetailsView
3. EntityDataSource

Step 6: Configure EntityDataSource control

a). Right click on EntityDataSource control and select "Show Smart Tag" option

b) Click on Configure Data Source link

c) Select EmployeeDBContext from the Named Connection dropdownlist and click Next

d) Select the options on "Configure Data Selection" screen as shown in the image below and click Finish
configure entitydatasource

Step 7: Configure GridView control

a). Right click on GridView control and select "Show Smart Tag" option

b) Click on "Auto Format" link and select "Colourful" scheme

c) Select "EntityDataSource1" from "Choose Data Source" dropdownlist

d) Select Enable Editing and Enable Deleting checkboxes
configure gridview

Step 8: Configure DetailsView control
a) Right click on DetailsView control and select "Show Smart Tag" option
b) Click on "Auto Format" link and select "Colourful" scheme
c) Select "EntityDataSource1" from "Choose Data Source" dropdownlist
d) Select Enable Inserting checkbox
e) Set DeafultMode=Insert. Use properties window to set this.
f) Set InsertVisible="false" for the ID BoundField. You can do this directly in the HTML Source.
g) Generate ItemInserted event handler method for DetailsView control. Copy and paste the following code.
protected void DetailsView1_ItemInserted (object sender, DetailsViewInsertedEventArgs e)
{
    GridView1.DataBind();
}

At this point if you run the application, and if you insert, update and delete employees, by default entity framework will use the sql it auto-generates and not our custom stored procedures.

To tell the entity framework to use the stored procedures, we have to map them to the Employee entity.

Here are the steps.
1. Right click on "Employee" entity on "EmployeeModel.edmx" and select "Stored Procedure Mapping" option from the context menu.

2. In the "Mapping Details" windows specify the Insert, Update and Delete stored procedures that you want to use with "Employee" entity
stored procedure mapping in entity framework

At this point,
1. Run SQL Prrofiler
2. Run the application
3. Insert, Update and Delete Employee, and notice that the respective stored procedures are being called now.
Part 8 - Using stored procedures with entity framework code first approach
Suggested Videos
Part 5 - How to handle model changes in entity framework
Part 6 - How to seed database with test data using entity framework
Part 7 - Using stored procedures with entity framework




In this video we will discuss using stored procedures to perform Insert, Update and Delete operations using entity framework code first approach.




Step 1: Create a new empty asp.net web application project. Name it Demo. Install entity framework if it's not already installed.

Step 2: Add a class file to the project. Name it Employee.cs. Copy and paste the following code.
namespace Demo
{
    public class Employee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public int Salary { get; set; }
    }
}

Step 3: Add a class file to the project. Name it EmployeeDBContext.cs. Copy and paste the following code.
using System.Data.Entity;
namespace Demo
{
    public class EmployeeDBContext : DbContext
    {
        public DbSet<Employee> Employees { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            // This line will tell entity framework to use stored procedures
            // when inserting, updating and deleting Employees
            modelBuilder.Entity<Employee>().MapToStoredProcedures();
            base.OnModelCreating(modelBuilder);
        }
    }
}

Step 4: Add a class file to the project. Name it EmployeeRepository.cs. Copy and paste the following code.
using System.Collections.Generic;
using System.Linq;
namespace Demo
{
    public class EmployeeRepository
    {
        EmployeeDBContext employeeDBContext = new EmployeeDBContext();

        public List<Employee> GetEmployees()
        {
            return employeeDBContext.Employees.ToList();
        }

        public void InsertEmployee(Employee employee)
        {
            employeeDBContext.Employees.Add(employee);
            employeeDBContext.SaveChanges();
        }

        public void UpdateEmployee(Employee employee)
        {
            Employee employeeToUpdate = employeeDBContext
                .Employees.SingleOrDefault(x => x.ID == employee.ID);
            employeeToUpdate.Name = employee.Name;
            employeeToUpdate.Gender = employee.Gender;
            employeeToUpdate.Salary = employee.Salary;
            employeeDBContext.SaveChanges();
        }

        public void DeleteEmployee(Employee employee)
        {
            Employee employeeToDelete = employeeDBContext
                .Employees.SingleOrDefault(x => x.ID == employee.ID);
            employeeDBContext.Employees.Remove(employeeToDelete);
            employeeDBContext.SaveChanges();
        }
    }
}

Step 5: Add the database connection string in web.config file.
<connectionStrings>
  <add name="EmployeeDBContext"
    connectionString="server=.; database=Sample; integrated security=true;"
    providerName="System.Data.SqlClient" />
</connectionStrings>

Step 6: Add a webform to the project. Drag and drop the following 3 controls and build the solution.
1. GridView
2. DetailsView
3. ObjectDataSource

Step 7: Configure ObjectDataSource control
a). Right click on ObjectDataSource control and select "Show Smart Tag" option
b) Click on Configure Data Source link
c) Select Demo.EmployeeRepository on Choose a Business Object screen and click Next
d) On Define Data Methods screen
    i) On SELECT tab - Select GetEmployees() method
    ii) On UPDATE tab - Select UpdateEmployees(Employee employee) method
    iii) On INSERT tab - Select InsertEmployees(Employee employee) method
    iv) On DELETE tab - Select DeletEmployees(Employee employee) method

Step 8: Configure GridView control
a). Right click on GridView control and select "Show Smart Tag" option
b) Click on "Auto Format" link and select "Colourful" scheme
c) Select "ObjectDataSource1" from "Choose Data Source" dropdownlist
d) Select Enable Editing and Enable Deleting checkboxes
e) Set DataKeyNames="ID". Do this in the properties window of the GridView control

Step 9: Configure DetailsView control
a) Right click on DetailsView control and select "Show Smart Tag" option
b) Click on "Auto Format" link and select "Colourful" scheme
c) Select "ObjectDataSource1" from "Choose Data Source" dropdownlist
d) Select Enable Inserting checkbox
e) Set DeafultMode=Insert. Use properties window to set this.
f) Set InsertVisible="false" for the ID BoundField. You can do this directly in the HTML Source.

Step 10: If you already have Sample database in SQL Server. Delete it from SQL Server Management Studio.

Step 11: Run the application by pressing CTRL + F5. Notice that we don't have any data displayed on WebForm1. This is because we don't have any data in the Employees table.

At this point we have the Sample database and Employees table created automatically. The following stored procedures are also automatically created.
Employee_Delete
Employee_Insert
Employee_Update

By default, the following should be the naming convention for the stored procedures.
INSERT stored procedure - [Entity_Name]_Insert. Insert Stored procedure should return the auto-generated identity column value.
UPDATE stored procedure - [Entity_Name]_Update
DELETE stored procedure - [Entity_Name]_Delete

Step 12: Use the below SQL script to populate Employees tables with test data.
Insert into Employees values ('Mark', 'Male', 60000)
Insert into Employees values ('Steve', 'Male', 45000)
Insert into Employees values ('Ben', 'Male', 70000)
Insert into Employees values ('Philip', 'Male', 45000)
Insert into Employees values ('Mary', 'Female', 30000)
Insert into Employees values ('Valarie', 'Female', 35000)
Insert into Employees values ('John', 'Male', 80000)

At this point,
1. Run SQL Prrofiler
2. Run the application
3. Insert, Update and Delete Employees, and notice that the respective stored procedures are being called as expected
Part 9 - Overriding stored procedure defaults with entity framework code first approach
Suggested Videos
Part 6 - How to seed database with test data using entity framework
Part 7 - Using stored procedures with entity framework
Part 8 - Using stored procedures with entity frameowrk code first approach




In this video we will discuss, changing the default Insert, Update and Delete stored procedure names that are auto-generated by entity framework code first approach. This is continuation to Part 8. Please watch Part 8 before proceeding.




public class EmployeeDBContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>().MapToStoredProcedures();
        base.OnModelCreating(modelBuilder);
    }
}

By default, the code above generates the following 3 stored procedures for Inserting, Updating and Deleting Employee objects.
Employee_Insert
Employee_Update
Employee_Delete

If you want to override or change the default names of auto-generated stored procedures, change the code in EmployeeDBContext class as shown below.
public class EmployeeDBContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>()
            .MapToStoredProcedures(p => p.Insert(x => x.HasName("InsertEmployee")));
        modelBuilder.Entity<Employee>()
            .MapToStoredProcedures(p => p.Update(x => x.HasName("UpdateEmployee")));
        modelBuilder.Entity<Employee>()
            .MapToStoredProcedures(p => p.Delete(x => x.HasName("DeleteEmployee")));

        base.OnModelCreating(modelBuilder);
    }
}

At this point delete the Sample database and run WebForm1 again. Notice that the generated stored procedures now have the names we specified.
overriding the default stored procedure names in entity framework code first

The above code can also be rewritten as shown below
public class EmployeeDBContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>().MapToStoredProcedures
            (p => p.Insert(i => i.HasName("InsertEmployee"))
                    .Update(u => u.HasName("UpdateEmployee"))
                    .Delete(d => d.HasName("DeleteEmployee"))
            );
        base.OnModelCreating(modelBuilder);
    }
}

The default parameter names of the stored procedures can also be changed using the following code.
public class EmployeeDBContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>().MapToStoredProcedures
            (p => p.Insert(i => i.HasName("InsertEmployee")
                                    .Parameter(n => n.Name, "EmployeeName")
                                    .Parameter(n => n.Gender, "EmployeeGender")
                                    .Parameter(n => n.Salary, "EmployeeSalary"))
                    .Update(u => u.HasName("UpdateEmployee")
                                    .Parameter(n => n.ID, "EmployeeID")
                                    .Parameter(n => n.Name, "EmployeeName")
                                    .Parameter(n => n.Gender, "EmployeeGender")
                                    .Parameter(n => n.Salary, "EmployeeSalary"))
                    .Delete(d => d.HasName("DeleteEmployee")
                                    .Parameter(n => n.ID, "EmployeeID"))
            );
        base.OnModelCreating(modelBuilder);
    }
}

At this point drop the Sample database and run WebForm1 again. Notice that the stored procedure parameters have the names we specified.
overriding the default stored procedure parameter names in entity framework code first
Part 10 - Entity splitting in entity framework
Suggested Videos
Part 7 - Using stored procedures with entity framework
Part 8 - Using stored procedures with entity frameowrk code first approach
Part 9 - Overriding stored procedure defaults with entity frameowrk code first approach




Entity splitting refers to mapping an entity to two or more tables when the tables share a common key. Let us understand Entity splitting with an example.




We have the following 2 tables. Notice that both the table share the common key - EmployeeID.

Employees Table
Entity splitting

EmployeeContactDetails Table
Entity splitting in entity framework

SQL Script to create the database objects and populate them with test data
Create table Employees
(
     EmployeeID int primary key identity,
     FirstName nvarchar(50),
     LastName nvarchar(50),
     Gender nvarchar(50)
)
GO

Create table EmployeeContactDetails
(
     EmployeeID int primary key,
     Email nvarchar(50),
     Mobile nvarchar(50),
     LandLine nvarchar(50)
)
GO

Insert into Employees values ('Mark', 'Hastings', 'Male')
Insert into Employees values ('Steve', 'Pound', 'Male')
Insert into Employees values ('Ben', 'Hoskins', 'Male')
Insert into Employees values ('Philip', 'Hastings', 'Male')
Insert into Employees values ('Mary', 'Lambeth', 'Female')

Insert into EmployeeContactDetails values
(1, 'Mark@pragimtech.com', '111111111', '111111111')
Insert into EmployeeContactDetails values
(2, 'Steve@pragimtech.com', '2222222222', '2222222222')
Insert into EmployeeContactDetails values
(3, 'Ben@pragimtech.com', '3333333333', '3333333333')
Insert into EmployeeContactDetails values
(4, 'Philip@pragimtech.com', '44444444444', '44444444444')
Insert into EmployeeContactDetails values
(5, 'Mary@pragimtech.com', '5555555555', '5555555555')

Now, when we use ADO.NET Entity Framework to generate entities from the database using database first approach, by default 2 entities will be created, i.e Empoyee and EmployeeContactDetail entities.
Entity splitting example

There is a one to one mapping between tables and entities. We want a single Employee to map to both Employees & EmployeeContactDetails table.

To achieve this
1. Cut Email, Mobile and LandLine properties from EmployeeContactDetail entity and paste them in Employee entity
2. Delete EmployeeContactDetail entity. On "Delete Unmapped Tables and Views" window click NO.
3. Right click on Employee entity and select "Table Mapping" option from the context menu. Map EmployeeId, Email, Mobile and LandLine properties to the respective columns of EmployeeContactDetails table.
entity framework entity splitting example

At this point we have only one Entity. Build the solution. Add a WebForm. Drag and drop the following 3 controls.
1. GridView
2. DetailsView
3. EntityDataSource

Configure EntityDataSource control
a). Right click on EntityDataSource control and select "Show Smart Tag" option
b) Click on Configure Data Source link
c) Select EmployeeDBContext from the Named Connection dropdownlist and click Next
d) Select Employees from EntitySetName dropdownlist and enable Inserts, Updates and Deletes.

Configure GridView control
a). Right click on GridView control and select "Show Smart Tag" option
b) Click on "Auto Format" link and select "Colourful" scheme
c) Select "EntityDataSource1" from "Choose Data Source" dropdownlist
d) Select Enable Editing and Enable Deleting checkboxes

Configure DetailsView control
a) Right click on DetailsView control and select "Show Smart Tag" option
b) Click on "Auto Format" link and select "Colourful" scheme
c) Select "EntityDataSource1" from "Choose Data Source" dropdownlist
d) Select Enable Inserting checkbox
e) Set DeafultMode=Insert. Use properties window to set this.
f) Set InsertVisible="false" for the EmployeeID BoundField. You can do this directly in the HTML Source.
g) Generate ItemInserted event handler method for DetailsView control. Copy and paste the following code.
protected void DetailsView1_ItemInserted(object sender, DetailsViewInsertedEventArgs e)
{
       GridView1.DataBind();

}

At this point run the application. Insert, update and delete an Employee, and notice that both the tables (Employees and EmployeeContactDetails) are updated as expected.
Part 11 - Entity splitting in entity framework with code first approach
Suggested Videos
Part 8 - Using stored procedures with entity frameowrk code first approach
Part 9 - Overriding stored procedure defaults with entity frameowrk code first approach
Part 10 - Entity splitting in entity framework




Entity splitting refers to mapping an entity to two or more tables when the tables share a common key. We discussed, Entity splitting with database first approach in Part 10. In this video we will discuss Entity splitting with code first approach.




Step 1: Create a new empty asp.net web application project. Name it Demo. Install entity framework if it's not already installed.

Step 2: Add a class file to the project. Name it Employee.cs. Copy and paste the following code.
namespace Demo
{
    public class Employee
    {
        // These property values should be stored in Employees Table
        public int EmployeeId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Gender { get; set; }

        // These property values should be stored in EmployeeContactDetails Table
        public string Email { get; set; }
        public string Mobile { get; set; }
        public string Landline { get; set; }
    }
}

Step 3: Add a class file to the project. Name it EmployeeDBContext.cs. Copy and paste the following code.
using System.Data.Entity;
namespace Demo
{
    public class EmployeeDBContext : DbContext
    {
        public DbSet<Employee> Employees { get; set; }
    }
}

Step 4: Add a class file to the project. Name it EmployeeRepository.cs. Copy and paste the following code.
using System.Collections.Generic;
using System.Linq;
namespace Demo
{
    public class EmployeeRepository
    {
        EmployeeDBContext employeeDBContext = new EmployeeDBContext();

        public List<Employee> GetEmployees()
        {
            return employeeDBContext.Employees.ToList();
        }

        public void InsertEmployee(Employee employee)
        {
            employeeDBContext.Employees.Add(employee);
            employeeDBContext.SaveChanges();
        }

        public void UpdateEmployee(Employee employee)
        {
            Employee employeeToUpdate = employeeDBContext.Employees
                .SingleOrDefault(x => x.EmployeeId == employee.EmployeeId);
            employeeToUpdate.EmployeeId = employee.EmployeeId;
            employeeToUpdate.FirstName = employee.FirstName;
            employeeToUpdate.Gender = employee.Gender;
            employeeToUpdate.Email = employee.Email;
            employeeToUpdate.Mobile = employee.Mobile;
            employeeToUpdate.Landline = employee.Landline;

            employeeDBContext.SaveChanges();
        }

        public void DeleteEmployee(Employee employee)
        {
            Employee employeeToDelete = employeeDBContext.Employees
                .SingleOrDefault(x => x.EmployeeId == employee.EmployeeId);
            employeeDBContext.Employees.Remove(employeeToDelete);
            employeeDBContext.SaveChanges();
        }
    }
}

Step 5: Add the database connection string in web.config file.
<connectionStrings>
  <add name="EmployeeDBContext"
        connectionString="server=.; database=Sample; integrated security=SSPI;"
        providerName="System.Data.SqlClient" />
</connectionStrings>

Step 6: Add a webform to the project. Drag and drop the following 3 controls and build the solution.
1. GridView
2. DetailsView
3. ObjectDataSource

Step 7: Configure ObjectDataSource control
a).Right click on ObjectDataSource control and select "Show Smart Tag" option
b) Click on Configure Data Source link
c) Select Demo.EmployeeRepository on Choose a Business Object screen and click Next
d) On Define Data Methods screen
    i) On SELECT tab - Select GetEmployees() method
    ii) On UPDATE tab - Select UpdateEmployee(Employee employee) method
    iii) On INSERT tab - Select InsertEmployee(Employee employee) method
    iv) On DELETE tab - Select DeletEmployee(Employee employee) method

Step 8: Configure GridView control
a). Right click on GridView control and select "Show Smart Tag" option
b) Click on "Auto Format" link and select "Colourful" scheme
c) Select "ObjectDataSource1" from "Choose Data Source" dropdownlist
d) Select Enable Editing and Enable Deleting checkboxes
e) Set DataKeyNames="EmployeeId". Do this in the properties window of the GridView control
f) Set ReadOnly="true" for the EmployeeId BoundField. You can do this directly in the HTML Source.

Step 9: Configure DetailsView control
a) Right click on DetailsView control and select "Show Smart Tag" option
b) Click on "Auto Format" link and select "Colourful" scheme
c) Select "ObjectDataSource1" from "Choose Data Source" dropdownlist
d) Select Enable Inserting checkbox
e) Set DeafultMode=Insert. Use properties window to set this.
f) Set InsertVisible="false" for the EmployeeId BoundField. You can do this directly in the HTML Source.
g) Generate ItemInserted event handler method for DetailsView control. Copy and paste the following code.
protected void DetailsView1_ItemInserted(object sender, DetailsViewInsertedEventArgs e)
{
    GridView1.DataBind();
}

Step 10: If you already have Sample database in SQL Server. Delete it from SQL Server Management Studio.

Step 11: Run the application by pressing CTRL + F5. By default entity framework creates one Table i.e Employees table. But we want entity framework to create the following 2 tables.
a) Employees table with columns EmployeeId, FirstName, LastName and Gender
b) EmployeeContactDetails table with columns EmployeeId, Email, Mobile and  Landline

Step 12: Override OnModelCreating() method to tell entity framework to generate 2 tables(Employees & EmployeeContactDetails) for the Employee entity. OnModelCreating() method is a virtual method present in DbContext class. So, modify EmployeeDBContext class in EmployeeDBContext.cs file as shown below.
public class EmployeeDBContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>()
        // Specify properties to map to Employees table
        .Map(map =>
        {
            map.Properties(p => new
            {
                p.EmployeeId,
                p.FirstName,
                p.LastName,
                p.Gender
            });

            map.ToTable("Employees");
        })
        // Specify properties to map to EmployeeContactDetails table
        .Map(map =>
        {
            map.Properties(p => new
            {
                p.EmployeeId,
                p.Email,
                p.Mobile,
                p.Landline
            });

            map.ToTable("EmployeeContactDetails");
        });

        base.OnModelCreating(modelBuilder);
    }
}

Step 13: Delete the Sample database and run the web application.

Step 14: Notice that now we have 2 tables generated by entity framework as expected.
Entity splitting in entity framework with code first approach

Step 15: Execute the following SQL script to populate the tables with test data.
Insert into Employees values ('Mark', 'Hastings', 'Male')
Insert into Employees values ('Steve', 'Pound', 'Male')
Insert into Employees values ('Ben', 'Hoskins', 'Male')
Insert into Employees values ('Philip', 'Hastings', 'Male')

Insert into Employees values ('Mary', 'Lambeth', 'Female')

Insert into EmployeeContactDetails values
(1, 'Mark@pragimtech.com', '111111111', '111111111')
Insert into EmployeeContactDetails values
(2, 'Steve@pragimtech.com', '2222222222', '2222222222')
Insert into EmployeeContactDetails values
(3, 'Ben@pragimtech.com', '3333333333', '3333333333')
Insert into EmployeeContactDetails values
(4, 'Philip@pragimtech.com', '44444444444', '44444444444')
Insert into EmployeeContactDetails values

(5, 'Mary@pragimtech.com', '5555555555', '5555555555')

Step 16: At this point run the application. Insert, update and delete an Employee, and notice that both the tables (Employees and EmployeeContactDetails) are updated as expected.
Part 12 - Table splitting in entity framework
Suggested Videos
Part 9 - Overriding stored procedure defaults with entity frameowrk code first approach
Part 10 - Entity splitting in entity framework
Part 11 - Entity splitting in entity framework with code first approach




In this video we will discuss Table splitting in entity framework with database first approach. Table Splitting is the opposite of Entity Splitting.




We discussed Entity Splitting in Part 10 and 11 of Entity Framework tutorial.

Entity Splitting refers to mapping an entity to two or more tables when the tables share a common key.
Entity splitting in entity framework example

Mapping multiple entities to a single table is called table splitting.
table splitting in entity framework

One common entity framework interview question is, What is the main reason for using Table Splitting?
Table Splitting is useful when you want to delay the loading of some properties with large data when using lazy loading.

For example, if you have Employee entity and if it contains Photo property that would return large binary data, and if we use this Photo property only on a few pages in our application, then it does not make sense from a performance perspective to load this property every time we load the Employee entity. Using lazy loading load it only on the pages where we need to display Employee photo.

We will be using the following Employees table
Employees table

SQL script to create the table
Create table Employees
(
     EmployeeID int primary key identity,
     FirstName nvarchar(50),
     LastName nvarchar(50),
     Gender nvarchar(50),
     Email nvarchar(50),
     Mobile nvarchar(50),
     LandLine nvarchar(50)
)

Insert into Employees values ('Mark', 'Hastings', 'Male', 'x@x.com', 'XXX', 'XXX')
Insert into Employees values ('Steve', 'Pound', 'Male', 'y@y.com', 'YYY', 'YYY')
Insert into Employees values ('Ben', 'Hoskins', 'Male', 'z@z.com', 'ZZZ', 'ZZZ')
Insert into Employees values ('Philip', 'Hastings', 'Male', 'a@a.com', 'AAA', 'AAA')
Insert into Employees values ('Mary', 'Lambeth', 'Female', 'b@b.com', 'BBB', 'BBB')

Now, when we use ADO.NET Entity Framework to generate entities from the database using database first approach, by default one entity will be created, i.e Empoyee entity.
employee entity

Let's say, we will not be using Email, Mobile and Landline properties as often as we would be using FirstName, LastName, and Gender properties. If all of these properties are present in one Employee entity, then every time we load this entity, all the properties will be automatically loaded. So let's create 2 entities (Employee & EmployeeContactDetail). This enables us to load EmployeeContactDetails only when needed.

To achieve this:
1. Right click on the entity designer and select "Add Entity" option from the context menu. Set
    a) Entity Name = EmployeeContactDetail
    b) Bae type = (None)
    c) Entity Set = EmployeeContactDetails
    d) Create Key Property = Checked
    e) Property Name = EmployeeID
add entity

2. Cut Email, Mobile and LandLine properties from Employee entity and paste them in EmployeeContactDetail entity

3. Right click on the entity designer and select "Add - Association" option from the context menu. Fill the details shown below.
add association

4. Right click on the association and select "Properties". In the Properties window, click on the ellipsis button next to "Referential Constraint" property and fill in the details as shown below.
referential constraint

5. Right click on "EmployeeContactDetail" entity and select "Table Mapping" option from the context menu. Select "Employees" table and map EmployeeId, Email, Mobile and Landline properties of the entity to the respective columns of the table.
table mapping in entity framework

6. Add a webform. Copy and paste the following HTML in the ASPX page.
<div style="font-family:Arial">
    <asp:CheckBox ID="checkBoxIncludeContactDetails"
    Text="Include Contact Details" runat="server" />
    <br />
    <asp:Button ID="Button1" runat="server" Text="Get Employee Data"
        onclick="Button1_Click" />
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
</div>

7. Copy and paste the following code in the code-behind file.
public partial class WebForm1 : System.Web.UI.Page
{
    private DataTable GetEmployeeData()
    {
        EmployeeDBContext employeeDBContext = new EmployeeDBContext();
        List<Employee> employees = employeeDBContext.Employees.ToList();

        DataTable dataTable = new DataTable();
        DataColumn[] columns = { new DataColumn("EmployeeID"),
                                 new DataColumn("FirstName"),
                                 new DataColumn("LastName"),
                                 new DataColumn("Gender")};

        dataTable.Columns.AddRange(columns);

        foreach (Employee employee in employees)
        {
            DataRow dr = dataTable.NewRow();
            dr["EmployeeID"] = employee.EmployeeID;
            dr["FirstName"] = employee.FirstName;
            dr["LastName"] = employee.LastName;
            dr["Gender"] = employee.Gender;

            dataTable.Rows.Add(dr);
        }

        return dataTable;
    }

    private DataTable GetEmployeeDataIncludingContactDetails()
    {
        EmployeeDBContext employeeDBContext = new EmployeeDBContext();
        List<Employee> employees = employeeDBContext.Employees
            .Include("EmployeeContactDetail").ToList();

        DataTable dataTable = new DataTable();
        DataColumn[] columns = { new DataColumn("EmployeeID"),
                                 new DataColumn("FirstName"),
                                 new DataColumn("LastName"),
                                 new DataColumn("Gender"),
                                 new DataColumn("Email"),
                                 new DataColumn("Mobile"),
                                 new DataColumn("LandLine") };
        dataTable.Columns.AddRange(columns);

        foreach (Employee employee in employees)
        {
            DataRow dr = dataTable.NewRow();
            dr["EmployeeID"] = employee.EmployeeID;
            dr["FirstName"] = employee.FirstName;
            dr["LastName"] = employee.LastName;
            dr["Gender"] = employee.Gender;
            dr["Email"] = employee.EmployeeContactDetail.Email;
            dr["Mobile"] = employee.EmployeeContactDetail.Mobile;
            dr["LandLine"] = employee.EmployeeContactDetail.LandLine;

            dataTable.Rows.Add(dr);
        }

        return dataTable;
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        if (checkBoxIncludeContactDetails.Checked)
        {
            GridView1.DataSource = GetEmployeeDataIncludingContactDetails();
        }
        else
        {
            GridView1.DataSource = GetEmployeeData();
        }
        GridView1.DataBind();
    }
}

At this point, run the application and when you retrieve Employees without checking "Include Contact Details" checkbox, the following query is generated by the entity framework. Use SQL Profiler to view the generated query. Notice that Email, Mobile and LandLine column values are not loaded.
SELECT
[Extent1].[EmployeeID] AS [EmployeeID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Gender] AS [Gender]
FROM [dbo].[Employees] AS [Extent1]

When you check "Include Contact Details" checkbox, the following query is generated
SELECT
[Extent1].[EmployeeID] AS [EmployeeID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Gender] AS [Gender],
[Extent1].[Email] AS [Email],
[Extent1].[Mobile] AS [Mobile],
[Extent1].[LandLine] AS [LandLine]
FROM [dbo].[Employees] AS [Extent1]
Part 13 - Table splitting in entity framework with code first approach
Suggested Videos
Part 10 - Entity splitting in entity framework
Part 11 - Entity splitting in entity framework with code first approach
Part 12 - Table splitting in entity framework




In this video we will discuss Table splitting in entity framework with code first approach. We discussed Table Splitting with database first approach in Part 12.

Mapping multiple entities to a single table is called table splitting.




Step 1: Create a new empty asp.net web application project. Name it Demo. Install entity framework if it's not already installed.

Step 2: Add a class file to the project. Name it Employee.cs. Copy and paste the following code.
namespace Demo
{
    public class Employee
    {
        public int EmployeeID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Gender { get; set; }

        public EmployeeContactDetail EmployeeContactDetail { get; set; }
    }
}

Step 3: Add a class file to the project. Name it EmployeeContactDetail.cs. Copy and paste the following code.
namespace Demo
{
    public class EmployeeContactDetail
    {
        public int EmployeeID { get; set; }
        public string Email { get; set; }
        public string Mobile { get; set; }
        public string LandLine { get; set; }

        public Employee Employee { get; set; }
    }
}

Step 4: Add a class file to the project. Name it EmployeeDBContext.cs. Copy and paste the following code.
using System.Data.Entity;
namespace Demo
{
    public class EmployeeDBContext : DbContext
    {
        public DbSet<Employee> Employees { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Employee>()
                .HasKey(pk => pk.EmployeeID)
                .ToTable("Employees");

            modelBuilder.Entity<EmployeeContactDetail>()
                .HasKey(pk => pk.EmployeeID)
                .ToTable("Employees");

            modelBuilder.Entity<Employee>()
                .HasRequired(p => p.EmployeeContactDetail)
                .WithRequiredPrincipal(c => c.Employee);
        }
    }
}

Step 5: Add the database connection string in web.config file.
<connectionStrings>
  <add name="EmployeeDBContext"
      connectionString="server=.; database=Sample; integrated security=SSPI;"
      providerName="System.Data.SqlClient" />
</connectionStrings>

Step 6: Add a web form to the project. Copy and paste the following HTML in the aspx page
<div style="font-family:Arial">
    <asp:CheckBox ID="checkBoxIncludeContactDetails"
    Text="Include Contact Details" runat="server" />
    <br />
    <asp:Button ID="Button1" runat="server" Text="Get Employee Data"
        onclick="Button1_Click" />
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
</div>

Step 7: Copy and paste the following code in the code-behind file.
public partial class WebForm1 : System.Web.UI.Page
{
    private DataTable GetEmployeeData()
    {
        EmployeeDBContext employeeDBContext = new EmployeeDBContext();
        List<Employee> employees = employeeDBContext.Employees.ToList();

        DataTable dataTable = new DataTable();
        DataColumn[] columns = { new DataColumn("EmployeeID"),
                                 new DataColumn("FirstName"),
                                 new DataColumn("LastName"),
                                 new DataColumn("Gender")};

        dataTable.Columns.AddRange(columns);

        foreach (Employee employee in employees)
        {
            DataRow dr = dataTable.NewRow();
            dr["EmployeeID"] = employee.EmployeeID;
            dr["FirstName"] = employee.FirstName;
            dr["LastName"] = employee.LastName;
            dr["Gender"] = employee.Gender;

            dataTable.Rows.Add(dr);
        }

        return dataTable;
    }

    private DataTable GetEmployeeDataIncludingContactDetails()
    {
        EmployeeDBContext employeeDBContext = new EmployeeDBContext();
        List<Employee> employees = employeeDBContext.Employees
            .Include("EmployeeContactDetail").ToList();

        DataTable dataTable = new DataTable();
        DataColumn[] columns = { new DataColumn("EmployeeID"),
                                 new DataColumn("FirstName"),
                                 new DataColumn("LastName"),
                                 new DataColumn("Gender"),
                                 new DataColumn("Email"),
                                 new DataColumn("Mobile"),
                                 new DataColumn("LandLine") };
        dataTable.Columns.AddRange(columns);

        foreach (Employee employee in employees)
        {
            DataRow dr = dataTable.NewRow();
            dr["EmployeeID"] = employee.EmployeeID;
            dr["FirstName"] = employee.FirstName;
            dr["LastName"] = employee.LastName;
            dr["Gender"] = employee.Gender;
            dr["Email"] = employee.EmployeeContactDetail.Email;
            dr["Mobile"] = employee.EmployeeContactDetail.Mobile;
            dr["LandLine"] = employee.EmployeeContactDetail.LandLine;

            dataTable.Rows.Add(dr);
        }

        return dataTable;
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        if (checkBoxIncludeContactDetails.Checked)
        {
            GridView1.DataSource = GetEmployeeDataIncludingContactDetails();
        }
        else
        {
            GridView1.DataSource = GetEmployeeData();
        }
        GridView1.DataBind();
    }
}

At this point, run the application. Sample database and Employees table should be created by the entity framework.

Step 8: Insert test data using the following SQL script
Insert into Employees values ('Mark', 'Hastings', 'Male', 'x@x.com', 'XXX', 'XXX')
Insert into Employees values ('Steve', 'Pound', 'Male', 'y@y.com', 'YYY', 'YYY')
Insert into Employees values ('Ben', 'Hoskins', 'Male', 'z@z.com', 'ZZZ', 'ZZZ')
Insert into Employees values ('Philip', 'Hastings', 'Male', 'a@a.com', 'AAA', 'AAA')
Insert into Employees values ('Mary', 'Lambeth', 'Female', 'b@b.com', 'BBB', 'BBB')

When you retrieve Employees without checking "Include Contact Details" checkbox, the following query is generated by the entity framework. Use SQL Profile to view the generated query. Notice that Email, Mobile and LandLine column values are not loaded.
SELECT
[Extent1].[EmployeeID] AS [EmployeeID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Gender] AS [Gender]
FROM [dbo].[Employees] AS [Extent1]

When you check "Include Contact Details" checkbox, the following query is generated
SELECT
[Extent1].[EmployeeID] AS [EmployeeID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Gender] AS [Gender],
[Extent1].[Email] AS [Email],
[Extent1].[Mobile] AS [Mobile],
[Extent1].[LandLine] AS [LandLine]
FROM [dbo].[Employees] AS [Extent1]
Part 14 - Conditional Mapping in entity framework
Suggested Videos
Part 11 - Entity splitting in entity framework with code first approach
Part 12 - Table splitting in entity framework
Part 13 - Table splitting in entity framework with code first approach




In this video we will discuss Conditional Mapping feature in entity framework with database first approach. Let us understand what Conditional Mapping can do with an example.




We will be using the following Employees table in this demo. IsTerminated column determines if an employee is a terminated employee or not.
Conditional Mapping in entity framework

SQL Query to create Employees table
Create table Employees
(
     EmployeeID int primary key identity,
     FirstName nvarchar(50),
     LastName nvarchar(50),
     Gender nvarchar(50),
     IsTerminated bit not null
)
GO

Insert into Employees values ('Mark', 'Hastings', 'Male', 0)
Insert into Employees values ('Steve', 'Pound', 'Male', 0)
Insert into Employees values ('Ben', 'Hoskins', 'Male', 0)
Insert into Employees values ('Philip', 'Hastings', 'Male', 1)
Insert into Employees values ('Mary', 'Lambeth', 'Female', 0)
Insert into Employees values ('Valarie', 'Vikings', 'Female', 0)
Insert into Employees values ('John', 'Stanmore', 'Male', 1)

If the application that we are developing always need only the employees who are not terminated, then in the query we will have to always include the filter across our entire application. Conditional Mapping can be used to apply such a permanent filter on the entity, so that the generated SQL query always have the WHERE clause.

To use Conditional Mapping,
1. Right click on the entity and select "Table Mapping" option from the context menu
2. Add the condition - When Is Terminated = false
Conditional Mapping example

At this point, if you build the solution or validate the model, you will get the following error
Problem in mapping fragments starting at line 46:Condition member 'Employees.IsTerminated' with a condition other than 'IsNull=False' is mapped. Either remove the condition on Employees.IsTerminated or remove it from the mapping

This is because, a table column cannot be mapped more than once. We have used IsTerminated column in conditional mapping, so it cannot be used in property mapping as well. For this reason delete it from Employee entity.

Add a web form to the project. Drag and drop a GridView control. Copy and paste the following code in the code-behind file.
protected void Page_Load(object sender, EventArgs e)
{
    EmployeeDBContext employeeDBContext = new EmployeeDBContext();
    GridView1.DataSource = employeeDBContext.Employees;
    GridView1.DataBind();
}

Open SQL profiler and run the webform. Notice that the select query has a where clause, which will always return employees who are not terminated.
SELECT
[Extent1].[EmployeeID] AS [EmployeeID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Gender] AS [Gender]
FROM [dbo].[Employees] AS [Extent1]
WHERE [Extent1].[IsTerminated] = 0
Part 15 - Conditional Mapping in entity framework with code first
Suggested Videos
Part 12 - Table splitting in entity framework
Part 13 - Table splitting in entity framework with code first approach
Part 14 - Conditional Mapping in entity framework




In Part 14, we discussed Conditional Mapping in entity framework with database first approach. Please watch Part 14 before proceeding. In this video we will discuss Conditional Mapping in entity framework with code first approach.




Step 1: Create a new empty asp.net web application project. Name it Demo. Install entity framework if it's not already installed.

Step 2: Add a class file to the project. Name it Employee.cs. Copy and paste the following code.
namespace Demo
{
    public class Employee
    {
        public int EmployeeID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Gender { get; set; }
        public bool IsTerminated { get; set; }
    }
}

Step 3: Add a class file to the project. Name it EmployeeDBContext.cs. Copy and paste the following code.
using System.Data.Entity;
namespace Demo
{
    public class EmployeeDBContext : DbContext
    {
        public DbSet<Employee> Employees { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Employee>()
                .Map(m => m.Requires("IsTerminated")
                .HasValue(false))
                .Ignore(m => m.IsTerminated);

            base.OnModelCreating(modelBuilder);
        }
    }
}

Step 4: Add the database connection string in web.config file.
<connectionStrings>
  <add name="EmployeeDBContext"
            connectionString="server=.; database=Sample; integrated security=SSPI;"
            providerName="System.Data.SqlClient" />
</connectionStrings>

Step 5: Add a webform to the project. Drag and drop a GridView control.

Step 6: Copy and paste the following code in the code-behind file.
protected void Page_Load(object sender, EventArgs e)
{
    EmployeeDBContext employeeDBContext = new EmployeeDBContext();
    GridView1.DataSource = employeeDBContext.Employees.ToList();
    GridView1.DataBind();
}

Step 7: If you already have Sample database in SQL Server. Delete it from SQL Server Management Studio.

Step 8: Run the application. Sample database and Employees table must be created at this point.

Step 9: Insert test data using the following SQL script
Insert into Employees values ('Mark', 'Hastings', 'Male', 0)
Insert into Employees values ('Steve', 'Pound', 'Male', 0)
Insert into Employees values ('Ben', 'Hoskins', 'Male', 0)
Insert into Employees values ('Philip', 'Hastings', 'Male', 1)
Insert into Employees values ('Mary', 'Lambeth', 'Female', 0)
Insert into Employees values ('Valarie', 'Vikings', 'Female', 0)
Insert into Employees values ('John', 'Stanmore', 'Male', 1)

Step 10: Open SQL profiler and run the webform. Notice that the select query has a WHERE clause, which will always return employees who are not terminated.
SELECT
    [Extent1].[EmployeeID] AS [EmployeeID],
    [Extent1].[FirstName] AS [FirstName],
    [Extent1].[LastName] AS [LastName],
    [Extent1].[Gender] AS [Gender]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE [Extent1].[IsTerminated] = 0
Part 16 - Self referencing association in entity framework
Suggested Videos
Part 13 - Table splitting in entity framework with code first approach
Part 14 - Conditional Mapping in entity framework
Part 15 - Conditional Mapping in entity framework with code first




In this video, we will discuss self referencing association in entity framework with database first approach. Let us understand self-referencing association with an example.




We will be using the following Employees table in this demo. This table is a self-referencing table because to get the manager of an employee, we take ManagerId column value and look up in the EmployeeId column of the same table.
Self referencing association

SQL script to create the table
Create table Employees
(
     EmployeeID int primary key identity,
     EmployeeName nvarchar(50),
     ManagerID int foreign key references Employees(EmployeeID)
)
GO

Insert into Employees values ('John', NULL)
Insert into Employees values ('Mark', NULL)
Insert into Employees values ('Steve', NULL)
Insert into Employees values ('Tom', NULL)
Insert into Employees values ('Lara', NULL)
Insert into Employees values ('Simon', NULL)
Insert into Employees values ('David', NULL)
Insert into Employees values ('Ben', NULL)
Insert into Employees values ('Stacy', NULL)
Insert into Employees values ('Sam', NULL)
GO

Update Employees Set ManagerID = 8 Where EmployeeName IN ('Mark', 'Steve', 'Lara')
Update Employees Set ManagerID = 2 Where EmployeeName IN ('Stacy', 'Simon')
Update Employees Set ManagerID = 3 Where EmployeeName IN ('Tom')
Update Employees Set ManagerID = 5 Where EmployeeName IN ('John', 'Sam')
Update Employees Set ManagerID = 4 Where EmployeeName IN ('David')
GO

Now if you generate an ADO.NET entity data model based on this Employees table, the following Employee entity is generated. Notice that a self-referencing association and 2 navigation properties (Employees1 & Employee1) are automatically created.
Self referencing association in entity framework

Right click on Employees1 navigation property and select properties. In the properties window notice that the Multiplicity is set to Many. So, this navigation property returns employees who are subordinates.
Self referencing table in entity framework

Similarly, right click on Employee1 navigation property and select properties. In the properties window notice that the Multiplicity is set to Zero or One. So, this navigation property returns the manager of an employee.
Self referencing association in entity framework database first

From a developer perspective the default navigation property names that entity framework generates are not meaningful. If you have to write any code based on these navigation properties, it can get even more complicated.

For example, let us say we want to display Employee names and their respective manager names as shown below.
Self referencing association example

To achieve this we would drag and drop a GridView control on the web form and write the following code in the code-behind file Page_Load event. Notice that, because of the poor naming of the navigation properties the code is hard to read and maintain.
protected void Page_Load(object sender, EventArgs e)
{
    EmployeeDBContext employeeDBContext = new EmployeeDBContext();
    GridView1.DataSource = employeeDBContext.Employees.Select(emp => new
    {
        EmployeeName = emp.EmployeeName,
        ManagerName = emp.Employee1 == null ? "Super Boss"
                        : emp.Employee1.EmployeeName
    }).ToList();
    GridView1.DataBind();
}

Now let's give these navigation properties meaningful names. To do this,
1. Right click on Employees1 navigation property and rename it to Subordinates
2. Similarly, right click on Employee1 navigation property and rename it to Manager
Self referencing association in entity framework database first example

Now the code in the code-behind file would change as shown below which is more readable and maintainable.
protected void Page_Load(object sender, EventArgs e)
{
    EmployeeDBContext employeeDBContext = new EmployeeDBContext();
    GridView1.DataSource = employeeDBContext.Employees.Select(emp => new
    {
        EmployeeName = emp.EmployeeName,
        ManagerName = emp.Manager == null ? "Super Boss"
                                      : emp.Manager.EmployeeName
    }).ToList();
    GridView1.DataBind();
}
Part 17 - Self referencing association in entity framework with code first
Suggested Videos
Part 14 - Conditional Mapping in entity framework
Part 15 - Conditional Mapping in entity framework with code first
Part 16 - Self referencing association in entity framework




In this video, we will discuss self referencing association in entity framework with code first approach. This is continuation to Part 16, where we discussed self-referencing association with database first approach. Please watch Part 16 before proceeding.




Step 1: Create a new empty asp.net web application project. Name it Demo. Install entity framework if it's not already installed.

Step 2: Add a class file to the project. Name it Employee.cs. Copy and paste the following code.
namespace Demo
{
    public class Employee
    {
        // Scalar properties
        public int EmployeeID { get; set; }
        public string EmployeeName { get; set; }
        public int? ManagerID { get; set; }

        // Navigation property
        public Employee Manager { get; set; }
    }
}

Step 3: Add a class file to the project. Name it EmployeeDBContext.cs. Copy and paste the following code.
using System.Data.Entity;
namespace Demo
{
    public class EmployeeDBContext : DbContext
    {
        public DbSet<Employee> Employees { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Employee>()
                .HasOptional(e => e.Manager)
                .WithMany()
                .HasForeignKey(m => m.ManagerID);

            base.OnModelCreating(modelBuilder);
        }
    }
}

Step 4: Add the database connection string in web.config file.
<connectionStrings>
  <add name="EmployeeDBContext"
            connectionString="server=.; database=Sample; integrated security=SSPI;"
            providerName="System.Data.SqlClient" />
</connectionStrings>

Step 5: Add a webform to the project. Drag and drop a GridView control.

Step 6: Copy and paste the following code in the code-behind file.
using System;
using System.Linq;
namespace Demo
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            EmployeeDBContext employeeDBContext = new EmployeeDBContext();
            GridView1.DataSource = employeeDBContext.Employees.Select(emp => new
            {
                EmployeeName = emp.EmployeeName,
                ManagerName = emp.Manager == null ?
                    "Super Boss" : emp.Manager.EmployeeName
            }).ToList();
            GridView1.DataBind();
        }
    }
}

Step 7: If you already have Sample database in SQL Server. Delete it from SQL Server Management Studio.

Step 8: Run the application. Sample database and Employees table must be created at this point. Notice that EmployeeID is marked as primary key and ManagerID is marked as foreign key.
Self referencing association in entity framework with code first

Step 9: Insert test data using the following SQL script
Insert into Employees values ('John', NULL)
Insert into Employees values ('Mark', NULL)
Insert into Employees values ('Steve', NULL)
Insert into Employees values ('Tom', NULL)
Insert into Employees values ('Lara', NULL)
Insert into Employees values ('Simon', NULL)
Insert into Employees values ('David', NULL)
Insert into Employees values ('Ben', NULL)
Insert into Employees values ('Stacy', NULL)
Insert into Employees values ('Sam', NULL)
GO

Update Employees Set ManagerID = 8 Where EmployeeName IN ('Mark', 'Steve', 'Lara')
Update Employees Set ManagerID = 2 Where EmployeeName IN ('Stacy', 'Simon')
Update Employees Set ManagerID = 3 Where EmployeeName IN ('Tom')
Update Employees Set ManagerID = 5 Where EmployeeName IN ('John', 'Sam')
Update Employees Set ManagerID = 4 Where EmployeeName IN ('David')
GO

Step 10: Reload web form and notice that the employee name and their respective manager name is is displayed as expected.
Part 18 - Table Per Hierarchy (TPH) inheritance in entity framework
Suggested Videos
Part 15 - Conditional Mapping in entity framework with code first
Part 16 - Self referencing association in entity framework
Part 17 - Self referencing association in entity framework with code first




In this video, we will discuss implementing Table Per Hierarchy (TPH) inheritance in entity framework with database first approach. In TPH inheritance one database table is used to store data for all of the entity types in the inheritance hierarchy. Let us understand this with an example.




We will be using the following Employees table.
tph example in entity framework

SQL Script to create Employees Table
Create Table Employees
(
     ID int primary key identity,
     FirstName nvarchar(50),
     LastName nvarchar(50),
     Gender nvarchar(50),
     AnuualSalary int,
     HourlyPay int,
     HoursWorked int,
     Discriminator nvarchar(50)
)

Insert into Employees values
('Mark', 'Hastings', 'Male', 60000, NULL, NULL, 'PermanentEmployee')
Insert into Employees values
('Steve', 'Pound', 'Male', NULL, 50, 160, 'ContractEmployee')
Insert into Employees values
('Ben', 'Hoskins', 'Male', NULL, 40, 120, 'ContractEmployee')
Insert into Employees values
('Philip', 'Hastings', 'Male', 45000, NULL, NULL, 'PermanentEmployee')
Insert into Employees values
('Mary', 'Lambeth', 'Female', 30000, NULL, NULL, 'PermanentEmployee')
Insert into Employees values
('Valarie', 'Vikings', 'Female', NULL, 30, 140, 'ContractEmployee')
Insert into Employees values
('John', 'Stanmore', 'Male', 80000, NULL, NULL, 'PermanentEmployee')

In our organization we have 2 types of employees
1. Permanent Employees - AnuualSalary is specific for Permanent Employees
2. Contract Employees - HourlyPay & HoursWorked is specific for Contract Employees

Now if we generate an ADO.NET entity data model based on this Employees table, by default the following Employee entity class is generated.
Table Per Hierarchy example

But from an application perspective we want 3 entity classes
Employee - This should be an abstract class and should contain all the common properties of permanent and contract employees. ID, FirstName, LastName & Gender properties should be present in this class.

PermanentEmployee - This class should inherit from the abstract Employee class and should contain AnnualSalary property

ContractEmployee - This class should inherit from the abstract Employee class and should contain HourlyPay & HoursWorked properties

To achieve this using the Entity Framework designer
1. Right click on the designer surface and select Add - Entity option, and provide the details as shown below and click OK.
Add permanent employee entity

2. Cut AnnualSalary property from Employee entity and paste it in Permanent Employee entity.

3. Right click on the designer surface and select Add - Entity option, and provide the details as shown below and click OK.
Add contract employee entity

4. Cut HourlyPay & HoursWorked properties from Employee entity and paste then in Contract Employee entity. At this point, we should have the following 3 entities.
Table Per Hierarchy inheritance in entity framework

5. Right click on PermanentEmployee and select Table Mapping option, and map PermanentEmployee entity to Employees table. Also notice the conditional mapping, in which we are using Discriminator column to determine when an Employee can be PermanentEmployee.
table mapping permanent employee entity

6. Along the same lines, right click on ContractEmployee and select Table Mapping option, and map ContractEmployee entity to Employees table. Also notice the conditional mapping, in which we are using Discriminator column to determine when an Employee can be ContractEmployee.
table mapping contract employee entity

7. Since we are using Discriminator column in conditional mapping we cannot use it again with property mapping in Employee entity. So delete it from there.

8. Finally right click on Employee entity and select properties. In the properties window set Abstract=true. This should make Employee class an abstract class.

Now, let us see how to query the data. Design a webform as shown below.
tph in entity framework

Here is the HTML for the web form
<div style="font-family: Arial">
    <asp:RadioButtonList ID="RadioButtonList1" runat="server" AutoPostBack="True"
        OnSelectedIndexChanged="RadioButtonList1_SelectedIndexChanged">
        <asp:ListItem Text="Load all Employees" Value="All"></asp:ListItem>
        <asp:ListItem Text="Load Permanent Employees" Value="Permanent"></asp:ListItem>
        <asp:ListItem Text="Load Contract Employees" Value="Contract"></asp:ListItem>
    </asp:RadioButtonList>
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
</div>

Here is the code behind code:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace Demo
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            EmployeeDBContext employeeDBContext = new EmployeeDBContext();

            switch (RadioButtonList1.SelectedValue)
            {
                case "Permanent":
                    GridView1.DataSource = employeeDBContext.Employees
                        .OfType<PermanentEmployee>().ToList();
                    GridView1.DataBind();
                    break;

                case "Contract":
                    GridView1.DataSource = employeeDBContext.Employees
                        .OfType<ContractEmployee>().ToList();
                    GridView1.DataBind();
                    break;

                default:
                    GridView1.DataSource = ConvertEmployeesForDisplay(
                        employeeDBContext.Employees.ToList());
                    GridView1.DataBind();
                    break;
            }
        }

        private DataTable ConvertEmployeesForDisplay(List<Employee> employees)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("FirstName");
            dt.Columns.Add("LastName");
            dt.Columns.Add("Gender");
            dt.Columns.Add("AnuualSalary");
            dt.Columns.Add("HourlyPay");
            dt.Columns.Add("HoursWorked");
            dt.Columns.Add("Type");

            foreach (Employee employee in employees)
            {
                DataRow dr = dt.NewRow();
                dr["ID"] = employee.ID;
                dr["FirstName"] = employee.FirstName;
                dr["LastName"] = employee.LastName;
                dr["Gender"] = employee.Gender;

                if (employee is PermanentEmployee)
                {
                    dr["AnuualSalary"] = ((PermanentEmployee)employee).AnuualSalary;
                    dr["Type"] = "Permanent";
                }
                else
                {
                    dr["HourlyPay"] = ((ContractEmployee)employee).HourlyPay;
                    dr["HoursWorked"] = ((ContractEmployee)employee).HoursWorked;
                    dr["Type"] = "Contract";
                }
                dt.Rows.Add(dr);
            }

            return dt;
        }
    }
}

Run the application and turn on the SQL profiler to inspect the queries that are generated.

When Load All Employees radio button is selected:
SELECT
[Extent1].[Discriminator] AS [Discriminator],
[Extent1].[ID] AS [ID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Gender] AS [Gender],
[Extent1].[AnuualSalary] AS [AnuualSalary],
[Extent1].[HoursWorked] AS [HoursWorked],
[Extent1].[HourlyPay] AS [HourlyPay]
FROM [dbo].[Employees] AS [Extent1]
WHERE [Extent1].[Discriminator] IN (N'PermanentEmployee',N'ContractEmployee')

When Load Permanent Employees radio button is selected:
SELECT
'0X0X' AS [C1],
[Extent1].[ID] AS [ID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Gender] AS [Gender],
[Extent1].[AnuualSalary] AS [AnuualSalary]
FROM [dbo].[Employees] AS [Extent1]
WHERE [Extent1].[Discriminator] = N'PermanentEmployee'

When Load Contract Employees radio button is selected:
SELECT
'0X0X' AS [C1],
[Extent1].[ID] AS [ID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Gender] AS [Gender],
[Extent1].[HoursWorked] AS [HoursWorked],
[Extent1].[HourlyPay] AS [HourlyPay]
FROM [dbo].[Employees] AS [Extent1]
WHERE [Extent1].[Discriminator] = N'ContractEmployee'
Part 19 - Table Per Hierarchy (TPH) inheritance in entity framework with code first
Suggested Videos
Part 16 - Self referencing association in entity framework
Part 17 - Self referencing association in entity framework with code first
Part 18 - Table Per Hierarchy (TPH) inheritance in entity framework




In this video, we will discuss implementing Table Per Hierarchy (TPH) inheritance in entity framework with code first approach. This is continuation to Part 18, where we discussed implementing Table Per Hierarchy inheritance with database first approach. Please watch Part 18 before proceeding.




In TPH inheritance one database table is used to store data for all of the entity types in the inheritance hierarchy.
Table Per Hierarchy inheritance code first

Step 1: Create a new empty asp.net web application project. Name it Demo. Install entity framework if it's not already installed.

Step 2: Add a class file to the project. Name it Employee.cs. Copy and paste the following code.
using System.ComponentModel.DataAnnotations.Schema;
namespace Demo
{
    public abstract class Employee
    {
        [Column(Order = 1)]
        public int ID { get; set; }
        [Column(Order = 2)]
        public string FirstName { get; set; }
        [Column(Order = 3)]
        public string LastName { get; set; }
        [Column(Order = 4)]
        public string Gender { get; set; }
    }
}

Step 3: Add a class file to the project. Name it PermanentEmployee.cs. Copy and paste the following code.
using System.ComponentModel.DataAnnotations.Schema;
namespace Demo
{
    public class PermanentEmployee : Employee
    {
        [Column(Order = 5)]
        public int AnnualSalary { get; set; }
    }
}

Step 4: Add a class file to the project. Name it ContractEmployee.cs. Copy and paste the following code.
using System.ComponentModel.DataAnnotations.Schema;
namespace Demo
{
    public class ContractEmployee : Employee
    {
        [Column(Order = 6)]
        public int HoursWorked { get; set; }
        [Column(Order = 7)]
        public int HourlyPay { get; set; }
    }
}

Step 5: Add a class file to the project. Name it EmployeeDBContext.cs. Copy and paste the following code.
using System.Data.Entity;
namespace Demo
{
    public class EmployeeDBContext : DbContext
    {
        public DbSet<Employee> Employees { get; set; }
    }
}

Step 6: Add the database connection string in web.config file.
<connectionStrings>
  <add name="EmployeeDBContext"
        connectionString="server=.; database=Sample; integrated security=SSPI;"
        providerName="System.Data.SqlClient" />
</connectionStrings>

Step 7: Add a webform to the project. Copy and paste the following HTML.
<div style="font-family: Arial">
    <asp:RadioButtonList ID="RadioButtonList1" runat="server" AutoPostBack="True"
        onselectedindexchanged="RadioButtonList1_SelectedIndexChanged">
        <asp:ListItem Text="Load all Employees" Value="All"></asp:ListItem>
        <asp:ListItem Text="Load Permanent Employees" Value="Permanent"></asp:ListItem>
        <asp:ListItem Text="Load Contract Employees" Value="Contract"></asp:ListItem>
    </asp:RadioButtonList>
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
    <br />
    <asp:Button ID="btnAddPermanentEmployee" runat="server"
        Text="Add Permanent Employee" onclick="btnAddPermanentEmployee_Click" />
    <br />
    <br />
    <asp:Button ID="btnAddContractEmployee" runat="server"
        Text="Add Contract Employee" onclick="btnAddContractEmployee_Click" />
</div>

Step 8: Copy and paste the following code in the code-behind file.
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace Demo
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            EmployeeDBContext employeeDBContext = new EmployeeDBContext();

            switch (RadioButtonList1.SelectedValue)
            {
                case "Permanent":
                    GridView1.DataSource = employeeDBContext.Employees.OfType<PermanentEmployee>().ToList();
                    GridView1.DataBind();
                    break;

                case "Contract":
                    GridView1.DataSource = employeeDBContext.Employees.OfType<ContractEmployee>().ToList();
                    GridView1.DataBind();
                    break;

                default:
                    GridView1.DataSource = ConvertEmployeesForDisplay(employeeDBContext.Employees.ToList());
                    GridView1.DataBind();
                    break;
            }
        }

        private DataTable ConvertEmployeesForDisplay(List<Employee> employees)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("FirstName");
            dt.Columns.Add("LastName");
            dt.Columns.Add("Gender");
            dt.Columns.Add("AnuualSalary");
            dt.Columns.Add("HourlyPay");
            dt.Columns.Add("HoursWorked");
            dt.Columns.Add("Type");

            foreach (Employee employee in employees)
            {
                DataRow dr = dt.NewRow();
                dr["ID"] = employee.ID;
                dr["FirstName"] = employee.FirstName;
                dr["LastName"] = employee.LastName;
                dr["Gender"] = employee.Gender;

                if (employee is PermanentEmployee)
                {
                    dr["AnuualSalary"] = ((PermanentEmployee)employee).AnnualSalary;
                    dr["Type"] = "Permanent";
                }
                else
                {
                    dr["HourlyPay"] = ((ContractEmployee)employee).HourlyPay;
                    dr["HoursWorked"] = ((ContractEmployee)employee).HoursWorked;
                    dr["Type"] = "Contract";
                }
                dt.Rows.Add(dr);
            }

            return dt;
        }

        protected void btnAddPermanentEmployee_Click(object sender, EventArgs e)
        {
            PermanentEmployee permanentEmployee = new PermanentEmployee
            {
                FirstName = "Mike",
                LastName = "Brown",
                Gender = "Male",
                AnnualSalary = 70000,
            };

            EmployeeDBContext employeeDBContext = new EmployeeDBContext();
            employeeDBContext.Employees.Add(permanentEmployee);
            employeeDBContext.SaveChanges();
        }

        protected void btnAddContractEmployee_Click(object sender, EventArgs e)
        {
            ContractEmployee contractEmployee = new ContractEmployee
            {
                FirstName = "Stacy",
                LastName = "Josh",
                Gender = "Female",
                HourlyPay = 50,
                HoursWorked = 120
            };

            EmployeeDBContext employeeDBContext = new EmployeeDBContext();
            employeeDBContext.Employees.Add(contractEmployee);
            employeeDBContext.SaveChanges();
        }
    }
}

Step 7: If you already have Sample database in SQL Server. Delete it from SQL Server Management Studio.

Step 8: Run the application. Sample database and Employees table must be created at this point.

Step 9: Insert test data using the following SQL script
Insert into Employees values
('Mark', 'Hastings', 'Male', 60000, NULL, NULL, 'PermanentEmployee')
Insert into Employees values
('Steve', 'Pound', 'Male', NULL, 50, 160, 'ContractEmployee')
Insert into Employees values
('Ben', 'Hoskins', 'Male', NULL, 40, 120, 'ContractEmployee')
Insert into Employees values
('Philip', 'Hastings', 'Male', 45000, NULL, NULL, 'PermanentEmployee')
Insert into Employees values
('Mary', 'Lambeth', 'Female', 30000, NULL, NULL, 'PermanentEmployee')
Insert into Employees values
('Valarie', 'Vikings', 'Female', NULL, 30, 140, 'ContractEmployee')
Insert into Employees values
('John', 'Stanmore', 'Male', 80000, NULL, NULL, 'PermanentEmployee')

Step 10: Reload the webform. Open SQL Profiler. Select the different radio buttons to load contract, permanent & all employees and notice the queries generated by the entity framework. Also click on the following buttons.
a) Add Permanent Employee
b) Add Contract Employee

Notice that the Discriminator column is populated automatically based on the type of Employee being inserted.
Part 20 - Table Per Type (TPT) inheritance in entity framework
Suggested Videos
Part 17 - Self referencing association in entity framework with code first
Part 18 - Table Per Hierarchy (TPH) inheritance in entity framework
Part 19 - Table Per Hierarchy (TPH) inheritance in entity framework with code first




In this video we will discuss implementing Table Per Type (TPT) inheritance in entity framework with database first approach. In TPT inheritance one database table per type is used to store data for the respective entity types in the inheritance hierarchy. This means there is no problem of de-normalized tables.




In Parts 18 and 19 of Entity Framework tutorial we discussed how inheritance hierarchy can be represented using Table Per Hierarchy (TPH). With TPH one database table is used to store data of all the entity types in the inheritance hierarchy. The downside of this is that we have a denormalized table and some columns will have NULL values depending on the type of the derived object being saved to the database table.

Example: Consider the following inheritance hierarchy
tph example

The data of all the entity types in the above inheritance hierarchy is stored in the following Employees table
tph example in entity framework

The problem with TPH is that when we store a PermanentEmployee object to this table, HoursWorked & HourlyPay columns will be left NULL. Along the same lines, when we store a ContractEmployee object, AnnualSalary column will be NULL. With Table Per Type inheritance we don't have this problem.

We will be using the followin 3 tables to implement Table Per Type (TPT) inheritance. Notice that EmployeeID is the primary key of Employees table. In PermanentEmployees & ContractEmployees, EmployeeID is the foreign key.
table per type inheritance example

SQL Script to create the above 3 tables
Create Table Employees
(
     EmployeeID int primary key,
     FirstName nvarchar(50),
     LastName nvarchar(50),
     Gender nvarchar(50),
)
GO

Create Table PermanentEmployees
(
     EmployeeID int foreign key references
     Employees(EmployeeID) not null,
     AnnualSalary int
)
GO

Create Table ContractEmployees
(
     EmployeeID int foreign key references
     Employees(EmployeeID) not null,
     HourlyPay int,
     HoursWorked int
)
GO

-- Employees Table Insert
Insert into Employees values (1, 'Mark', 'Hastings', 'Male')
Insert into Employees values (2, 'Steve', 'Pound', 'Male')
Insert into Employees values (3, 'Ben', 'Hoskins', 'Male')
Insert into Employees values (4, 'Philip', 'Hastings', 'Male')
Insert into Employees values (5, 'Mary', 'Lambeth', 'Female')
Insert into Employees values (6, 'Valarie', 'Vikings', 'Female')
Insert into Employees values (7, 'John', 'Stanmore', 'Male')

-- PermanentEmployees Table Insert
Insert into PermanentEmployees values (1, 60000)
Insert into PermanentEmployees values (3, 45000)
Insert into PermanentEmployees values (4, 30000)
Insert into PermanentEmployees values (7, 80000)

-- ContractEmployees Table Insert
Insert into ContractEmployees values (2, 50, 160)
Insert into ContractEmployees values (5, 40, 120)
Insert into ContractEmployees values (6, 30, 140)

Now if we generate an ADO.NET entity data model based on the above 3 tables, the following 3 entities are generated with an association instead of inheritance relation between the entities.
table per type example

Delete the association between Employee and PermanentEmployee entities. This will also automatically delete the PermanentEmployee navigation property from Employee entity and Employee navigation property from PermanentEmployee entity.

Along the same lines, delete the association between Employee and ContractEmployee entities. This will also automatically delete the ContractEmployee navigation property from Employee entity and Employee navigation property from ContractEmployee entity.

Now, add the inheritance relationship between Employee & ContractEmployee entities
1. Right click on the designer surface and select Add - Inheritance option
2. Select Employee as the Base Entity and ContractEmployee as the Derived entity
adding inheritance relation between employee and contract employee entities

Along the same line, add the inheritance relationship between Employee & PermanentEmployee entities
1. Right click on the designer surface and select Add - Inheritance option
2. Select Employee as the Base Entity and PermanentEmployee as the Derived entity
adding inheritance relation between entities

Now delete the EmployeeID property from both PermanentEmployee & ContractEmployee entities. For both of these entities EmployeeID property will be available from the base Employee entity thru inheritance.

At this point, the entity model should look as shown below.
Table Per Type inheritance in entity framework

Now, let us see how to query the data. Design a webform as shown below.
tph in entity framework

Here is the HTML for the web form
<div style="font-family:Arial">
<asp:RadioButtonList ID="RadioButtonList1" runat="server"
    AutoPostBack="True"
    onselectedindexchanged="RadioButtonList1_SelectedIndexChanged">
    <asp:ListItem Text="All Employees" Value="All"></asp:ListItem>
    <asp:ListItem Text="Load Permanent Employees" Value="Permanent">
    </asp:ListItem>
    <asp:ListItem Text="Load Contract Employees" Value="Contract">
    </asp:ListItem>
</asp:RadioButtonList>
<br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>

Copy and paste the following code in the code-behind file
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace Demo
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            EmployeeDBContext employeeDBContext = new EmployeeDBContext();

            switch (RadioButtonList1.SelectedValue)
            {
                case "Permanent":
                    GridView1.DataSource = employeeDBContext.Employees
                        .OfType<PermanentEmployee>().ToList();
                    GridView1.DataBind();
                    break;

                case "Contract":
                    GridView1.DataSource = employeeDBContext.Employees
                        .OfType<ContractEmployee>().ToList();
                    GridView1.DataBind();
                    break;

                default:
                    GridView1.DataSource =
                        ConvertEmployeesForDisplay(employeeDBContext.Employees.ToList());
                    GridView1.DataBind();
                    break;
            }
        }

        private DataTable ConvertEmployeesForDisplay(List<Employee> employees)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("FirstName");
            dt.Columns.Add("LastName");
            dt.Columns.Add("Gender");
            dt.Columns.Add("AnuualSalary");
            dt.Columns.Add("HourlyPay");
            dt.Columns.Add("HoursWorked");
            dt.Columns.Add("Type");

            foreach (Employee employee in employees)
            {
                DataRow dr = dt.NewRow();
                dr["ID"] = employee.EmployeeID;
                dr["FirstName"] = employee.FirstName;
                dr["LastName"] = employee.LastName;
                dr["Gender"] = employee.Gender;

                if (employee is PermanentEmployee)
                {
                    dr["AnuualSalary"] = ((PermanentEmployee)employee).AnnualSalary;
                    dr["Type"] = "Permanent";
                }
                else
                {
                    dr["HourlyPay"] = ((ContractEmployee)employee).HourlyPay;
                    dr["HoursWorked"] = ((ContractEmployee)employee).HoursWorked;
                    dr["Type"] = "Contract";
                }
                dt.Rows.Add(dr);
            }

            return dt;
        }
    }
}

Run the application and turn on the SQL profiler to inspect the queries that are generated
Part 21 - Table Per Type (TPT) inheritance in entity framework code first
Suggested Videos
Part 18 - Table Per Hierarchy (TPH) inheritance in entity framework
Part 19 - Table Per Hierarchy (TPH) inheritance in entity framework with code first
Part 20 - Table Per Type (TPT) inheritance in entity framework




In this video, we will discuss implementing Table Per Type (TPT) inheritance in entity framework with code first approach. This is continuation to Part 20, where we discussed implementing Table Per Type inheritance with database first approach. Please watch Part 20 before proceeding.




In TPT inheritance one database table per type is used to store data for the respective entity types in the inheritance hierarchy.

Step 1: Create a new empty asp.net web application project. Name it Demo. Install entity framework if it's not already installed.

Step 2: Add a class file to the project. Name it Employee.cs. Copy and paste the following code.
using System.ComponentModel.DataAnnotations.Schema;
namespace Demo
{
    [Table("Employees")]
    public class Employee
    {
        public int EmployeeID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Gender { get; set; }
    }

}

Step 3: Add a class file to the project. Name it PermanentEmployee.cs. Copy and paste the following code.
using System.ComponentModel.DataAnnotations.Schema;
namespace Demo
{
    [Table("PermanentEmployees")]
    public class PermanentEmployee : Employee
    {
        public int AnnualSalary { get; set; }
    }
}

Step 4: Add a class file to the project. Name it ContractEmployee.cs. Copy and paste the following code.
using System.ComponentModel.DataAnnotations.Schema;
namespace Demo
{
    [Table("ContractEmployees")]
    public class ContractEmployee : Employee
    {
        public int HoursWorked { get; set; }
        public int HourlyPay { get; set; }
    }
}

Step 5: Add a class file to the project. Name it EmployeeDBContext.cs. Copy and paste the following code.
using System.Data.Entity;
namespace Demo
{
    public class EmployeeDBContext : DbContext
    {
        public DbSet<Employee> Employees { get; set; }
    }
}

Step 6: Add the database connection string in web.config file.
<connectionStrings>
  <add name="EmployeeDBContext"
            connectionString="server=.; database=Sample; integrated security=SSPI;"
            providerName="System.Data.SqlClient" />
</connectionStrings>

Step 7: Add a webform to the project. Copy and paste the following HTML.
<div style="font-family: Arial">
    <asp:RadioButtonList ID="RadioButtonList1" runat="server" AutoPostBack="True"
        OnSelectedIndexChanged="RadioButtonList1_SelectedIndexChanged">
        <asp:ListItem Text="Load all Employees" Value="All"></asp:ListItem>
        <asp:ListItem Text="Load Permanent Employees" Value="Permanent"></asp:ListItem>
        <asp:ListItem Text="Load Contract Employees" Value="Contract"></asp:ListItem>
    </asp:RadioButtonList>
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
    <br />
    <asp:Button ID="btnAddPermanentEmployee" runat="server"
        Text="Add Permanent Employee"
        OnClick="btnAddPermanentEmployee_Click" />
    <br />
    <br />
    <asp:Button ID="btnAddContractEmployee" runat="server" Text="Add Contract Employee"
        OnClick="btnAddContractEmployee_Click" />
</div>

Step 8: Copy and paste the following code in the code-behind file.
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace Demo
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            EmployeeDBContext employeeDBContext = new EmployeeDBContext();

            switch (RadioButtonList1.SelectedValue)
            {
                case "Permanent":
                    GridView1.DataSource = employeeDBContext.Employees
                             .OfType<PermanentEmployee>().ToList();
                    GridView1.DataBind();
                    break;

                case "Contract":
                    GridView1.DataSource = employeeDBContext.Employees
                             .OfType<ContractEmployee>().ToList();
                    GridView1.DataBind();
                    break;

                default:
                    GridView1.DataSource = ConvertEmployeesForDisplay(employeeDBContext.Employees.ToList());
                    GridView1.DataBind();
                    break;
            }
        }

        private DataTable ConvertEmployeesForDisplay(List<Employee> employees)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("FirstName");
            dt.Columns.Add("LastName");
            dt.Columns.Add("Gender");
            dt.Columns.Add("AnuualSalary");
            dt.Columns.Add("HourlyPay");
            dt.Columns.Add("HoursWorked");
            dt.Columns.Add("Type");

            foreach (Employee employee in employees)
            {
                DataRow dr = dt.NewRow();
                dr["ID"] = employee.EmployeeID;
                dr["FirstName"] = employee.FirstName;
                dr["LastName"] = employee.LastName;
                dr["Gender"] = employee.Gender;

                if (employee is PermanentEmployee)
                {
                    dr["AnuualSalary"] = ((PermanentEmployee)employee).AnnualSalary;
                    dr["Type"] = "Permanent";
                }
                else
                {
                    dr["HourlyPay"] = ((ContractEmployee)employee).HourlyPay;
                    dr["HoursWorked"] = ((ContractEmployee)employee).HoursWorked;
                    dr["Type"] = "Contract";
                }
                dt.Rows.Add(dr);
            }

            return dt;
        }

        protected void btnAddPermanentEmployee_Click(object sender, EventArgs e)
        {
            PermanentEmployee permanentEmployee = new PermanentEmployee
            {
                FirstName = "Mike",
                LastName = "Brown",
                Gender = "Male",
                AnnualSalary = 70000,
            };

            EmployeeDBContext employeeDBContext = new EmployeeDBContext();
            employeeDBContext.Employees.Add(permanentEmployee);
            employeeDBContext.SaveChanges();
        }

        protected void btnAddContractEmployee_Click(object sender, EventArgs e)
        {
            ContractEmployee contractEmployee = new ContractEmployee
            {
                FirstName = "Stacy",
                LastName = "Josh",
                Gender = "Female",
                HourlyPay = 50,
                HoursWorked = 120
            };

            EmployeeDBContext employeeDBContext = new EmployeeDBContext();
            employeeDBContext.Employees.Add(contractEmployee);
            employeeDBContext.SaveChanges();
        }
    }
}

Step 7: If you already have Sample database in SQL Server. Delete it from SQL Server Management Studio.

Step 8: Run the application. Sample database and the required tables (Employees, PermanentEmployees & ContractEmployees) must be created at this point.

Step 9: Insert test data using the following SQL script
-- Employees Table Insert
Insert into Employees values ('Mark', 'Hastings', 'Male')
Insert into Employees values ('Steve', 'Pound', 'Male')
Insert into Employees values ('Ben', 'Hoskins', 'Male')
Insert into Employees values ('Philip', 'Hastings', 'Male')
Insert into Employees values ('Mary', 'Lambeth', 'Female')
Insert into Employees values ('Valarie', 'Vikings', 'Female')
Insert into Employees values ('John', 'Stanmore', 'Male')
GO

-- PermanentEmployees Table Insert
Insert into PermanentEmployees values (1, 60000)
Insert into PermanentEmployees values (3, 45000)
Insert into PermanentEmployees values (4, 30000)
Insert into PermanentEmployees values (7, 80000)
GO

-- ContractEmployees Table Insert
Insert into ContractEmployees values (2, 50, 160)
Insert into ContractEmployees values (5, 40, 120)
Insert into ContractEmployees values (6, 30, 140)
GO

Step 10: Reload the webform. Open SQL Profiler. Select the different radio buttons to load contract, permanent & all employees and notice the queries generated by the entity framework. Also click on the following buttons.
a) Add Permanent Employee
b) Add Contract Employee

Notice that entity framework automatically inserts records into the correct underlying tables based on the type of Employee (PermanentEmployee or ContractEmployee) being inserted.

Note: You can also use Fluent API to do the table mapping by overriding OnModelCreating() as shown below.
public class EmployeeDBContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<ContractEmployee>().ToTable("ContractEmployees");
        modelBuilder.Entity<PermanentEmployee>().ToTable("PermanentEmployees");

        base.OnModelCreating(modelBuilder);
    }
}
Part 22 - Many to many relationship in entity framework
Suggested Videos
Part 19 - Table Per Hierarchy (TPH) inheritance in entity framework with code first
Part 20 - Table Per Type (TPT) inheritance in entity framework
Part 21 - Table Per Type (TPT) inheritance in entity framework code first




In this video we will discuss many-to-many relationship in entity framework with database first approach. We will be using the following 3 tables in this demo. There is a Many-to-Many relationship between Courses and Students. A student can enroll into many courses and a single course can have many students enrolled. The data is stored in the StudentCourses bridge table




Many to many relationship in entity framework

SQL Script to create the above 3 tables
Create Table Courses
(
     CourseID int identity primary key,
     CourseName nvarchar(50)
)
GO

Create Table Students
(
     StudentID int identity primary key,
     StudentName nvarchar(50)
)
GO

Create Table StudentCourses
(
     StudentID int not null foreign key references Students(StudentID),
     CourseID int not null foreign key references Courses(CourseID)
     primary key (StudentID, CourseID)
)
GO

Insert into Courses values ('C#')
Insert into Courses values ('ASP.NET')
Insert into Courses values ('SQL Server')
Insert into Courses values ('WCF')
GO

Insert into Students values ('Mike')
Insert into Students values ('John')
GO

Insert into StudentCourses values (1, 1)
Insert into StudentCourses values (1, 2)
Insert into StudentCourses values (2, 1)
Insert into StudentCourses values (2, 2)
Insert into StudentCourses values (2, 3)
GO

Now if we generate an ADO.NET entity data model based on the above 3 tables, only 2 entities (Student & Course) are generated with a Many-to-Many association between them. Notice that an entity for the bridge table (StudentCourses) is not generated in the entity model. Also, notice that we have navigation properties to navigate from Course to Students and from Student to Courses.
Many to many association in entity framework

At this point, right click on Many-to-Many association and select Table Mapping option. In the Mapping Details window notice that, the StudentCourses Many-to-Many association is mapped to StudentCourses database table.
mapping many to many association in entity framework

Now, let us see how to query the data. We want to display all the students names and the courses they have opted into.
many to many association example in entity framework

Drag and drop a GridView control on the webform. Copy and paste the following code in the code-behind file.
protected void Page_Load(object sender, EventArgs e)
{
    EmployeeDBContext employeeDBContext = new EmployeeDBContext();

    GridView1.DataSource = from student in employeeDBContext.Students
                            from course in student.Courses
                            select new
                            {
                                StudentName = student.StudentName,
                                CourseName = course.CourseName
                            };
    GridView1.DataBind();
}

Turn on SQL Profiler and load the webform. Notice that the generated SQL Query joins all the 3 tables (Students, Courses & StudentCourses)
SELECT
[Extent1].[StudentID] AS [StudentID],
[Extent1].[StudentName] AS [StudentName],
[Join1].[CourseName] AS [CourseName]
FROM  [dbo].[Students] AS [Extent1]
INNER JOIN  (SELECT [Extent2].[StudentID] AS [StudentID],
[Extent3].[CourseName] AS [CourseName]
FROM  [dbo].[StudentCourses] AS [Extent2]
INNER JOIN [dbo].[Courses] AS [Extent3]
ON [Extent3].[CourseID] = [Extent2].[CourseID] )
AS [Join1] ON [Extent1].[StudentID] = [Join1].[StudentID]

Modifying data:
Assign Mike to WCF course
Remove John from SQL Server course

To achieve this,
1. Drag and Drop 2 button controls on the web form
2. Change text on the
     First button to Assign WCF Course to Mike
     Second button to Remove John from SQL Server Course
3. Copy and paste the following code in the respective click event handler methods
protected void Button1_Click(object sender, EventArgs e)
{
    EmployeeDBContext employeeDBContext = new EmployeeDBContext();
    Course WCFCourse = employeeDBContext.Courses
        .FirstOrDefault(x => x.CourseID == 4);

    employeeDBContext.Students.FirstOrDefault(x => x.StudentID == 1)
        .Courses.Add(WCFCourse);
    employeeDBContext.SaveChanges();
}

protected void Button2_Click(object sender, EventArgs e)
{
    EmployeeDBContext employeeDBContext = new EmployeeDBContext();
    Course SQLServerCourse = employeeDBContext.Courses
        .FirstOrDefault(x => x.CourseID == 3);

    employeeDBContext.Students.FirstOrDefault(x => x.StudentID == 2)
        .Courses.Remove(SQLServerCourse);
    employeeDBContext.SaveChanges();
}

Turn on SQL Profiler and load the webform. Click on the buttons and notice the queries generated.

Query to assign WCF course to Mike
exec sp_executesql N'insert [dbo].[StudentCourses]([StudentID], [CourseID])
values (@0, @1)',N'@0 int,@1 int',@0=1,@1=4

Query to remove John from SQL Server Course
exec sp_executesql N'delete [dbo].[StudentCourses] where
(([StudentID] = @0) and ([CourseID] = @1))',N'@0 int,@1 int',@0=2,@1=3
Part 23 - Many to many relationship in entity framework code first
Suggested Videos
Part 20 - Table Per Type (TPT) inheritance in entity framework
Part 21 - Table Per Type (TPT) inheritance in entity framework code first
Part 22 - Many to many relationship in entity framework




In this video we will discuss many-to-many relationship in entity framework with code first approach. This is continuation to Part 22, where we discussed Many to many relationship with database first approach. Please watch Part 22 before proceeding.




Based on Course & Student classes, entity framework should generate the required database tables with many-to-many relationship
Many to many relationship entity framework code first

Step 1: Create a new empty asp.net web application project. Name it Demo. Install entity framework if it's not already installed.

Step 2: Add a class file to the project. Name it Course.cs. Copy and paste the following code.
using System.Collections.Generic;
namespace Demo
{
    public class Course
    {
        public int CourseID { get; set; }
        public string CourseName { get; set; }
        public IList<Student> Students { get; set; }
    }
}

Step 3: Add a class file to the project. Name it Student.cs. Copy and paste the following code.
using System.Collections.Generic;
namespace Demo
{
    public class Student
    {
        public int StudentID { get; set; }
        public string StudentName { get; set; }
        public IList<Course> Courses { get; set; }
    }
}

Step 4: Add a class file to the project. Name it EmployeeDBContext.cs. Copy and paste the following code.
using System.Data.Entity;
namespace Demo
{
    public class EmployeeDBContext : DbContext
    {
        public DbSet<Course> Courses { get; set; }
        public DbSet<Student> Students { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Student>()
            .HasMany(t => t.Courses)
            .WithMany(t => t.Students)
            .Map(m =>
            {
                m.ToTable("StudentCourses");
                m.MapLeftKey("StudentID");
                m.MapRightKey("CourseID");
            });

            base.OnModelCreating(modelBuilder);
        }
    }
}

Step 5: Add the database connection string in web.config file.
<connectionStrings>
  <add name="EmployeeDBContext"
            connectionString="server=.; database=Sample; integrated security=SSPI;"
            providerName="System.Data.SqlClient" />
</connectionStrings>

Step 6: Add a webform to the project. Copy and paste the following HTML.
<div style="font-family: Arial">
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
    <br />
    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click"
        Text="Assign WCF Course to Mike"/>
    <br />
    <br />
    <asp:Button ID="Button2" runat="server" OnClick="Button2_Click"
        Text="Remove John from SQL Server Course"/>
</div>

Step 7: Copy and paste the following code in the code-behind file.
using System;
using System.Linq;

namespace Demo
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            EmployeeDBContext employeeDBContext = new EmployeeDBContext();

            GridView1.DataSource = (from student in employeeDBContext.Students
                                    from c in student.Courses
                                    select new
                                    {
                                        StudentName = student.StudentName,
                                        CourseName = c.CourseName
                                    }).ToList();

            GridView1.DataBind();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            EmployeeDBContext employeeDBContext = new EmployeeDBContext();

            Course WCFCourse = employeeDBContext.Courses
                .FirstOrDefault(x => x.CourseID == 4);

            employeeDBContext.Students.Include("Courses")
                .FirstOrDefault(x => x.StudentID == 1).Courses.Add(WCFCourse);
            employeeDBContext.SaveChanges();
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            EmployeeDBContext employeeDBContext = new EmployeeDBContext();

            Course SQLServerCourse = employeeDBContext.Courses
                .FirstOrDefault(x => x.CourseID == 3);

            employeeDBContext.Students.Include("Courses")
                .FirstOrDefault(x => x.StudentID == 2).Courses.Remove(SQLServerCourse);
            employeeDBContext.SaveChanges();
        }
    }
}

Step 8: If you already have Sample database in SQL Server. Delete it from SQL Server Management Studio.

Step 9: Run the application. Sample database and the required tables (Courses, Students & StudentCourses) must be created at this point.

Step 10: Insert test data using the following SQL script
-- Insert into Courses Table
Insert into Courses values ('C#')
Insert into Courses values ('ASP.NET')
Insert into Courses values ('SQL Server')
Insert into Courses values ('WCF')
GO

-- Insert into Students Table
Insert into Students values ('Mike')
Insert into Students values ('John')
GO

-- Insert into StudentCourses Table
Insert into StudentCourses values (1, 1)
Insert into StudentCourses values (1, 2)
Insert into StudentCourses values (2, 1)
Insert into StudentCourses values (2, 2)
Insert into StudentCourses values (2, 3)
GO

Step 11: Reload the webform. Notice that the data is displayed as expected. Now, click on the following buttons, and notice that StudentCourses table is updated as expected.
a) Assign WCF Course to Mike
b) Remove John from SQL Server Course
Part 24 - Entity for BridgeTable in many to many relationship
Suggested Videos
Part 21 - Table Per Type (TPT) inheritance in entity framework code first
Part 22 - Many to many relationship in entity framework
Part 23 - Many to many relationship in entity framework code first




In this video we will discuss a scenario in which Entity framework generates an Entity for the many-to-many relationship bridge table.




In Part 22 we used the following 3 tables. Notice that the Bridge Table (StudentCourses) does not have any additional columns except for the foreign keys (StudentID & CourseID). When an entity model was created based on these 3 tables, an entity for the bridge table is not created.
many to many relationship in entity framework

Let us now, modify the Bridge Table (StudentCourses) to include EnrolledDate column.
Entity for BridgeTable in many to many relationship

SQL Script to create all the 3 tables (Courses, Students & StudentCourses)
Create Table Courses
(
     CourseID int identity primary key,
     CourseName nvarchar(50)
)
GO

Create Table Students
(
     StudentID int identity primary key,
     StudentName nvarchar(50)
)
GO

Create Table StudentCourses
(
     StudentID int not null foreign key references Students(StudentID),
     CourseID int not null foreign key references Courses(CourseID),
     EnrolledDate DateTime,
     primary key (StudentID, CourseID)
)
GO

Insert into Courses values ('C#')
Insert into Courses values ('ASP.NET')
Insert into Courses values ('SQL Server')
Insert into Courses values ('WCF')
GO

Insert into Students values ('Mike')
Insert into Students values ('John')
GO

Insert into StudentCourses values (1, 1, Getdate())
Insert into StudentCourses values (1, 2, Getdate())
Insert into StudentCourses values (2, 1, Getdate())
Insert into StudentCourses values (2, 2, Getdate())
Insert into StudentCourses values (2, 3, Getdate())
GO

Now if we generate an ADO.NET entity data model based on the above 3 tables, notice that 3 entities (Student, Course & StudentCourse) are generated. An entity (StudentCourse) for the bridge table (StudentCourses) is also generated.
a) Student entity has One-Many relationship with StudentCourse Entity
b) Course entity has One-Many relationship with StudentCourse Entity

Entity Framework interview question : Explain when an entity will and will not be created by the entity framework for the bridge table in a many-to-many relationship?
An entity for the bridge table is NOT created when the bridge table has only the foreign keys. On the other if the bridge table has any other columns apart from the foreign key columns then a bridge table is created.

Querying data: We want to display students and courses data in a GridView as shown below.
displaying many to many relationship data

To achieve this
1. Drag and drop a GridView & 2 Button controls on the webform

2. Change the Text on Button1 control to "Assign WCF Course to Mike" and double click on the button control to generate the click event handler

3. Change the Text on Button2 control to "Remove John from SQL Server Course" and double click on the button control to generate the click event handler

4. At this point the webform design should look as shown below
querying data in many to many relationship

Copy and paste the following code in the code-behind file
using System;
using System.Linq;
namespace Demo
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            EmployeeDBContext employeeDBContext = new EmployeeDBContext();

            GridView1.DataSource = (from student in employeeDBContext.Students
                                    from studentCourse in student.StudentCourses
                                    select new
                                    {
                                        StudentName = student.StudentName,
                                        CourseName = studentCourse.Course.CourseName,
                                        EnrolledDate = studentCourse.EnrolledDate
                                    }).ToList();

            // The above query can also be written as shown below
            //GridView1.DataSource = (from course in employeeDBContext.Courses
            //                        from studentCourse in course.StudentCourses
            //                        select new
            //                        {
            //                            StudentName = studentCourse.Student.StudentName,
            //                            CourseName = course.CourseName,
            //                            EnrolledDate = studentCourse.EnrolledDate
            //                        }).ToList();

            GridView1.DataBind();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            EmployeeDBContext employeeDBContext = new EmployeeDBContext();
            employeeDBContext.StudentCourses.AddObject
                (new StudentCourse { StudentID = 1, CourseID = 4,
                                                       EnrolledDate = DateTime.Now });
            employeeDBContext.SaveChanges();
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            EmployeeDBContext employeeDBContext = new EmployeeDBContext();
            StudentCourse studentCourseToRemove = employeeDBContext.StudentCourses
                .FirstOrDefault(x => x.StudentID == 2 && x.CourseID == 3);
            employeeDBContext.StudentCourses.DeleteObject(studentCourseToRemove);
            employeeDBContext.SaveChanges();
        }
    }
}

Run the application. Notice that the data is displayed as expected. Now, click on the following buttons, and notice that StudentCourses table is updated as expected.
a) Assign WCF Course to Mike
b) Remove John from SQL Server Course
Part 25 - Entity for BridgeTable in many to many relationship code first
Suggested Videos
Part 22 - Many to many relationship in entity framework
Part 23 - Many to many relationship in entity framework code first
Part 24 - Entity for BridgeTable in many to many relationship




In this video we will discuss creating an Entity for the bridge table in a many-to-many relationship with code first. This is continuation to Part 24. Please watch Part 24 before proceeding.




We want the entity framework to create the following tables
creating an Entity for the bridge table in a many-to-many relationship with code first

1. Courses - CourseID should be the Primary Key
2. Students - StudentID should be the Primary Key
3. StudentCourses - Composite primary key consisting of CourseID & StudentID columns. CourseID should also be the foreign key referencing CourseID column in Courses table. StudentID should also be the foreign key referencing StudentID column in Students table.

To achieve this
Step 1: Create a new empty asp.net web application project. Name it Demo. Install entity framework if it's not already installed.

Step 2: Add a class file to the project. Name it Course.cs. Copy and paste the following code.
using System.Collections.Generic;
namespace Demo
{
    public class Course
    {
        public int CourseID { get; set; }
        public string CourseName { get; set; }
        public IList<StudentCourse> StudentCourses { get; set; }
    }
}

Step 3: Add a class file to the project. Name it Student.cs. Copy and paste the following code.
using System.Collections.Generic;
namespace Demo
{
    public class Student
    {
        public int StudentID { get; set; }
        public string StudentName { get; set; }
        public IList<StudentCourse> StudentCourses { get; set; }
    }
}

Step 4: Add a class file to the project. Name it StudentCourse.cs. Copy and paste the following code.
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace Demo
{
    public class StudentCourse
    {
        public Course Course { get; set; }

        public Student Student { get; set; }

        [Key, Column(Order = 1)]
        public int StudentID { get; set; }

        [Key, Column(Order = 2)]
        public int CourseID { get; set; }

        public DateTime EnrolledDate { get; set; }
    }
}

Step 5: Add a class file to the project. Name it EmployeeDBContext.cs. Copy and paste the following code.
using System.Data.Entity;
namespace Demo
{
    public class EmployeeDBContext : DbContext
    {
        public DbSet<Course> Courses { get; set; }
        public DbSet<Student> Students { get; set; }
        public DbSet<StudentCourse> StudentCourses { get; set; }
    }
}

Step 6: Add the database connection string in web.config file.
<connectionStrings>
  <add name="EmployeeDBContext"
            connectionString="server=.; database=Sample; integrated security=SSPI;"
            providerName="System.Data.SqlClient" />
</connectionStrings>

Step 7: Add a webform to the project. Copy and paste the following HTML.
<body>
    <form id="form1" runat="server">
    <div style="font-family: Arial">
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
    <br />
    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click"
        Text="Assign WCF Course to Mike"/>
    <br />
    <br />
    <asp:Button ID="Button2" runat="server" OnClick="Button2_Click"
        Text="Remove John from SQL Server Course"/>
</div>

Step 8: Copy and paste the following code in the code-behind file.
using System;
using System.Linq;
namespace Demo
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            EmployeeDBContext employeeDBContext = new EmployeeDBContext();

            GridView1.DataSource = (from student in employeeDBContext.Students
                                    from studentCourse in student.StudentCourses
                                    select new
                                    {
                                        StudentName = student.StudentName,
                                        CourseName = studentCourse.Course.CourseName,
                                        EnrolledDate = studentCourse.EnrolledDate
                                    }).ToList();

            // The above query can also be written as shown below
            //GridView1.DataSource = (from course in employeeDBContext.Courses
            //                        from studentCourse in course.StudentCourses
            //                        select new
            //                        {
            //                            StudentName = studentCourse.Student.StudentName,
            //                            CourseName = course.CourseName,
            //                            EnrolledDate = studentCourse.EnrolledDate
            //                        }).ToList();

            GridView1.DataBind();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            EmployeeDBContext employeeDBContext = new EmployeeDBContext();
            employeeDBContext.StudentCourses.Add(new StudentCourse
            { StudentID = 1, CourseID = 4, EnrolledDate = DateTime.Now });
            employeeDBContext.SaveChanges();
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            EmployeeDBContext employeeDBContext = new EmployeeDBContext();
            StudentCourse studentCourseToRemove = employeeDBContext.StudentCourses
                .FirstOrDefault(x => x.StudentID == 2 && x.CourseID == 3);
            employeeDBContext.StudentCourses.Remove(studentCourseToRemove);
            employeeDBContext.SaveChanges();
        }
    }
}

Step 9: If you already have Sample database in SQL Server. Delete it from SQL Server Management Studio.

Step 10: Run the application. Sample database and the required tables (Courses, Students & StudentCourses) must be created at this point.

Step 11: Insert test data using the following SQL script
-- Insert into Courses Table
Insert into Courses values ('C#')
Insert into Courses values ('ASP.NET')
Insert into Courses values ('SQL Server')
Insert into Courses values ('WCF')
GO

-- Insert into Students Table
Insert into Students values ('Mike')
Insert into Students values ('John')
GO

-- Insert into StudentCourses Table
Insert into StudentCourses values (1, 1, Getdate())
Insert into StudentCourses values (1, 2, Getdate())
Insert into StudentCourses values (2, 1, Getdate())
Insert into StudentCourses values (2, 2, Getdate())
Insert into StudentCourses values (2, 3, Getdate())
GO

Step 12: Reload the webform. Notice that the data is displayed as expected. Now, click on the following buttons, and notice that StudentCourses table is updated as expected.
a) Assign WCF Course to Mike
b) Remove John from SQL Server Course