Table Splitting in Entity Framework
Entity Framework include
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:
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:
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:
Step 3
Create a 1 to 1 association between Course and CourseDetails.
After pressing OK the resulting model will look like:
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:
and the association mapping will look like:
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.
