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

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:

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

Expected behavior

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

About this issue

Original URL
State: closed
Created 5 years ago
Reactions: 2
Comments: 16 (8 by maintainers)

Most upvoted comments

@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.

douglasg14b on Apr 7, 2020

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?

dush-a on Apr 7, 2020

yes even with the updated configuration:

services.AddDefaultIdentity<User>(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.

andre-dasilva on Oct 23, 2019