Mapping the same model to multiple tables with EntityFramework.Core

Posted on In Word count in article: 8.7k Reading time ≈ 8 mins.

With EntityFramework Core, we can use attributes or Fluent API to config the model mappings. One day I just got a scenario that needs a new mapping style. There is a system that generates lots of data every day and it needs to store the data per table per day. For example, the database looks like:

Database

All the tables have the same structure. So how could we change the mapping to avoid creating lots of models?

In this article, I will show you how to change the mapping to handle this situation. You can also use this method to extend more usages.

Creating the .NET Core 3.1 project

Now we can use .NET Core 3.1 which is an LTS version of .NET Core. So that you can easily upgrade it to .NET 5 in the future.

I suppose you already have the latest .NET Core SDK installed on your machine. If not, you can download it from https://dotnet.microsoft.com/download. Then you can use dotnet CLI to create projects. For this sample, I will use .NET Core 3.1.

Let create a new .NET Core Console project named DynamicModelDemo.

1
dotnet new console --name DynamicModelDemo

We can create a solution by using this command:

1
dotnet new sln --name DynamicModelDemo

Then add the project to the solution:

1
dotnet sln add "DynamicModelDemo/DynamicModelDemo.csproj"

Now you can open the solution by Visual Studio.

Creating the model

The model is very simple. Add a new class file named ConfigurableEntity.cs in the project that has the code below:

1
2
3
4
5
6
7
8
9
10
11
12
using System;

namespace DynamicModelDemo
{
    public class ConfigurableEntity
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        public DateTime CreateDateTime { get; set; }
    }
}

We will use CreateDateTime to identify which table the model should be mapped to.

Adding EntityFramework Core

Navigate to the project folder then use the command below to add the EF.Core package:

1
2
dotnet add package Microsoft.EntityFrameworkCore.SqlSever
dotnet add package Microsoft.EntityFrameworkCore.Design

If you have not installed ef tool for dotnet core, run the command below to install it:

1
dotnet tool install --global dotnet-ef

So that you can use the dotnet ef tool to create migrations or update the database by applying the migrations.

Creating the DbContext

Add a new class file named DynamicContext.cs to the project. The content is shown below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using System;

namespace DynamicModelDemo
{
    public class DynamicContext : DbContext
    {
        public DbSet<ConfigurableEntity> Entities { get; set; }

        #region OnConfiguring
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
                .UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=DynamicContext;Trusted_Connection=True;");
        #endregion

        #region OnModelCreating
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<ConfigurableEntity>(b =>
            {
                b.HasKey(p => p.Id);
            });
        }
        #endregion
    }
}

For now, it is the basic configuration for EF.Core. It uses the default mapping which means the model will be mapped to a table named Entities. So what we should do if we want to map the models to different tables based on its CreateDateTime property?

You might know we can use ToTable() method to change the table name, but how can we change the table name in OnModelCreating method? When EF builds the model, it only runs OnModelCreating once.

For this scenario, we need to change the default mapping by using IModelCacheKeyFactory, which allow us to hook into the model caching mechanism so EF is able to create different models based on its property.

What is IModelCacheKeyFactory?

Here is the introduction from the Microsoft Docs:

EF uses IModelCacheKeyFactory to generate cache keys for models.

By default, EF assumes that for any given context type, the model will be the same. But for our scenario, the model would be different because it is mapped to different tables. So we need to replace the IModelCacheKeyFactory service with our implementation which compares the cache keys to map the model to the correct table.

Please note that this interface is typically used by database provider and other extensions, not used in application code. But for our goal, it is a feasible approach.

Creating an implementation of IModelCacheKeyFactory

We need to use the CreateDateTime to difference the tables. Add a property in the DynamicContext class:

1
public DateTime CreateDateTime { get; set; }

Add a new class file named DynamicModelCacheKeyFactory.cs in the project. The code is shown below:

1
2
3
4
5
6
7
8
9
10
11
12
13
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;

namespace DynamicModelDemo
{
    public class DynamicModelCacheKeyFactory : IModelCacheKeyFactory
    {
        public object Create(DbContext context)
            => context is DynamicContext dynamicContext
                ? (context.GetType(), dynamicContext.CreateDateTime)
                : (object)context.GetType();
    }
}

This implementation takes the CreateDateTime property into account when producing a model cache key.

Applying the new IModelCacheKeyFactory

Next, we can register the new IModelCacheKeyFactory in the context:

1
2
3
4
5
6
#region OnConfiguring
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=DynamicContext;Trusted_Connection=True;")
        .ReplaceService<IModelCacheKeyFactory, DynamicModelCacheKeyFactory>();
#endregion

So that we can map the table names respectively in OnModelCreating method:

1
2
3
4
5
6
7
8
9
10
#region OnModelCreating
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ConfigurableEntity>(b =>
    {
        b.ToTable(CreateDateTime.ToString("yyyyMMdd"));
        b.HasKey(p => p.Id);
    });
}
#endregion

The CreateDateTime comes from the property of DynamicContext.

We can specify the ``CreateDateTimeproperty when creatingDynamicContext`:

1
var context = new DynamicContext { CreateDateTime = datetime };

If datetime is “27/03/2020”, the model of context would be mapped to the table named “20200327”.

Creating the database

Before we validate our code, we need to create the database first. However, EF migration is not the best solution for this situation as the system will generate more tables as time goes by. We just use it to create some sample tables to validate the mapping. Actually the system should have another way to dynamically generates the tables every day.

Run the below command to create the first migration:

1
dotnet ef migrations add InitialCreate

You would see there are two files generated in the Migrations folder. Open the xxx_InitialCreate.cs file and update the Up method by the following code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
protected override void Up(MigrationBuilder migrationBuilder)
{
    for (int i = 0; i < 30; i++)
    {
        var index = i;
        migrationBuilder.CreateTable(
            name: DateTime.Now.AddDays(-index).ToString("yyyyMMdd"),
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Title = table.Column<string>(nullable: true),
                Content = table.Column<string>(nullable: true),
                CreateDateTime = table.Column<DateTime>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey($"PK_{DateTime.Now.AddDays(-index):yyyyMMdd}", x => x.Id);
            });
    }
}

The change is to make sure we could have enough tables in the database for testing. Please note that we should not use this in production.

Next we can use this command to create and update the database:

1
dotnet ef database update

You would see it generates some tables for the last 30 days in the database.

Validating the mapping

Now it is the time to validate the new mapping. Update the Main method in Program.cs by the following code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
static void Main(string[] args)
{
    DateTime datetime1 = DateTime.Now;
    using (var context = new DynamicContext { CreateDateTime = datetime1 })
    {
        context.Entities.Add(new ConfigurableEntity { Title = "Great News One", Content = $"Hello World! I am the news of {datetime1}", CreateDateTime = datetime1 });
        context.SaveChanges();
    }
    DateTime datetime2 = DateTime.Now.AddDays(-1);
    using (var context = new DynamicContext { CreateDateTime = datetime2 })
    {
        context.Entities.Add(new ConfigurableEntity { Title = "Great News Two", Content = $"Hello World! I am the news of {datetime2}", CreateDateTime = datetime2 });
        context.SaveChanges();
    }

    using (var context = new DynamicContext { CreateDateTime = datetime1 })
    {
        var entity = context.Entities.Single();
        // Writes news of today
        Console.WriteLine($"{entity.Title} {entity.Content} {entity.CreateDateTime}");
    }

    using (var context = new DynamicContext { CreateDateTime = datetime2 })
    {
        var entity = context.Entities.Single();
        // Writes news of yesterday
        Console.WriteLine($"{entity.Title} {entity.Content} {entity.CreateDateTime}");

    }
}

You would see the output like this:

Output

All done! Now we can use the same DbContext to represent different models by passing the CreateDateTime property.

Summary

This demo is to demonstrate how to use IModelCacheKeyFactory to change the default model mapping. Please note that you still need to implement the method to generate the tables respectively. The hosted service is one way to do it. For more information, please visit Background tasks in ASP.NET Core.