问题

Here is the translated text:

有没有人知道如何配置时间表以适应自定义模式和LINQ？
以下是T-SQL，它在[dbo]模式下正常工作，但在像[pm]这样的自定义模式下不起作用。

我的开发环境：

• .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: )

参考链接：
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/

以下是重现的步骤：

• 创建一个新数据库
• 创建一个带有SYSTEM_VERSIONING = ON的新表
• 为书签创建一个简单的EF Core模型
• 配置时间表

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

• 使用LINQ将一些值插入到Bookmark表中

T-SQL

-------------------------------------
--创建数据库
CREATE DATABASE [SampleDb]
GO
USE [SampleDb]
GO
--创建自定义模式
CREATE SCHEMA [pm] AUTHORIZATION [dbo]
GO
--创建一个带有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
--这是使用自定义模式生成的LINQ --> [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
--查询结果
SELECT *,[PeriodStart],[PeriodEnd] FROM [pm].[Bookmark]
GO
SELECT * FROM [pm].[BookmarkHistory]
--------------------
 
**问题：** 历史表中没有数据

[请参考此证据](https://i.stack.imgur.com/1MuSq.png)

<details>
<summary>英文:</summary>

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 &lt;X64&gt; (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 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](https://i.stack.imgur.com/1MuSq.png)
</details>
# 答案1
**得分**: 0
要配置EF Core以与自定义模式和时间表一起工作，您可以在您的DbContext类中覆盖OnModelCreating方法，并使用ToTable和IsTemporal方法指定模式和时间表配置：
```csharp
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Entity<Bookmark>()
.ToTable("Bookmark", "pm", b => b.IsTemporal());
}

这将配置Bookmark实体以使用pm模式中的Bookmark表，并将其视为时间表。

您还需要在Bookmark类中指定PeriodStart和PeriodEnd属性，并使用[DatabaseGenerated(DatabaseGeneratedOption.Identity)]属性将它们标记为隐藏：

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将能够正确地从pm模式中的Bookmark时间表中插入和检索数据。

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

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; }
}