Temporal Table Generates With Identity Column

[amurchison-hsid]

Bug description

Hi,

I have 2 entities that are related in a many-to-many relationship, User and Group:

[Table("Group")]
public class Group : BaseEntity, ITemporalEntity
{
    public string Name { get; set; }
    public string Status { get; set; }
    public List<GroupUser> GroupUsers { get; set; }
    public Group() { }
}

Previously the entities were related only by a pair of skip navigations (Group had a list of Users and visa versa). Along with switching to the explicit join entity mapping, GroupUser now also inherits from BaseEntity, giving it a primary key "Id", and some audit columns.

Unfortunately, the script that is being generated to apply the changes to the database is attempting to add an identity column to the existing history table (GroupUserHistory). Being that history tables are not allowed to contain identity columns, I would expect Entity Framework to never attempt to assign an identity column to a history table.

Attempting to run the script throws this error:

Setting SYSTEM_VERSIONING to ON failed because history table 'CoreAutomateDB.dbo.GroupUserHistory' has IDENTITY column specification. Consider dropping all IDENTITY column specifications and trying again.

Your code

BEGIN TRANSACTION;
ALTER TABLE [GroupUser] DROP CONSTRAINT [FK_GroupUser_Group_GroupsId];

ALTER TABLE [GroupUser] DROP CONSTRAINT [FK_GroupUser_User_UsersId];

ALTER TABLE [GroupUser] SET (SYSTEM_VERSIONING = OFF)


ALTER TABLE [GroupUser] DROP CONSTRAINT [PK_GroupUser];

EXEC sp_rename N'[GroupUser].[PeriodStart]', N'UTCPeriodStart', 'COLUMN';

EXEC sp_rename N'[GroupUserHistory].[PeriodStart]', N'UTCPeriodStart', 'COLUMN';

EXEC sp_rename N'[GroupUser].[PeriodEnd]', N'UTCPeriodEnd', 'COLUMN';

EXEC sp_rename N'[GroupUserHistory].[PeriodEnd]', N'UTCPeriodEnd', 'COLUMN';

EXEC sp_rename N'[GroupUser].[UsersId]', N'UserId', 'COLUMN';

EXEC sp_rename N'[GroupUserHistory].[UsersId]', N'UserId', 'COLUMN';

EXEC sp_rename N'[GroupUser].[GroupsId]', N'GroupId', 'COLUMN';

EXEC sp_rename N'[GroupUserHistory].[GroupsId]', N'GroupId', 'COLUMN';

EXEC sp_rename N'[GroupUser].[IX_GroupUser_UsersId]', N'IX_GroupUser_UserId', 'INDEX';

ALTER TABLE [GroupUser] ADD [Id] int NOT NULL IDENTITY;

-- This should just not say IDENTITY for a temporal table.
ALTER TABLE [GroupUserHistory] ADD [Id] int NOT NULL IDENTITY;

ALTER TABLE [GroupUser] ADD [Created] datetimeoffset NOT NULL DEFAULT '0001-01-01T00:00:00.0000000+00:00';

ALTER TABLE [GroupUserHistory] ADD [Created] datetimeoffset NOT NULL DEFAULT '0001-01-01T00:00:00.0000000+00:00';

ALTER TABLE [GroupUser] ADD [CreatedBy] nvarchar(255) NULL;

ALTER TABLE [GroupUserHistory] ADD [CreatedBy] nvarchar(255) NULL;

ALTER TABLE [GroupUser] ADD [LastModified] datetimeoffset NULL;

ALTER TABLE [GroupUserHistory] ADD [LastModified] datetimeoffset NULL;

ALTER TABLE [GroupUser] ADD [LastModifiedBy] nvarchar(255) NULL;

ALTER TABLE [GroupUserHistory] ADD [LastModifiedBy] nvarchar(255) NULL;

ALTER TABLE [GroupUser] ADD CONSTRAINT [PK_GroupUser] PRIMARY KEY ([Id]);

CREATE INDEX [IX_GroupUser_GroupId] ON [GroupUser] ([GroupId]);

ALTER TABLE [GroupUser] ADD CONSTRAINT [FK_GroupUser_Group_GroupId] FOREIGN KEY ([GroupId]) REFERENCES [Group] ([Id]);

ALTER TABLE [GroupUser] ADD CONSTRAINT [FK_GroupUser_User_UserId] FOREIGN KEY ([UserId]) REFERENCES [User] ([Id]);

DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'ALTER TABLE [GroupUser] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[GroupUserHistory]))')


INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20250501172924_GroupUserExplicitManyToMany', N'9.0.3');

COMMIT;
GO

Stack traces

Setting SYSTEM_VERSIONING to ON failed because history table 'CoreAutomateDB.dbo.GroupUserHistory' has IDENTITY column specification. Consider dropping all IDENTITY column specifications and trying again.

Verbose output

EF Core version

9.0.3

Database provider

No response

Target framework

No response

Operating system

No response

IDE

No response