ASP.NET-Identity Limit UserNameIndex Length

Question

How can I limit the UNIQUE index of UserNameIndex in the AspNetUsers table?

I use the ASP.NET identifier with the Mysql backend and click on another instance:

Index column size too large. The maximum column size is 767 bytes. 

I tried

 modelBuilder.Entity<Secuser>().Property(x => x.UserName).HasMaxLength(100); modelBuilder.Entity<Secuser>().Property(t => t.Id).HasMaxLength(100); 

I followed the whole standard:

 public class Secuser : IdentityUser { [Required] public string FullName { get; set; } [Required] public string Address { get; set; } [Required] public string City { get; set; } [Required] public string Country { get; set; } [Required] public bool AgreeTermsOfServicePrivacyPolicy { get; set; } [Required] public string BasePath { get; set; } } [DbConfigurationType(typeof(MySqlEFConfiguration))] public class ApplicationDbContext : IdentityDbContext<Secuser> { public ApplicationDbContext() : base("DefaultConnection") { } ..... protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Secuser>().Property(x => x.UserName).HasMaxLength(100); modelBuilder.Entity<Secuser>().Property(t => t.Id).HasMaxLength(100); ..... base.OnModelCreating(modelBuilder); } } 

When I go out:

 update-database -verbose 

output:

 Using StartUp project 'sec'. Using NuGet project 'sec'. Specify the '-Verbose' flag to view the SQL statements being applied to the target database. Target database is: 'sec' (DataSource: localhost, Provider: MySql.Data.MySqlClient, Origin: Configuration). Applying explicit migrations: [201405072209015_InitialCreate]. Applying explicit migration: 201405072209015_InitialCreate. ..... create table `AspNetUsers` (`Id` nvarchar(128) not null ,`FullName` longtext not null ,`Address` longtext not null ,`City` longtext not null ,`Country` longtext not null ,`AgreeTermsOfServicePrivacyPolicy` bool not null ,`BasePath` longtext not null ,`Email` nvarchar(256) ,`EmailConfirmed` bool not null ,`PasswordHash` longtext,`SecurityStamp` longtext,`PhoneNumber` longtext,`PhoneNumberConfirmed` bool not null ,`TwoFactorEnabled` bool not null ,`LockoutEndDateUtc` datetime,`LockoutEnabled` bool not null ,`AccessFailedCount` int not null ,`UserName` nvarchar(256) not null ,primary key ( `Id`) ) engine=InnoDb auto_increment=0 CREATE UNIQUE index `UserNameIndex` on `AspNetUsers` (`UserName` DESC) using HASH MySql.Data.MySqlClient.MySqlException (0x80004005): Index column size too large. The maximum column size is 767 bytes. at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) ..... 

I also made changes to allow the migration history:

 public class MySqlHistoryContext : HistoryContext { public MySqlHistoryContext(DbConnection connection, string defaultSchema):base(connection,defaultSchema) { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity<HistoryRow>().Property(h => h.MigrationId).HasMaxLength(100).IsRequired(); modelBuilder.Entity<HistoryRow>().Property(h => h.ContextKey).HasMaxLength(200).IsRequired(); } } 

It seems like the whole problem is centered around

 `UserName` nvarchar(256) modelBuilder.Entity<Secuser>().Property(x => x.UserName).HasMaxLength(100); 

doesn't seem to work. I am using EntityFramework 6.1.0 and Microsoft.AspNet.Identity.Core 2.0.1.

So this looks like looking at:

ASP.NET-Identity Length Limit Custom Name Length

either that I missed something in the way I implemented HasMaxLength, or there is some other reason why it does not work.

Answer 1

Simply, if you look at the log (not so obvious), it crashes into this query:

 CREATE UNIQUE index `UserNameIndex` on `AspNetUsers` (`UserName` DESC) using HASH 

When you look at your transition code, the up () function, you will see this by default:

  UserName = c.String(nullable: false, maxLength: 256, storeType: "nvarchar"), 

I just changed the maxLength value to 196, it should work. You just need to remember that usernames cannot be longer than 196 characters (which, I think, is really enough for 99.9% of the population)

PS: I got the same error for the AspNetRoles table

Answer 2

Instead of setting the maximum length in the migration code, it is better to set it on the model itself. This can be done using the following code.

Thanks: Rune G @ https://stackoverflow.com >

Just copy the code from the link above to simplify the link

 public class ApplicationDbContext : IdentityDbContext<ApplicationUser> { //... default code for ApplicationDbContext protected override void OnModelCreating(DbModelBuilder modelBuilder) { if (modelBuilder == null) { throw new ArgumentNullException("modelBuilder"); } base.OnModelCreating(modelBuilder); modelBuilder.Entity<IdentityUser>().Property(u => u.UserName).HasMaxLength(128); //Uncomment this to have Email length 128 too (not neccessary) //modelBuilder.Entity<ApplicationUser>().Property(u => u.Email).HasMaxLength(128); modelBuilder.Entity<IdentityRole>().Property(r => r.Name).HasMaxLength(128); } }