Table Splitting involves mapping multiple entities in the conceptual layer to a single table in the store. There are a few scenarios where this is useful. The most common is that a table may contain a large column that may not be used frequently. This could be an image, other binary data, xml … The net-net is that you do not want to always return this beast when fetching an entity. If / when you want it you can load that relationship using eager, explicit or lazy loading.
A second scenario is that you want to split the data among 2 entities for conceptual reasons. Perhaps you only want certain consumers to have access to certain kinds of data. In this post, I will illustrate the first scenario, as it is more common.
Watch the Screencast
Entity Framework Modeling: Table Splitting
Blog Series Index
Index of Entity Framework Posts and Screencasts
The Scenario
Below you can see the Employees table in Northwind. As you can see, it has an image column named Photo. It also has an nvarchar column storing a path to an image (not sure why it has both). In this demo, I’ll spilt both columns off into a separate entity named EmployeePhoto.
Implementing Entity Splitting
Step 1: Create a console application. I named mine TableSplitting.
Step 2: Add the Entity Data Model. I named mine NorthwindModel
Generate the model from the database.
Choose the Nortwind connection (or create one, if you need to). Don’t forget to set an appropriate name to the “entity connection settings”, as this will be the name of your context. I use the naming convention <Domain>Context, so mine is named NorthwindContext.
Add the Employees table.
Your conceptual model will look like this:
Step 3: Copy the Employee entity
Simply single-click on the Employee entity and press ctrl+c. Then click on some whitespace in the model and press ctrl+v. You should see something like the following:
Step 4: Rename the Employee1 Entity and delete the appropriate properties
Rename this entity to EmployeePhoto. Then delete every property from the entity except the following (you can single click or multi-select properties and press the delete key):
- EmployeeID
- Photo
- PhotoPath
It should now look something like this:
Step 5: Delete the Photo and PhotoPath properties from the Employee Entity
Select the Photo and PhotoPath properties from the Employee entity and press the delete key.
Step 6: Map the EmployeePhoto entity to the Employees Table
Right-click the EmployeePhoto entity and choose “Table Mapping”
Choose to map this entity to the Employees table.
Because the property names line up with the table column names, you do not need to do anything else. Notice that the EmployeeID, Photo and PhotoPath properties are mapped to the appropriate table columns:
Step 7: Add a 1:1 Association between the Employee and EmployeePhoto entities
Right-click the EmployeePhoto entity, choose “Add” and choose “Association…”
Make sure the multiplicity between the 2 entities is 1:1 like below:
Step 8: Add a referential constraint between the Employee and EmployeePhoto entities
Double-Click the association line to bring up the referential constraint dialog. Choose the Employee as the principle and press OK.
This created a referential constraint in the conceptual layer. You can look at it by saving the model, closing the designer and opening the edmx with an XML Editor.
Scroll down to the Association Element in the CSDL and you can see the referential constraint.
Testing the Model
Write the following code in your SVM:
static void Main(string[] args)
{
using (NorthwindContext context = new NorthwindContext())
{
var employee = (from e in context.Employees
select e).FirstOrDefault();
Console.WriteLine(employee.LastName);
Console.WriteLine(employee.EmployeePhoto.Photo.Length.ToString());
}
Console.ReadLine();
}
If you run it, you get the expected result:
Taking a look at SQL Profiler, you can see that the original query did not return the Photo or PhotoPath columns.
You can also see that, when we accessed the EmployeePhoto entity, Lazy Loading kicked in and a query was sent to the server to fetch the Photo and PhotoPath columns. Have a look:
Using Eager Loading
We didn’t have to use Lazy Loading to fetch the related EmployeePhoto entity. If I knew that I needed to access the EmployeePhoto with the Employee, I could simply use Eager Loading. have a look at the following code. The only difference is that I added an “Include” statement.
using (NorthwindContext context = new NorthwindContext())
{
var employee = (from e in context.Employees.Include("EmployeePhoto")
select e).FirstOrDefault();
Console.WriteLine(employee.LastName);
Console.WriteLine(employee.EmployeePhoto.Photo.Length.ToString());
}
Console.ReadLine();
If I clear profiler and run the code again, notice that only one query is now run and that the Photo and PhotoPath columns are returned.
Conclusion
The tooling in Entity Framework 4 makes short work of Table Splitting.