Multi-tenancy issues

Hivoltech · March 11, 2025, 6:00am

Hello, I'm testing out Multi-tenancy from a new application and keep getting this error. not sure what to do here.

Thanks

PM> dotnet ef database update -c ApplicationIdentityDbContext
Build started...
Build succeeded.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (8ms) [Parameters=, CommandType='Text', CommandTimeout='30']
SELECT 1
info: Microsoft.EntityFrameworkCore.Migrations[20411]
Acquiring an exclusive lock for migration application. See Migrations Overview - EF Core | Microsoft Learn for more information if this takes too long.
Acquiring an exclusive lock for migration application. See Migrations Overview - EF Core | Microsoft Learn for more information if this takes too long.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (7ms) [Parameters=, CommandType='Text', CommandTimeout='30']
DECLARE @result int;
EXEC @result = sp_getapplock @Resource = '__EFMigrationsLock', @LockOwner = 'Session', @LockMode = 'Exclusive';
SELECT @result
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=, CommandType='Text', CommandTimeout='30']
IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
CREATE TABLE [__EFMigrationsHistory] (
[MigrationId] nvarchar(150) NOT NULL,
[ProductVersion] nvarchar(32) NOT NULL,
CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
);
END;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (0ms) [Parameters=, CommandType='Text', CommandTimeout='30']
SELECT 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (0ms) [Parameters=, CommandType='Text', CommandTimeout='30']
SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=, CommandType='Text', CommandTimeout='30']
SELECT [MigrationId], [ProductVersion]
FROM [__EFMigrationsHistory]
ORDER BY [MigrationId];
info: Microsoft.EntityFrameworkCore.Migrations[20402]
Applying migration '00000000000000_CreateIdentitySchema'.
Applying migration '00000000000000_CreateIdentitySchema'.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (2ms) [Parameters=, CommandType='Text', CommandTimeout='30']
CREATE TABLE [AspNetTenants] (
[Id] int NOT NULL IDENTITY,
[Name] nvarchar(max) NOT NULL,
[Hosts] nvarchar(max) NOT NULL,
CONSTRAINT [PK_Tenants] PRIMARY KEY ([Id])
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=, CommandType='Text', CommandTimeout='30']
CREATE TABLE [AspNetRoles] (
[Id] nvarchar(450) NOT NULL,
[ConcurrencyStamp] nvarchar(max) NULL,
[Name] nvarchar(256) NULL,
[NormalizedName] nvarchar(256) NULL,
[TenantId] int NULL,
CONSTRAINT [PK_AspNetRoles] PRIMARY KEY ([Id]),
CONSTRAINT [FK_AspNetRoles_Tenants_TenantId] FOREIGN KEY ([TenantId]) REFERENCES [AspNetTenants] ([Id]) ON DELETE SET NULL
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=, CommandType='Text', CommandTimeout='30']
CREATE TABLE [AspNetUsers] (
[Id] nvarchar(450) NOT NULL,
[AccessFailedCount] int NOT NULL,
[ConcurrencyStamp] nvarchar(max) NULL,
[Email] nvarchar(256) NULL,
[EmailConfirmed] bit NOT NULL,
[LockoutEnabled] bit NOT NULL,
[LockoutEnd] datetimeoffset NULL,
[NormalizedEmail] nvarchar(256) NULL,
[NormalizedUserName] nvarchar(256) NULL,
[PasswordHash] nvarchar(max) NULL,
[PhoneNumber] nvarchar(max) NULL,
[PhoneNumberConfirmed] bit NOT NULL,
[SecurityStamp] nvarchar(max) NULL,
[TwoFactorEnabled] bit NOT NULL,
[UserName] nvarchar(256) NULL,
[TenantId] int NULL,
CONSTRAINT [PK_AspNetUsers] PRIMARY KEY ([Id]),
CONSTRAINT [FK_AspNetUsers_Tenants_TenantId] FOREIGN KEY ([TenantId]) REFERENCES [AspNetTenants] ([Id]) ON DELETE SET NULL
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (2ms) [Parameters=, CommandType='Text', CommandTimeout='30']
CREATE TABLE [AspNetUserTokens] (
[UserId] nvarchar(450) NOT NULL,
[LoginProvider] nvarchar(450) NOT NULL,
[Name] nvarchar(450) NOT NULL,
[Value] nvarchar(max) NULL,
CONSTRAINT [PK_AspNetUserTokens] PRIMARY KEY ([UserId], [LoginProvider], [Name]),
CONSTRAINT [FK_AspNetUserTokens_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AspNetUsers] ([Id]) ON DELETE CASCADE
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=, CommandType='Text', CommandTimeout='30']
CREATE TABLE [AspNetRoleClaims] (
[Id] int NOT NULL IDENTITY,
[ClaimType] nvarchar(max) NULL,
[ClaimValue] nvarchar(max) NULL,
[RoleId] nvarchar(450) NOT NULL,
CONSTRAINT [PK_AspNetRoleClaims] PRIMARY KEY ([Id]),
CONSTRAINT [FK_AspNetRoleClaims_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [AspNetRoles] ([Id]) ON DELETE CASCADE
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=, CommandType='Text', CommandTimeout='30']
CREATE TABLE [AspNetUserClaims] (
[Id] int NOT NULL IDENTITY,
[ClaimType] nvarchar(max) NULL,
[ClaimValue] nvarchar(max) NULL,
[UserId] nvarchar(450) NOT NULL,
CONSTRAINT [PK_AspNetUserClaims] PRIMARY KEY ([Id]),
CONSTRAINT [FK_AspNetUserClaims_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AspNetUsers] ([Id]) ON DELETE CASCADE
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=, CommandType='Text', CommandTimeout='30']
CREATE TABLE [AspNetUserLogins] (
[LoginProvider] nvarchar(450) NOT NULL,
[ProviderKey] nvarchar(450) NOT NULL,
[ProviderDisplayName] nvarchar(max) NULL,
[UserId] nvarchar(450) NOT NULL,
CONSTRAINT [PK_AspNetUserLogins] PRIMARY KEY ([LoginProvider], [ProviderKey]),
CONSTRAINT [FK_AspNetUserLogins_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AspNetUsers] ([Id]) ON DELETE CASCADE
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=, CommandType='Text', CommandTimeout='30']
CREATE TABLE [AspNetUserRoles] (
[UserId] nvarchar(450) NOT NULL,
[RoleId] nvarchar(450) NOT NULL,
CONSTRAINT [PK_AspNetUserRoles] PRIMARY KEY ([UserId], [RoleId]),
CONSTRAINT [FK_AspNetUserRoles_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [AspNetRoles] ([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_AspNetUserRoles_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AspNetUsers] ([Id]) ON DELETE CASCADE
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=, CommandType='Text', CommandTimeout='30']
CREATE INDEX [RoleNameIndex] ON [AspNetRoles] ([NormalizedName]) WHERE [NormalizedName] IS NOT NULL;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=, CommandType='Text', CommandTimeout='30']
CREATE INDEX [IX_AspNetRoleClaims_RoleId] ON [AspNetRoleClaims] ([RoleId]);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=, CommandType='Text', CommandTimeout='30']
CREATE INDEX [IX_AspNetUserClaims_UserId] ON [AspNetUserClaims] ([UserId]);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=, CommandType='Text', CommandTimeout='30']
CREATE INDEX [IX_AspNetUserLogins_UserId] ON [AspNetUserLogins] ([UserId]);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=, CommandType='Text', CommandTimeout='30']
CREATE INDEX [IX_AspNetUserRoles_RoleId] ON [AspNetUserRoles] ([RoleId]);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=, CommandType='Text', CommandTimeout='30']
CREATE INDEX [EmailIndex] ON [AspNetUsers] ([NormalizedEmail]);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=, CommandType='Text', CommandTimeout='30']
CREATE INDEX [UserNameIndex] ON [AspNetUsers] ([NormalizedUserName]) WHERE [NormalizedUserName] IS NOT NULL;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (0ms) [Parameters=, CommandType='Text', CommandTimeout='30']
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'00000000000000_CreateIdentitySchema', N'9.0.2');
Applying migration '20250311055131_ExtendApplicationUser'.
info: Microsoft.EntityFrameworkCore.Migrations[20402]
Applying migration '20250311055131_ExtendApplicationUser'.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (0ms) [Parameters=, CommandType='Text', CommandTimeout='30']
DECLARE @result int;
EXEC @result = sp_releaseapplock @Resource = '__EFMigrationsLock', @LockOwner = 'Session';
SELECT @result
System.InvalidOperationException: To change the IDENTITY property of a column, the column needs to be dropped and recreated.
at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(AlterColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.<>c.<.cctor>b__83_4(MigrationsSqlGenerator g, MigrationOperation o, IModel m, MigrationCommandListBuilder b)
at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(IReadOnlyList1 operations, IModel model, MigrationsSqlGenerationOptions options) at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(IReadOnlyList1 operations, IModel model, MigrationsSqlGenerationOptions options)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateUpSql(Migration migration, MigrationsSqlGenerationOptions options)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c__DisplayClass25_2.b__2()
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateImplementation(DbContext context, String targetMigration, MigrationExecutionState state, Boolean useTransaction)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c.b__20_1(DbContext c, ValueTuple4 s) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
To change the IDENTITY property of a column, the column needs to be dropped and recreated.
PM>

enchev · March 11, 2025, 6:09am

There are most probably some kind of migrations (security tables already exist) applied already to this database that cannot be detected by EF API and you get this error during our security migrations attempt to execute.

Hivoltech · March 11, 2025, 11:20pm

I'm starting with a blank database and a new application, and this repeatedly happens, order of events:

create a new application.
Add the database.
add security with Multitenancy
Project won't run.
no tables in the database to drop and recreate

Not sure how to get this going

Thanks

Rob

enchev · March 12, 2025, 5:36am

Just tried the same and it worked normally:

I was able to scaffold normally all security pages, migrations, etc. and I was able to login normally with tenantsadmin user (seeded on first run):

There is a warning in .NET 9 that can be ignored like this:

Hivoltech · March 12, 2025, 9:11pm

That fixed it,

Thanks

Rob