c# -> Entity Framework -> Asp.Net Identity -> Set custom data types

Question

Since Entity Framework 6.1.3 uses nvarchar(MAX) as default for strings I wan't to change this to nvarchar(256) instead. You can read some more about why nvarchar(MAX) hurts performance on the link below. Short answer is Indexes.

https://dba.stackexchange.com/questions/48408/ef-code-first-uses-nvarcharmax-for-all-strings-will-this-hurt-query-performan

I have done this by adding this line to OnModelCreating:

modelBuilder.Properties<string>().Configure(s => 
    s.HasMaxLength(256).HasColumnType("nvarchar"));

However this also affects the columns created automatically by IdentityDbContext (Individual user accounts).

Specifically dbo.AspNetUsers.PasswordHash, dbo.AspNetUsers.SecurityStamp, dbo.AspNetUserClaims.ClaimType and dbo.AspNetUserClaims.ClaimValue. For these I still wan't to use nvarchar(MAX).

For properties that I have access to I can use the column attribute and this will be fixed. Example:

[Column(TypeName = "nvarchar(MAX)")]
public string CaseComment { get; set; }

I guess I have to use fluent API to solve this but how can I do that?

Answer 1

Found my answer here:

https://learn.microsoft.com/en-us/ef/core/modeling/relational/columns

modelBuilder.Entity<ApplicationUser>()
    .Property(b => b.PasswordHash)
    .HasColumnType("nvarchar(MAX)")
    .HasMaxLength(null);

modelBuilder.Entity<ApplicationUser>()
    .Property(b => b.SecurityStamp)
    .HasColumnType("nvarchar(MAX)")
    .HasMaxLength(null);

modelBuilder.Entity<ApplicationUserClaim>()
    .Property(b => b.ClaimType)
    .HasColumnType("nvarchar(MAX)")
    .HasMaxLength(null);

modelBuilder.Entity<ApplicationUserClaim>()
    .Property(b => b.ClaimValue)
    .HasColumnType("nvarchar(MAX)")
    .HasMaxLength(null);

ApplicationUserClaim is because we use custom claims in our application. The default would be:

modelBuilder.Entity<IdentityUserClaim>()
    .Property(b => b.ClaimType)
    .HasColumnType("nvarchar(MAX)");

modelBuilder.Entity<IdentityUserClaim>()
    .Property(b => b.ClaimValue)
    .HasColumnType("nvarchar(MAX)");

Update 2

Using a override for all strings like this:

modelBuilder.Properties<string>().Configure(s => 
    s.HasMaxLength(256).HasColumnType("nvarchar"));

and then modifying properties with attributes like this:

[Column(TypeName = "nvarchar(MAX)")]
public string CaseComment { get; set; }

Or this:

modelBuilder.Entity<IdentityUserClaim>()
    .Property(b => b.ClaimType)
    .HasColumnType("nvarchar(MAX)");

This can cause the Exception. Validation failed for one or more entities. See 'EntityValidationErrors' property for more details. Even though the column is of correct data type Entity Framework still thinks it is nvarchar(256) and throws the error DbEntityValidationException.

To fix this use the following instead:

[Column(TypeName = "nvarchar(MAX)")]
[MaxLength]
public string CaseComment { get; set; }

modelBuilder.Entity<IdentityUserClaim>()
    .Property(b => b.ClaimType)
    .HasColumnType("nvarchar(MAX)")
    .HasMaxLength(null);

Update:

If you have a standard ApplicationUser and wan't to keep it as is but still wan't nvarchar(256) as standard you can use these settings:

modelBuilder.Entity<ApplicationUser>()
    .Property(b => b.Id)
    .HasColumnType("nvarchar")
    .HasMaxLength(128);

modelBuilder.Entity<ApplicationUser>()
    .Property(b => b.PasswordHash)
    .HasColumnType("nvarchar(MAX)")
    .HasMaxLength(null);

modelBuilder.Entity<ApplicationUser>()
    .Property(b => b.SecurityStamp)
    .HasColumnType("nvarchar(MAX)")
    .HasMaxLength(null);

modelBuilder.Entity<ApplicationUser>()
    .Property(b => b.PhoneNumber)
    .HasColumnType("nvarchar(MAX)")
    .HasMaxLength(null);

modelBuilder.Entity<IdentityRole>()
    .Property(b => b.Id)
    .HasColumnType("nvarchar")
    .HasMaxLength(128);

modelBuilder.Entity<IdentityUserRole>()
    .Property(b => b.UserId)
    .HasColumnType("nvarchar")
    .HasMaxLength(128);

modelBuilder.Entity<IdentityUserRole>()
    .Property(b => b.RoleId)
    .HasColumnType("nvarchar")
    .HasMaxLength(128);

modelBuilder.Entity<IdentityUserLogin>()
    .Property(b => b.UserId)
    .HasColumnType("nvarchar")
    .HasMaxLength(128);

modelBuilder.Entity<IdentityUserLogin>()
    .Property(b => b.LoginProvider)
    .HasColumnType("nvarchar")
    .HasMaxLength(128);

modelBuilder.Entity<IdentityUserLogin>()
    .Property(b => b.ProviderKey)
    .HasColumnType("nvarchar")
    .HasMaxLength(128);

modelBuilder.Entity<IdentityUserClaim>()
    .Property(b => b.UserId)
    .HasColumnType("nvarchar")
    .HasMaxLength(128);

modelBuilder.Entity<IdentityUserClaim>()
    .Property(b => b.ClaimType)
    .HasColumnType("nvarchar(MAX)")
    .HasMaxLength(null);

modelBuilder.Entity<IdentityUserClaim>()
    .Property(b => b.ClaimValue)
    .HasColumnType("nvarchar(MAX)")
    .HasMaxLength(null);