ASP.NET-Identity limit UserNameIndex length

Question

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

I am using ASP.NET Identity with a Mysql backend and am running up against another instance of:

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

I have tried

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

I have done all the 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 issue:

update-database -verbose

the output is:

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 have also made the changes to allow Migration History to work:

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 appears that the entire problem centers around

`UserName` nvarchar(256)

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

appears to not be working. I am using EntityFramework 6.1.0 and Microsoft.AspNet.Identity.Core 2.0.1.

So, it appears from looking at:

ASP.NET-Identity limit UserName length

that either I am missing something in how I implemented HasMaxLength or there is some other reason why it does not work.

Answer 1

Simply, if you look at the log (not that evident), it's crashing on this query :

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

When you look at your migration 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'll just have to remember that usernames can't 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