Aspnetcore: Default identity causes composite keys in EF Core that are too large

[jez9999]

Describe the bug

When I create a DB context based on the default ASP.NET identity:

public class SimplyClickDbContext : IdentityDbContext {
    public SimplyClickDbContext(DbContextOptions<SimplyClickDbContext> options) : base(options) { }
}

... then scaffold a DB migration to implement it, and script it to SQL, I get the following warnings from SQL Server:

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_AspNetUserRoles' 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 2700 bytes. For some combination of large values, the insert/update operation will fail.

These composite key fields are nvarchars that should be limited in length so as to stay within the 900 byte limit.

To Reproduce

Steps to reproduce the behavior:

1. Create DB context based on IdentityDbContext
2. Scaffold DB migration: dotnet ef migrations add InitialMigration
3. Generate SQL for migration: dotnet ef migrations script 0 -o migration.sql
4. Run SQL on database
5. See warnings

Expected behavior

When you run the SQL on the database, no warnings should appear.

[HaoK]

Can you tell us what version of asp.net core as you using?

[jez9999]

v3.0.0.

[andre-dasilva]

Just got the same problem with v.3.0.0
Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_AspNetUserRoles' 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 1412 bytes. For some combination of large values, the insert/update operation will fail.

Plus some index errors
CREATE INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

[HaoK]

Are you calling AddDefaultIdentity still? Can you try ensuring that your identity options have configured: o.Stores.MaxLengthForKeys = 128

[andre-dasilva]

yes even with the updated configuration:
```c#
services.AddDefaultIdentity(options =>
{
options.Password.RequireDigit = false;
options.Password.RequiredLength = 1;
options.Password.RequiredUniqueChars = 0;
options.Password.RequireLowercase = false;
options.Password.RequireUppercase = false;
options.Password.RequireNonAlphanumeric = false;
options.Stores.MaxLengthForKeys = 128;
})

I get the warnings:

Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_AspNetUserRoles' 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 1412 bytes. For some combination of large values, the insert/update operation will fail.

CREATE INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
```

[HaoK]

Putting this in 5.0 so it shows up in my filters

[dush-a]

I am having the same problem with ASP.NET Core 3.1.

1. Create a ASP.NET Core MVC project with individual user accounts. Running the app worked well as expected. During the development it used LocalDB.
2. Before deploying to production I created the DB Schema using the script-migration and Executed on the SQL server. I got only 2 errors.

_Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_AspNetUserRoles' 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 1412 bytes. For some combination of large values, the insert/update operation will fail._

When you look at the script it clearly shows why it gives this error:

`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
);

GO

CREATE TABLE [AspNetUserTokens] (
[UserId] nvarchar(450) NOT NULL,
[LoginProvider] nvarchar(128) NOT NULL,
[Name] nvarchar(128) 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
);

GO`

Why do we need to have nvarchar(450) for userId and RoleId ?

This warning is not limited to SQL Server, the same warning message gives if I execute the script against the LocalDB as well.

[HaoK]

@ajcvickers so I looked into this, and when we added the MaxLengthForKeys, we applied them to most of the strings, but not the primary keys themselves, so they still have the default lengths. We could apply the max key length to the primary keys too, is there any downside to doing that (UserId and RoleId)?

[ajcvickers]

@HaoK Only that is is a schema change.

[HaoK]

So basically not worth at this point? They can always hand edit the migration to change all the lengths to 256 as a workaround right?

[ajcvickers]

@HaoK Yes, it's probably better for people to change the column length themselves, either directly in the migration, or by changing the EF model and scaffolding a new migration.

[HaoK]

Sounds good, closing this as won't fix

[dush-a]

So, then what should we need to do? Change the lengths to 225 or 256?
This issue is there for a long time, even in 2017 you guys have closed the issue telling that it will be fixed in next preview. Now you are closing this again without fixing it.

Why don't you change it in the template or the migration script (at least in 5.0.0-preview2) so that people who use this template will not want to search the internet until they find this thread in the future?

[douglasg14b]

@ajcvickers @HaoK

If this problem still exists, is known, and has existed for years, will not be fixed. Why is it not documented as a common "gotcha"?

If the column sizes are literally wrong, why maintain that? The expectation that an astute user should manually fix what they are led to believe as a fully compliant operation seems like a misguided conclusion no?

---

Also setting the lengths to 256 as mentioned results in a 1024 bit key, not a 900 bit key. 225 is the correct length.

On AspNetUserTokens which has a clustered index of 3 columns, 225 is ofc too large there and should be 150. But wait, the UserId is a FK of 225, either I change the AspNetUser.Id to be 150 as well, or I keep it at 225 and change AspNetUserTokens.LoginProvider and AspNetUserTokens.Name to 112 each....

Cripes that's a lot of unnecessary friction.

[firstdivision]

better for people to change the column length themselves, either directly in the migration, or by changing the EF model and scaffolding a new migration

@ajcvickers @HaoK

How do I modify the model classes of the Identity objects like AspNetUserLogins, AspNetUserTokens, etc? They're buried inside the NuGet package, right?

[douglasg14b]

@firstdivision

This is what I did

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder)
    modelBuilder.Entity<AppUser>().Property(x => x.Id).HasMaxLength(225);
    modelBuilder.Entity<IdentityRole>().Property(x => x.Id).HasMaxLength(225);
    modelBuilder.Entity<IdentityUserLogin<string>>().Property(x => x.ProviderKey).HasMaxLength(225);
    modelBuilder.Entity<IdentityUserLogin<string>>().Property(x => x.LoginProvider).HasMaxLength(225);
    modelBuilder.Entity<IdentityUserToken<string>>().Property(x => x.Name).HasMaxLength(112);
    modelBuilder.Entity<IdentityUserToken<string>>().Property(x => x.LoginProvider).HasMaxLength(112);
}