Table Splitting in Entity Framework

October 13, 2009

no comments

Table Splitting in Entity Framework

Entity Framework includeTable Splitting in Entity Framework
a lot of ways to customize
the Entity Data Model. One
such way is Table Splitting
which enables to map multiple
entity types to a single table.
This post will show how we can achieve this ability.

Why Using Table Splitting?

Sometimes we want to delay the loading of properties which the columns
that they map to hold very large amount of data. This can be a big Xml
column, images or big binary data (blob). In such cases that we want to use
lazy loading to some columns, you will want to use the table splitting
feature of Entity Framework. Another reason which is less important is
to organize and arrange the columns you have in a single table into more
then one object. For this reason I prefer to use complex types which
aren’t managed as EntityObjects.

Table Splitting Preparation for the Example

In the example I’m going to use the following table:

CREATE TABLE [dbo].[Course](
    [CourseID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](100) NOT NULL,
    [Days] [nvarchar](50) NOT NULL,
    [Time] [datetime] NOT NULL,
    [Location] [nvarchar](100) NULL,
    [Credits] [int] NOT NULL,
    [DepartmentID] [int] NOT NULL,
 CONSTRAINT [PK_School.Course] PRIMARY KEY CLUSTERED 
(
    [CourseID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO

Pay attention that there is no reason to use table splitting in this

table. I use this table only to show the concept.

After generating the model from the database the result model will

look like:

Entity Designer Diagram 

Table Splitting

Now that we have our model lets split the table. I’m going to split

the table into a course and a course details entities.



Step 1

Copy and paste the Course entity to the model.

The model should look like:

Copy and Paste

Step 2

Rename Course1 to CourseDetails and remove the irrelevant properties in

every entity. In CourseDetails remove Title, and DepartmentID. In Course

remove all the properties of CourseDetails (not including! the CourseID).

The model should look like:

Model without Association

Step 3

Create a 1 to 1 association between Course and CourseDetails.

Create Association

After pressing OK the resulting model will look like:

Model with Association

Step 4

Map the model to the relevant parts. Map CourseDetails to Course table

and map the association to Course table. The CourseDetails mapping will

look like:

CourseDetails Mapping

and the association mapping will look like:

Association Mapping

Step 5

In EF1 save the model and open it in Xml editor to edit the CSDL.

In the CSDL we need to add referential constraint to inform the model that

there is a parent child relationship between Course and CourseDetails:

<Association Name="CourseCourseDetails">
  <End Type="SchoolModel.Course" Role="Course" Multiplicity="1" />
  <End Type="SchoolModel.CourseDetails" Role="CourseDetails" Multiplicity="1" />
  <ReferentialConstraint>
    <Principal Role="Course">
      <PropertyRef Name="CourseID"/>
    </Principal>
    <Dependent Role="CourseDetails">
      <PropertyRef Name="CourseID"/>
    </Dependent>
  </ReferentialConstraint>
</Association>

In EF4 the referential constraint is automatically created. If it isn’t created

you can use the designer to generate it for you with the

Referential Constraint designer.

Step 6

Test the result.

using (var context = new SchoolEntities())
{
    // get all courses
    foreach (var c in context.Courses)
    {
        if (!c.CourseDetailsReference.IsLoaded)
        {
            c.CourseDetailsReference.Load();
        }
    }
 
    // create a new course with details
    var course = Course.CreateCourse(30, "New Course", 1);
    context.AddToCourses(course);
    var courseDetails = CourseDetails.CreateCourseDetails(30, "M", DateTime.Now, 3);
    course.CourseDetails = courseDetails;
    context.SaveChanges();
}

 

Summary

Lets sum up, in the post I showed another way to customize your

entity data model which is table splitting. Table splitting is very appropriate

for loading large data properties on demand and not in every query to

the database.

DotNetKicks Image
Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

*

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>