Entity Splitting in Entity Framework

March 6, 2009

Entity Splitting in Entity Framework

One of the mapping scenariosEntity Splitting in Entity Framework
that I talked about in the
session at WDC this week
but I didn’t show an example
is entity splitting in Entity
Framework
. This post will explain
what is entity splitting and how to implement this mapping scenario
in Entity Framework.

What is Entity Splitting?

Entity splitting is a scenario that happen when our entity is constructed
from many tables in the database. This happens for example when we don’t
want duplications in our database and use lookup tables instead which our
table has a reference key to those tables. More scenarios can be that we
decide to split our entity representation in the database.
When we have such scenarios the wizard of Entity Framework don’t know
that we split our entity and we have to map the entity to more then
one table.

Splitting to Two Tables Example

In the example I have a database that looks like this:

Splitting To Two Tables Database

As you can see I split the employee entity to two tables: Employees and
Address. The two tables have the same primary key which is EmployeeID.
When I use the Entity Framework wizard and construct my model
the result will be:
Splitting To Two Tables Model

I want to have a single employee entity which contains its address.
How can I do that?
The answer is easy. I’ll map Employee entity to two tables instead of
one and drop the Address entity.

Step 1

Cut and paste the address details (City, Street and ZipCode) to the
Employee entity.

Step 2

Add a second mapping to the Employee entity. The mapping should be
to the Address table. When you do that Entity Framework is smart enough
to map the properties to their database fields.
The result should look like:
Entity Splitting with Mapping

Step 3

Remove the Address entity from the model.

Step 4

Test the result.
I use the following code to test the model:

using (EntityFrameworkExampleEntities context = new EntityFrameworkExampleEntities())
{
    var employee = new Employee
    {
        EmployeeType = "Developer",
        City = "Tel Aviv",
        EmployeeFirstName = "Dan",
        EmployeeLastName = "Ronen"
    };
 
    context.AddToEmployeeSet(employee);
    context.SaveChanges();
 
    var newEmployee = context.EmployeeSet.First(e => e.City.Equals("Tel Aviv"));
    Console.WriteLine("{0} {1}", newEmployee.EmployeeFirstName, newEmployee.EmployeeLastName);
}

Pay Attention!

In situations such as more then one table entity, we have table joins

whenever we retrieve the data and multiple updates whenever we use

CUD operations. If in your situation you don’t need the address properties

every time you retrieve an employee you should use other mapping scenarios

such as the navigation property that was constructed in the model in the

first place.

Summary

Lets sum up, in the post I showed how to map a split entity into a

single Entity Framework entity. Also you should always consider whether

to use entity splitting or to use other mapping scenarios because of

the performance impact of multiple joins.

DotNetKicks Image
Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

6 comments

  1. lpeMarch 7, 2009 ב 12:29

    you’re awesome :)
    thanks!

    Reply
  2. xxjthxxMarch 8, 2009 ב 11:49

    I don’t understand what is the need to “merge” those two entites together?

    The only possible scenario I can think of would be to automatically delete/create the other entry in database so there wouldn’t be any inconsistencies…. but this seems like a very ugly way to handle it…

    Reply
  3. Gil FinkMarch 8, 2009 ב 13:04

    @xxjthxx,
    Sometimes you don’t want to have a database table which has too many fields so you split it. The example I showed is very simple and in very large databases it could exists. I could use an Address entity instead of merging the entities but then you could have an EmployeeID of an non existing employee (if you insert a stand alone Address). As I wrote, the common scenario is to have lookups tables for a repeated data. In that case your entity is spread across more then one table.

    Reply
  4. NMay 24, 2009 ב 22:47

    Hi,
    Is it possible to do the same with a one-to-many table relationship? -so in the presented sample, we have multiple user addresses instead of one?

    Reply
  5. Gil FinkMay 25, 2009 ב 20:14

    Hi N,
    You can build an association in a one-to-many table relationship. The entity splitting isn’t recommanded for that issue.

    Reply