I am using a partitioned view (SQL Server 2012 STD Edition) to split my large Trn table based on date

I followed the following steps

First created several tables (Trn202425, Trn202324, Trn202223 etc) with the following schema

public class Trn202425
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    [ForeignKey("Branch")]
    public string BrnCd { get; set; }
    public BranchMaster Branch { get; set; }

    public DateTime TrnDt { get; set; }
    public string TrnNo { get; set; }
 }

Configured the model with

builder.ToTable("Trn202425");
builder.HasCheckConstraint("CK_Trn202425_TrnDt", "[TrnDt] >= '2024-04-01' And [TrnDt] <='2025-03-31'");

builder.ToTable("Trn202324");
builder.HasCheckConstraint("CK_Trn202324_TrnDt", "[TrnDt] >= '2023-04-01' And [TrnDt] <='2024-03-31'");

builder.ToTable("Trn202223");
builder.HasCheckConstraint("CK_Trn202223_TrnDt", "[TrnDt] >= '2022-04-01' And [TrnDt] <='2023-03-31'");

Used the Migration to create the above tables in Database

Then created the View

public class TrnMain
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    [ForeignKey("Branch")]
    public string BrnCd { get; set; }
    public BranchMaster Branch { get; set; }

    public DateTime TrnDt { get; set; }
    public string TrnNo { get; set; }
}

And configured it with

builder.ToView("TrnMain");

Created a view in the Database [Manually]

CREATE VIEW [dbo].[TrnMain]
WITH SCHEMABINDING
AS
SELECT TrnDt, TrnNo, Id, BrnCd FROM dbo.TrnMain202425
UNION ALL
SELECT TrnDt, TrnNo, Id, BrnCd FROM dbo.TrnMain202324
UNION ALL
SELECT TrnDt, TrnNo, Id, BrnCd FROM dbo.TrnMain202223

Now while inserting the values using EF Core

The entity type 'TrnMain' is not mapped to a table, therefore the entities cannot be persisted to the database. Use 'ToTable' in 'OnModelCreating' to map it.

I changed the line

builder.ToView("TrnMain"); 

To

builder.ToTable("TrnMain");

And now it is working.

My question is am I doing it correctly or is there any other way to implement the partitioned view

Using .NET 5 & EF Core 5

I am using a partitioned view (SQL Server 2012 STD Edition) to split my large Trn table based on date

I followed the following steps

First created several tables (Trn202425, Trn202324, Trn202223 etc) with the following schema

public class Trn202425
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    [ForeignKey("Branch")]
    public string BrnCd { get; set; }
    public BranchMaster Branch { get; set; }

    public DateTime TrnDt { get; set; }
    public string TrnNo { get; set; }
 }

Configured the model with

builder.ToTable("Trn202425");
builder.HasCheckConstraint("CK_Trn202425_TrnDt", "[TrnDt] >= '2024-04-01' And [TrnDt] <='2025-03-31'");

builder.ToTable("Trn202324");
builder.HasCheckConstraint("CK_Trn202324_TrnDt", "[TrnDt] >= '2023-04-01' And [TrnDt] <='2024-03-31'");

builder.ToTable("Trn202223");
builder.HasCheckConstraint("CK_Trn202223_TrnDt", "[TrnDt] >= '2022-04-01' And [TrnDt] <='2023-03-31'");

Used the Migration to create the above tables in Database

Then created the View

public class TrnMain
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    [ForeignKey("Branch")]
    public string BrnCd { get; set; }
    public BranchMaster Branch { get; set; }

    public DateTime TrnDt { get; set; }
    public string TrnNo { get; set; }
}

And configured it with

builder.ToView("TrnMain");

Created a view in the Database [Manually]

CREATE VIEW [dbo].[TrnMain]
WITH SCHEMABINDING
AS
SELECT TrnDt, TrnNo, Id, BrnCd FROM dbo.TrnMain202425
UNION ALL
SELECT TrnDt, TrnNo, Id, BrnCd FROM dbo.TrnMain202324
UNION ALL
SELECT TrnDt, TrnNo, Id, BrnCd FROM dbo.TrnMain202223

Now while inserting the values using EF Core

The entity type 'TrnMain' is not mapped to a table, therefore the entities cannot be persisted to the database. Use 'ToTable' in 'OnModelCreating' to map it.

I changed the line

builder.ToView("TrnMain"); 

To

builder.ToTable("TrnMain");

And now it is working.

My question is am I doing it correctly or is there any other way to implement the partitioned view

Using .NET 5 & EF Core 5

• c#
• sql-server
• asp-core
• entity-framework-core

• What you are doing sounds like an exceedingly bad idea. Why do you need a new table for every month? It's just going to get awful to manage. Also .NET 5 and EF Core 5 are both way out of date and unsupported, is there any reason you haven't upgraded? – Charlieface Commented Nov 16, 2024 at 18:35
• For partitioned views I'd consider managing the tables external to EF along with the view and just set up the transaction entity pointing at the view using ToTable as you are doing. You can specify a "Table" mapping to a view which essentially tells EF "You can select and insert etc. through this". It is up to the DB Engine whether inserts, updates, & deletes can be performed against the view or not. AFAIK you can insert and update rows automatically across partitions through the view. – Steve Py Commented Nov 17, 2024 at 5:07
• @Charlieface - we're going to play one of my favorite games. It's called "what is that feature for?". Today, it takes the form of "what is the use case for partitioned views?". – Ben Thul Commented Nov 17, 2024 at 16:00
• @BenThul An existing database which has a few separate tables for disparate entities, and you want to present a unified view of them, and be able to modify throught that view. It's not for unioning monthly tables, as that is going to get unmanageable quickly. It also is no better perf-wise than Table Partitioning when working from a fresh slate, and usually both options are a bad idea and don't do what people think they do. – Charlieface Commented Nov 17, 2024 at 16:22
• @Charlieface Those were not monthly, but yearly tables. Each year has more than 10 Million rows, That is the reason why i am consider splitting it.. And also no update/deletes/inserts are allowed on previous years records. – vcs Commented Nov 18, 2024 at 6:10

1 Answer 1

You can consider use SQL Server Partitioned tables.

Since EF Core doesn't generate the partitioning logic in migrations. You need to manage partitioning yourself via custom SQL scripts during database setup (handled through SQL Server directly). Then, use EF Core to map and query and manipulating partitioned tables.

When query the partitioned tables, you could use EF core to execute SQL Queries or work with Stored Procedure.