I'm trying to deploy my ASP.NET Core website on a production server, hosted with Plesk. I have 2 projects in my solution: 1 containing my DbContext, 1 containing my web project.
I generated migrations for my DbContext:
dotnet ef migrations add AddIdentity --project ..\MyProject.Data
Then I generated a production migration script as mentioned in the docs (https://learn.microsoft.com/en-us/ef/core/miscellaneous/cli/dotnet#dotnet-ef-migrations-script)
dotnet ef migrations script --idempotent --output "MigrationScripts\AddIdentity.sql" --context MyDbContext --project ..\MyProject.Data
Then I navigated to the SQL utility on my production server (MyLittleAdmin) and pasted the generated SQL script in it. This succeeds but I get the following error:
Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_AspNetUserLogins' has maximum length of 1800 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_AspNetUserTokens' has maximum length of 1804 bytes. For some combination of large values, the insert/update operation will fail.
If ignoring this error, I would probably get in trouble later on with queries failing for "no apparent reason" (this reason).
My code is very straightforward:
internal class MintPlayerContext : IdentityDbContext<User, Role, int>
{
private readonly IConfiguration configuration;
public MintPlayerContext(IConfiguration configuration) : base()
{
this.configuration = configuration;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
optionsBuilder.UseSqlServer(configuration.GetConnectionString("MintPlayer"), options => options.MigrationsAssembly("MintPlayer.Data"));
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
}
}
internal class User : IdentityUser<int>
{
public string PictureUrl { get; set; }
}
internal class Role : IdentityRole<int>
{
}
I also tried using Guid's (I want to use Guids eventually) but even with int's MyLittleAdmin complaints about the Key being too long. I already checked on Google but this only gives diagnostic articles and does not really provide a solution.
Right now this is the AspNetUsers section of the migration:
CREATE TABLE [AspNetUsers] (
[Id] int NOT NULL IDENTITY,
[UserName] nvarchar(256) NULL,
[NormalizedUserName] nvarchar(256) NULL,
[Email] nvarchar(256) NULL,
[NormalizedEmail] nvarchar(256) NULL,
[EmailConfirmed] bit NOT NULL,
[PasswordHash] nvarchar(max) NULL,
[SecurityStamp] nvarchar(max) NULL,
[ConcurrencyStamp] nvarchar(max) NULL,
[PhoneNumber] nvarchar(max) NULL,
[PhoneNumberConfirmed] bit NOT NULL,
[TwoFactorEnabled] bit NOT NULL,
[LockoutEnd] datetimeoffset NULL,
[LockoutEnabled] bit NOT NULL,
[AccessFailedCount] int NOT NULL,
[PictureUrl] nvarchar(max) NULL,
CONSTRAINT [PK_AspNetUsers] PRIMARY KEY ([Id])
);
What can I do to solve this?
Edit:
I tried modifying my DbContext configuration to force the Key to be only the Id:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<User>().HasKey(u => u.Id);
modelBuilder.Entity<Role>().HasKey(r => r.Id);
}
This seems to affect the migration indeed:
[Id] int NOT NULL IDENTITY,
Instead of:
[Id] uniqueidentifier NOT NULL,
But it still gives the same error
Edit: Adding the AspNetUserLogins, AspNetUserRoles, stripped the if's
CREATE TABLE [AspNetUserLogins] (
[LoginProvider] nvarchar(450) NOT NULL,
[ProviderKey] nvarchar(450) NOT NULL,
[ProviderDisplayName] nvarchar(max) NULL,
[UserId] int NOT NULL,
CONSTRAINT [PK_AspNetUserLogins] PRIMARY KEY ([LoginProvider], [ProviderKey]),
CONSTRAINT [FK_AspNetUserLogins_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AspNetUsers] ([Id]) ON DELETE CASCADE
);
CREATE TABLE [AspNetUserTokens] (
[UserId] int 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
);
Thanks everyone, It was spot-on. Thanks to all of your suggestions I managed to get it working.
Since I'm using Code-first, I can't just modify my generated migrations. So this is what I've done:
internal class MintPlayerContext : IdentityDbContext<User, Role, int>
{
// dotnet ef migrations add AddIdentity --project ..\MyProject.Data
// dotnet ef database update --project ..\MyProject.Data
// (only looks at your appsettings.json file, not your appsettings.*.json, so for local development)
// To generate production migrations
// dotnet ef migrations script --idempotent --output "MigrationScripts\AddIdentity.sql" --context MyDbContext --project ..\MyProject.Data
private readonly IConfiguration configuration;
public MintPlayerContext(IConfiguration configuration) : base()
{
this.configuration = configuration;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
optionsBuilder.UseSqlServer(configuration.GetConnectionString("MyProject"), options => options.MigrationsAssembly("MyProject.Data"));
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Microsoft.AspNetCore.Identity.IdentityUserLogin<int>>()
.Property(ut => ut.LoginProvider)
.HasMaxLength(50);
modelBuilder.Entity<Microsoft.AspNetCore.Identity.IdentityUserLogin<int>>()
.Property(ut => ut.ProviderKey)
.HasMaxLength(200);
modelBuilder.Entity<Microsoft.AspNetCore.Identity.IdentityUserToken<int>>()
.Property(ut => ut.LoginProvider)
.HasMaxLength(50);
modelBuilder.Entity<Microsoft.AspNetCore.Identity.IdentityUserToken<int>>()
.Property(ut => ut.Name)
.HasMaxLength(50);
}
}
I will be using Guid's in order to not make UserIds guess-able.