MVC5 EF6 Seed Method: Cannot insert NULL into column 'Id' table AspNetUsers

Question

Entity Framework 6, MVC5, ASP Identity 2.0.

I have followed this 2 tutorials to create the login part of the web application: part 1 - part 2

I created my own AppUser class from IdentityUser Class in another library project. I also have my data context from the IdentityDbContext

    public class DataContext: IdentityDbContext<AppUser>
    {
       public DataContext(): base("DefaultConnection") { }

       protected override void OnModelCreating(DbModelBuilder modelBuilder)
       {
           modelBuilder.Entity<AppUser>().Property(u => u.Id).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);
           base.OnModelCreating(modelBuilder);
      }
    }

public class AppUser : IdentityUser
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public override string Id { get; set; }

    [Required]
    [Display(Name ="First Name")]
    [MaxLength(100)]
    public string FirstName { get; set; }

    [Required]
    [Display(Name = "Last Name")]
    [MaxLength(100)]
    public string LastName { get; set; }

    [MaxLength(100)]
    public string Title { get; set; }

    [Required(ErrorMessage = "Email is required.")]
    [Display(Description = "Email")]
    public override string Email { get; set; }

    [MaxLength(20)]
    public string Phone { get; set; }

    [Range(0,9999)]
    [Display(Name = "Extension")]
    public int PhoneExtension { get; set; }

    [Display(Name = "Active")]
    [DefaultValue(true)]
    public bool IsActive { get; set; }
}

When I run the seed method I get the following error

System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Id', table 'CRM1.dbo.AspNetUsers'; column does not allow nulls. INSERT fails. The statement has been terminated.

Here is the seeding method:

protected override void Seed(CRM.DAL.Data.DataContext context)
    {
        context.AccessLevels.AddOrUpdate(al => al.Label,
            new Model.AccessLevel { AccessLevelID = 1, Label = "Admin", Description = "Full access to the CRM data" },
            new Model.AccessLevel { AccessLevelID = 2, Label = "Customer Service", Description = "Full access to the CRM data" },
            new Model.AccessLevel { AccessLevelID = 3, Label = "Sales", Description = "Full access to the CRM data" }
            );

        if (!(context.Users.Any(u => u.UserName == "admin@admin.com")))
        {
            var userStore = new UserStore<AppUser>(context);
            var userManager = new UserManager<AppUser>(userStore);
            var userToInsert = new AppUser
            {
                Id = "1",
                FirstName = "Admin",
                LastName = "Admin",
                Title = "Administrator",
                Email = "admin@admin.com",
                PhoneExtension = 0,
                IsActive = true,
                AccessLevelID = 1,
                EmailConfirmed = true,
                PhoneNumberConfirmed = true,
                TwoFactorEnabled = false,
                LockoutEnabled = false,
                AccessFailedCount = 0,
                UserName = "admin"
            };
            userManager.Create(userToInsert, "password");
        }

    }

So Id gets a value but EF says it is inserting NULL. I tried by not giving any value to Id thinking EF would do it automatically but no.

What is the approach to seed an AspNetUser with my context? I saw different tutorials but it does not apply to my extended AppUser class and DataContext.

Thank you

Answer 1

The following happened to me. I followed this guide to change the Primary Key for Users in ASP.NET Identity from Guid to Int.

https://learn.microsoft.com/en-us/aspnet/identity/overview/extensibility/change-primary-key-for-users-in-aspnet-identity

I had already made some migrations so EF tried to change the Id from nvarchar(128) to int. This worked but Identity Specification never turned from No to Yes. Solved it by removing all previous migrations, removing the database and then make a new migration for all the changes. Since we were early in development this was not a problem to do.

Answer 2

Remove this line from your code:

modelBuilder.Entity<AppUser>().Property(u => u.Id).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);

and also this part from your User:

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Key]
public override string Id { get; set; }

You don't need to override this property, just use the parent's property (entity framework will map it).

Explanation: when you configure the Id property like your did, you're telling entity framework the property value comes from database, so it totally ignores the value you passed to this property when inserting into database.

On the other hand, if your column inside your database table does not have a default value, it will fails, because entity framework is not providing a value for it.

So you need to decide: either your table must have a default value, or you should remove that HasDatabaseGeneratedOption option for that field.

The thing is that IdentityUser uses a string as key, which normally would be a Guid. Since EF won't generate Id for you automatically, and your column won't as well, you can create a constructor to your User do like this:

public AppUser()
{
    Id = Guid.NewGuid().ToString();
}

Whenever you create a new user, it will have a new Guid as Id....when you read an existing user from your context, although your constructor will generate it when being instantiated by EF, EF will then load Id value from your table over it (ignoring the one provided in constructor).

This means the autogenerated Guid inside your constructor will be used only for new instances.