|
This is the fourth post in a series that explains
entity association mappings with EF Code First. This
series includes:
|
In the
second part
of this series we saw how to map a special kind of
one-to-one association—a composition with complex
types. We argued that this is usually the simplest way
to represent one-to-one relationships which comes with
some
limitations. We addressed the first limitation (shared
references) by introducing shared primary key
associations in the
previous blog post. In today’s blog post we are going to address the
third limitation of the complex types by learning
about Table Splitting as yet another way to map
a one-to-one association.
The Motivation Behind this Mapping: A Complex Type
That Can be Lazy Loaded
A shared primary key association does not expose us to
the third limitation of the complex types regarding
Lazy Loading, we can of course lazy/defer load the Address
information of a given user but at the same time, it
does not give us the same SQL schema as the complex
type mapping. After all, it adds a new table for the
Address entity to the schema while mapping the Address
with a complex type stores the address information in
the Users table. So the question still remains there:
How can we keep everything (e.g. User and Address) in
one single table yet be able to lazy load the complex
type part (Address) after reading the principal entity
(User)? In other words, how can we have lazy loading
with a complex type?
Splitting a Single Table into Multiple Entities
Table splitting (a.k.a. horizontal splitting) enables
us to map a single table to multiple entities. This is
particularly useful for scenarios that we have a table
with many columns where some of those columns might
not be needed as frequently as others or some of the
columns are expensive to load (e.g. a column with a
binary data type).
An Example From the Northwind Database
Unlike the other parts of this series, where we start
with an object model and then derive a SQL schema
afterwards, in this post we are going to do the
reverse, for a reason that you'll see, we will start
with an existing schema and will try to create an
object model that matches the schema. For that we are
going to use the Employees table from the Northwind
database. You can download and install Northwind
database from
here
If you don't have it already installed on your SQL
Server. The following shows the Employees table from
the Northwind database that we are going to use:
|
|
As you can see, this table has a Photo column of image
type which makes it a good candidate to be lazy loaded
each time we read an Employee from this table.
The Object Model
As the following object model shows, I created two
entities: Employee and EmployeePhoto. I also created a
unidirectional association between these two by
defining a navigation property on the Employee class
called EmployeePhoto:
|
public class Employee
{
public int EmployeeID { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
public string Title { get; set; }
public string TitleOfCourtesy { get; set; }
public DateTime? BirthDate { get; set; }
public DateTime? HireDate { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string Region { get; set; }
public string PostalCode { get; set; }
public string Country { get; set; }
public string HomePhone { get; set; }
public string Extension { get; set; }
public string Notes { get; set; }
public int? ReportsTo { get; set; }
public virtual EmployeePhoto EmployeePhoto { get; set; }
}
public class EmployeePhoto
{
[Key]
public int EmployeeID { get; set; }
public byte[] Photo { get; set; }
public string PhotoPath { get; set; }
}
public class NorthwindContext : DbContext
{
public DbSet<Employee> Employees { get; set; }
public DbSet<EmployeePhoto> EmployeePhoto { get; set; }
}
|
How to Create a Table Splitting with Fluent API?
As also mentioned in the previous post, by convention,
Code First always takes a unidirectional association
as one-to-many unless we specify otherwise with fluent
API. However, the fluent API codes that we have seen
so far in this series won't let us create a table
splitting. If we mark EmployeePhoto class as a complex
type, we wouldn't be able to lazy load it anymore or
if we create a shared primary key association then it
will look for a separate table for the EmployeePhoto
entity which we don't have in the Northwind database.
The trick is to create a shared primary key
association between Employee and EmployeePhoto
entities but then instruct Code First to map them both
to the same table. The following code shows how:
|
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Employee>()
.HasRequired(e => e.EmployeePhoto)
.WithRequiredPrincipal();
modelBuilder.Entity<Employee>().ToTable("Employees");
modelBuilder.Entity<EmployeePhoto>().ToTable("Employees");
}
|
|
Note how we made both ends of the association required
by using
HasRequired
and
WithRequiredPrincipal
methods, even though both the Photo and PhotoPath
columns has been defined to allow NULLs.
|
See the Lazy Loading of the Dependent Entity in
Action
Now it's time to write a test to make sure that EF
does not select the Photo column each time we query
for an employee:
|
using (var context = new NorthwindContext())
{
Employee employee = context.Employees.First();
byte[] photo = employee.EmployeePhoto.Photo;
}
|
|
The following screen shot from the SQL Profiler shows
the query that has been submitted to SQL Server as the
result of reading the first employee object:
|
|
|
Accessing the EmployeePhoto navigation property of the
employee object on the next line causes EF to submit a
second query to the SQL Server to lazy (implicit) load
the EmployeePhoto (By default, EF fetches associated
objects and collections lazily whenever you access
them):
|
|
Where to Use this Mapping?
I recommend using Table Splitting only for mapping of
the legacy databases, actually that's the reason we
start this post from an existing database like
Northwind. For
green-field development
scenarios consider using
shared primary key association
instead. There are several reasons why you may want to
split the Employee table to two tables when designing
a new physical data model for your application. In
fact, it is very common for most applications to
require a core collection of data attributes of any
given entity, and then a specific subset of the
noncore data attributes. For example, the core columns
of the Employee table would include the columns
required to store their name, address, and phone
numbers; whereas noncore columns would include the
Photo column. Because Employee.Photo is large, and
required only by a few applications, you would want to
consider splitting it off into its own table. This
would help to improve retrieval access times for
applications that select all columns from the Employee
table yet do not require the photo. This also works
pretty well for EF since it doesn't support lazy
loading at the scalar property or complex type level.
|
Summary
In this post we learned about mapping a one-to-one
association with table splitting. It enabled us to
have lazy loading for the EmployeePhoto entity,
something that we would have missed, had we mapped it
with a complex type. We saw that on the database side
it looks like a complex type mapping but on the object
model it is not a complex type since we mapped
EmployeePhoto as an Entity with an object identifier
(EmployeeID). In fact, it's a special kind of a shared
primary key association where both the principal and
dependent entities are mapped to one single table.
This somehow exotic one-to-one association mapping
should be reserved only for the mapping of existing
legacy databases.
|