So far each of our tables is represented by a single model (class) in our C# code, however, this does not have to be so. We can have a table which is represented by more than one model. For this we can use the functionality of Table Splitting.

One of the reasons we would do this would be to simplify selection queries. For example: It is possible that we want to make queries to a table, and that, in general, we do not want to select certain columns. The table splitting technique allows us to do that.

To implement the table splitting we need to do two things: Use navigation properties between the models and use the Fluent API to configure the relationship between both models and configure the name of the table. The relationship will be one to one.

Let’s first define the situation. We have the table Students, which is mapped with the Student model. There is a set of columns in Students that we don’t want to query unnecessarily, but we don’t want to have almost all our LINQ statement using the Select function in order to avoid retrieving that set of columns. What we can do is place that set of columns in another class, StudentDetail. Let’s see these classes:

public class Student
{
   public int Id { get; set; }
   public StudentDetail Details { get; set; }
   // ... other properties
}
public class StudentDetail
{
   public int Id { get; set; }
   public string SSN { get; set; }
   public Student Student { get; set; }
}

As we can see, the Details navigation property of the Student class expresses that there is a one-to-one relationship between both entities. Now, the SSN column will not be retrieved unless we explicitly ask for it. Now let’s go to the Fluent API to perform the remaining configurations. In the OnModelCreating method we will place the following code:

modelBuilder.Entity<Student>().HasOne(x => x.Details)
.WithOne(x => x.Student)
.HasForeignKey<StudentDetail>(x => x.Id);

modelBuilder.Entity<StudentDetail>().ToTable("Students");

The first step is to configure the relationship between the primary keys of the entities. Notice that what we are indicating is that the StudentDetail foreign key will be its Id column. The next thing we did was to express that StudentDetail is going to be mapped with the Students table. Then we can add the migration and update the changes in the database. With this, our students table has the column SSN, which comes from the StudentDetails model.

Given the fact that at the level of navigation properties what we have is a one-to-one relationship, we can make queries using the Students and StudentDetails entities. Example:

// Does not include StudentDetails columns (the SSN column in our case)
var students = context.Students.ToList();

// Does include StudentDetails columns
var studentsWithDetails = context.Students.Include(x => x.Details).ToList();

Inserting a student with its details:

var student = new Student();
student.Name = "George Harris";
var studentDetails = new StudentDetail();
studentDetails.SSN = "1987";
student.Details = studentDetails;
context.Add(student);
context.SaveChanges();

In the case that the student already exists, and we only want to update the detail information, we can do it in the following way:

var george = context.Students.First(x => x.Name == "George Harris");
var studentDetails = new StudentDetail();
studentDetails.Id = george.Id;
studentDetails.SSN = "01234567891";
context.StudentDetails.Update(studentDetails);
context.SaveChanges();

Summary

The table splitting technique helps us to project a table in several models, that is, a table in your database will be represented by more than one C# class.