How to modify the existing IdentityUser Column size in code first approach

Question

Im using code first approach. Always we don't prefer to add NVarchar(max) in our tables. but when we use the identity account management by default column in AspNetUser table contains NVarchar(max) as in below image. my team feels having varchar(Max) will reduce the performance.

In Database

In code

Now my question are

1) Is that necessary to have varchar(max) in that column?

2) can we reduce size of that column?

3) If yes, how to reduce that columns size?

4) Is that really gives such a performance issue when having Varchar(Max)? (I know why we should not use that but guide me, Is that such a big problem)

many articles explain only how to include the new columns in that table but not to modify the existing columns. kindly assist me.

Sorry, if you don't understand my question, please ask me.

Answer 1

You can use fluent config to set a length:

public class ApplicationUser : IdentityUser
{
    ...

    public class Mapping : EntityTypeConfiguration<ApplicationUser>
    {
        Property(m => m.PasswordHash).HasMaxLength(50)
        Property(m => m.SecurityStamp).HasMaxLength(50)
        Property(m => m.PhoneNumber).HasMaxLength(50)
    }
}

Then, in your context:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    ...

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new ApplicationUser.Mapping());
    }
}

For the exact column lengths to use, you'll likely need to do some testing. I don't think the PasswordHash or SecurityStamp values have a fixed length, per se, so you'll need to discover some upward limit. PhoneNumber is fairly straight-forward, but if you're dealing with international users, remember that it's only 10 digits in the U.S. Other countries utilize a wide-range of differing phone number formats and lengths.

That said, though, this is mostly a waste of time, energy and effort. While there is a theoretical performance impact incurred by utilizing MAX versus a defined length, the impact is negligible, and will not be noticeable or truly even measurable in practice. The only real downside to using MAX is that you cannot apply an index on that column. However, that only matters if you intend to query by that column, which is highly unlikely for the three columns you're concerned about. Identity always looks up the user by id, username or email, never by PasswordHash or SecurityStamp, and how often will you likely look up a user by their phone number?

Long and short, all you're really doing here is introducing a potential maintenance concern for yourself. You'll need to henceforth keep an eye on these columns and make sure not only that you have chosen a good length to begin with, but also that future versions of Identity do not make some modification that affects the length.