In this article, we will learn how to split an entity into multiple tables in EF Core. This is a new feature that we can find in version 7.0 of Entity Framework Core.
So, let’s start.
Use Cases For Splitting an Entity Into Multiple Tables in EF Core
There are some cases where we divide the information about an entity into multiple tables in the database.
One reason could be that we update a part of this information quite often, while the rest of the data remains more or less unchanged. It is usually a good idea to divide such entities into more than one table for performance reasons.
Another case could be when we have to use a legacy database where information splits into different tables. Moreover, the complexity of the data model as well as security and privacy reasons may also impose the splitting of the data into separate tables.
However, in our code, we may find it useful to have all this information inside one entity. In this way, when we will be making a query for an entity, EF Core will automatically make a join between those tables and provide us with the full entity data. Moreover, when we create, update, or delete an entity, EF Core will create the corresponding queries for all the involved tables.
Let’s see how this works!
Split an Entity Into Multiple Tables in EF Core
Let’s consider the case where we store user information and their settings in our database:
public class User
{
public int Id { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public bool RememberMe { get; set; }
public string Theme { get; set; }
public User(int id, string name, string email, bool rememberMe, string theme)
{
Id = id;
Name = name;
Email = email;
RememberMe = rememberMe;
Theme = theme;
}
}
Note that we define a single entity User that holds all the information about a user and his preferred settings. However, since the settings properties Theme and RememberMe may change more frequently than the rest of the user information, we can decide to keep them in a separate table.
To this end, we will use a Users table to keep the ID, Name, and Email columns. Moreover, we are going to store the Theme and RememberMecolumns in a separate table which we will call UserSettings.
Let’s proceed by defining the UserContext class, where we configure the entity splitting:
public class UserContext : DbContext
{
public UserContext(DbContextOptions<UserContext> contextOptions)
: base(contextOptions)
{ }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<User>(
eb =>
{
eb
.ToTable("Users")
.SplitToTable(
"UserSettings",
tb =>
{
tb.Property(user => user.Id).HasColumnName("UserId");
tb.Property(user => user.RememberMe);
tb.Property(user => user.Theme);
});
});
}
public DbSet<User> Users { get; set; }
//...
}
We see that in the OnModelCreating() method, we split the User entity into two tables: Users and UserSettings.
We also define which entity fields will be stored in the UserSettings table (Id, RememberMe, and Theme). Moreover, we choose to use a different name for the column containing the User ID (UserId instead of Id).
Note that the rest of the entity fields (Name and Email) along with the user Id field are stored in the Users table.
For debugging purposes, let’s extend the UserContext class, to inspect the SQL queries that are created by EF Core:
public class UserContext : DbContext
{
//...
public static readonly Microsoft.Extensions.Logging.LoggerFactory _myLoggerFactory =
new LoggerFactory(new[] {
new Microsoft.Extensions.Logging.Debug.DebugLoggerProvider()
});
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseLoggerFactory(_myLoggerFactory);
}
}
We should also install the Microsoft.Extensions.Logging.Debug package from NuGet to be able to use this debugging functionality.
Create Scenario
Let’s give it a try, by creating a new User and storing it in the database:
using (var context = new UserContext(contextOptions))
{
if (context.Database.EnsureCreated())
{
var user = new User(1, "John Doe", "[email protected]", true, "Classic");
context.Add(user);
context.SaveChanges();
user = context.Users.Single(u => u.Id == 1);
}
}
If we check on the returned User entity, we will see that it contains all the information about the new user.
Furthermore, we can verify that this information is indeed divided into two separate tables, User and UserSettings, by having a look at the debugging output:
CREATE TABLE "Users" (
"Id" INTEGER NOT NULL CONSTRAINT "PK_Users" PRIMARY KEY AUTOINCREMENT,
"Name" TEXT NOT NULL,
"Email" TEXT NOT NULL
);
...
CREATE TABLE "UserSettings" (
"UserId" INTEGER NOT NULL CONSTRAINT "PK_UserSettings" PRIMARY KEY AUTOINCREMENT,
"RememberMe" INTEGER NOT NULL,
"Theme" TEXT NOT NULL,
CONSTRAINT "FK_UserSettings_Users_UserId" FOREIGN KEY ("UserId") REFERENCES "Users" ("Id") ON DELETE CASCADE
);
...
INSERT INTO "Users" ("Id", "Email", "Name")
VALUES (@p0, @p1, @p2);
...
INSERT INTO "UserSettings" ("UserId", "RememberMe", "Theme")
VALUES (@p0, @p1, @p2);
...
SELECT "u"."Id", "u"."Email", "u"."Name", "u0"."RememberMe", "u0"."Theme"
FROM "Users" AS "u"
INNER JOIN "UserSettings" AS "u0" ON "u"."Id" = "u0"."UserId"
LIMIT 2
We can see that the two tables Users and UserSettings are created (when not already existing) and the two INSERT statements are automatically translated from our EF Core code – when we Add() our new User entity to the EF Core context – into SQL.
The SELECT statement to retrieve the information for the User entity consists of an INNER JOIN between the two tables Users and UserSettings.
Update and Delete Scenario
So far, we have seen how to create a new entity that we have split into different tables in the database. We have also learned how to query for such an entity. Let’s see now how to update or delete a split entity.
When we update a split entity, the EF Core creates update queries only for the affected tables. For example, let’s consider the case where we update the user’s Theme property:
using (var context = new UserContext(contextOptions))
{
if (context.Database.EnsureCreated())
{
var user = context.Users.Single(u => u.Id == 1);
user.Theme = "Flashy";
context.SaveChanges();
}
}
Now, let’s examine the debugging output:
UPDATE "UserSettings" SET "Theme" = @p0 WHERE "UserId" = @p1 RETURNING 1;
Indeed, EF Core seems to have updated only the UserSettings table.
Next, let’s delete a user from the database:
using (var context = new UserContext(contextOptions))
{
if (context.Database.EnsureCreated())
{
var user = context.Users.Single(u => u.Id == 1);
context.Users.Remove(user);
context.SaveChanges();
}
}
We see that this operation resulted in deleting one row from each table. We also can verify it from the logs:
DELETE FROM "UserSettings" WHERE "UserId" = @p0 RETURNING 1; ... DELETE FROM "Users" WHERE "Id" = @p0 RETURNING 1;
Limitations of Entity Splitting
There are a couple of limitations to entity splitting in EF Core.
First of all, we cannot use it for entity types that participate in entity-type hierarchies. This means that we cannot have a derived type, e.g. Admin that inherits from User and continue to use entity splitting.
Moreover, we cannot have fragmented entities. This means, in our example, that there should be a row in the UserSettings table corresponding to each row in the Users table.
Conclusion
In this article, we have learned how to use Entity Splitting in order to split information about an entity in more than one table in EF Core. We have explored the basic capabilities and limitations of this functionality along with the most important use cases when we want to split an entity into multiple tables in EF Core.
