Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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.

Entity Framework Tutorial

8 comments:

  1. An item with the same key has already been added. i got this error

    ReplyDelete
    Replies
    1. i am waiting for the solution....Assaf by any chance did u get the solution?

      Delete
    2. First i made employeeId in Employeecontactdetails as idenetity then I too got the same error. so i deleted tables and edmx file and created again as venkat sir told then it works fine

      Delete
    3. just add foreign key in EmployeeContactDetails.

      Delete
    4. Here is the updated script for EmplyeeContactDetails:

      Create table EmployeeContactDetails
      (
      EmployeeID int primary key,
      Email nvarchar(50),
      Mobile nvarchar(50),
      LandLine nvarchar(50),
      CONSTRAINT fk_pk_empid FOREIGN KEY(EmployeeID) REFERENCES Employees(EmployeeID)
      )
      GO

      Delete
  2. I changed the table definitions and it worked just fine, i just set a foreign key reference in EmployeeContactDetails table:

    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,
    foreign key (EmployeeID) references Employees (EmployeeID),
    Email nvarchar(50),
    Mobile nvarchar(50),
    LandLine nvarchar(50)
    )
    GO

    ReplyDelete
    Replies
    1. yes..just need to add foreign key refrence to primark key of EmployeeContactdetails

      Delete
  3. Added foreign key, still getting the same error. Anybody here facing same problem?

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.