The Temporal tables is not working with the custom schema and LINQ

Question

Does everyone know how to config the temporal tables to work with a custom schema and LINQ? Below is the T-SQL, it is working fine with [dbo] schema but not working with a custom schema like [pm].

My dev environents:

• .NET 6
• Microsoft SQL Server 2019 (RTM-GDR) (KB5014356) - 15.0.2095.3 (X64) Apr 29 2022 18:00:13 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 10.0 (Build 19044: )

References https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16 https://devblogs.microsoft.com/dotnet/prime-your-flux-capacitor-sql-server-temporal-tables-in-ef-core-6-0/

The following steps to reproduce:

• create a new database
• create a new table with SYSTEM_VERSIONING = ON
• create a simple EF Core model for Bookmark
• configuring a temporal table

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<Bookmark>()
        .ToTable("Bookmark", b => b.IsTemporal());
}

• Insert some value into Bookmark table using LINQ

T-SQL

-------------------------------------
--Create a database
CREATE DATABASE [SampleDb]
GO
USE [SampleDb]
GO
--create a custom schema
CREATE SCHEMA [pm] AUTHORIZATION [dbo]
GO
--create a new table with SYSTEM_VERSIONING = ON
CREATE TABLE [pm].[Bookmark](
    [Id] [uniqueidentifier] NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
    [PeriodStart] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT '0001-01-01T00:00:00.0000000', 
    [PeriodEnd] DATETIME2  GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT '9999-12-31T23:59:59.9999999',
    PERIOD FOR SYSTEM_TIME ([PeriodStart], [PeriodEnd]),
 CONSTRAINT [Pk_Bookmark_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [pm].[BookmarkHistory], DATA_CONSISTENCY_CHECK = ON))
GO
--this is the LINQ generated with custom schema --> [pm].[Bookmark]
EXEC sp_executesql N'SET NOCOUNT ON;
INSERT INTO [pm].[Bookmark] ([Id],[Description])
VALUES (@p0, @p1);
SELECT [PeriodEnd], [PeriodStart]
FROM [pm].[Bookmark]
WHERE @@ROWCOUNT = 1 AND [Id] = @p0;',
N'@p0 uniqueidentifier,@p1 nvarchar(4000)'
,@p0='3ED9066F-4902-4626-A16B-BC3594308AFB',@p1='test'
GO
--query result
SELECT *,[PeriodStart],[PeriodEnd] FROM [pm].[Bookmark]
GO
SELECT * FROM [pm].[BookmarkHistory]
--------------------

**Problem:** the history table doesn't have data

Please see this evidence

Answer 1

To configure EF Core to work with a custom schema and temporal tables, you can override the OnModelCreating method in your DbContext class and specify the schema and temporal table configuration using the ToTable and IsTemporal methods:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<Bookmark>()
        .ToTable("Bookmark", "pm", b => b.IsTemporal());
}

This will configure the Bookmark entity to use the Bookmark table in the pm schema, and to treat it as a temporal table.

You will also need to specify the PeriodStart and PeriodEnd properties in your Bookmark class, and mark them as hidden using the [DatabaseGenerated(DatabaseGeneratedOption.Identity)] attribute:

public class Bookmark
{
    public Guid Id { get; set; }
    public string Description { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public DateTime PeriodStart { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public DateTime PeriodEnd { get; set; }
}

EF Core will be able to properly insert and retrieve data from the Bookmark temporal table in the pm schema.