Home > Entity Framework > Table Splitting in Entity Framework

Table Splitting in Entity Framework

In my previous post I covered the Entity Splitting technique and in this post I am going to talk about just the opposite of that technique.

Table Splitting:- In this scenario there are multiple entities or domain objects but the data is stored in a single table. Lets say in our database we have an employee table which stores basic information like first name, last name, DOB as well their photo. However in our domain model we want to create two different entities.

Lets write our domain objects to see this is action.

Employee.cs

public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public DateTime DOB { get; set; }
    public virtual EmployeePhoto Photo { get; set; }
}

EmployeePhoto.cs

public class EmployeePhoto
{
    public int Id { get; set; }
    public byte[] Photo { get; set; }
    public Employee Employee { get; set; }
}

As you can see we have define two entities Employee and EmployeePhoto in our domain model and we have declared EmployeePhoto as virtual, with this declaration we can take advantage of lazy loading and when we query the Employee entity through EF the photo column will not be selected as part of the linq query. Second it also indicates that EmployeePhoto entity cannot exist without an Employee. It is also important that EmployeePhoto has a reference of Employee entity and has an Id properties which we will use to map the two entities into one table.

Lets wire up the relationship in our context class as shown below.

EmployeeContext.cs

public class EmployeeContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }
    public DbSet<EmployeePhoto> Photos { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<EmployeePhoto>().HasKey(p => p.Id);

        modelBuilder.Entity<Employee>()
            .HasRequired(e => e.Photo)
            .WithRequiredPrincipal(p => p.Employee);

        modelBuilder.Entity<EmployeePhoto>().ToTable("Employee");
        modelBuilder.Entity<Employee>().ToTable("Employee");
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}

We first defined the secondary entity( Employee Photo ) that and state that it has an identity key which is identical in our main entity. Next in our Employee entity we defined that the photo property is required with the Employee as the primary entity and then using ToTable function we map both the entity to the same table i.e. Employee.

Advertisements
  1. December 7, 2015 at 3:02 am

    Thanks for the helpful posts on Entity Framework. I teach an ASP.NET MVC class and will share some of these posts with my students.

    • December 7, 2015 at 6:47 pm

      Thanks Bahrom, I am glad that you liked my post and it is going to help your students.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: