UserManager.AddToRole not working - Foreign Key error

Question

In my ASP.NET MVC application I have some code which should be fairly trivial:

UserManager.AddToRole(user.id, "Admin");

I just get this error...

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId". The conflict occurred in database "TestDatabase", table "dbo.AspNetRoles", column 'Id'.

My ASP.NET Identity Framework is custom in that everything uses Guid as keys instead of int or string.

Any ideas what is causing this?

Edits, as per user comments...

User class

public class User : IdentityUser<Guid, UserLogin, UserRole, UserClaim>
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public override Guid Id
    {
        get { return base.Id; }
        set { base.Id = value; }
    }
}

Role class

public class Role : IdentityRole<Guid, UserRole>
{
    public const string Admininstrator = "Administrator";

    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public new Guid Id { get; set; }
}

UserRole class

public class UserRole : IdentityUserRole<Guid>
{
}

internal class RoleManager : RoleManager<Role, Guid>
{
    public RoleManager(IRoleStore<Role, Guid> roleStore) : base(roleStore)
    {
    }

    public static RoleManager Create(IdentityFactoryOptions<RoleManager> options, IOwinContext context)
    {
        return new RoleManager(new RoleStore(context.Get<ApplicationDataContext>()));
    }
}

SignInManager class

internal class SignInManager : SignInManager<User, Guid>
{
    public SignInManager(UserManager userManager, IAuthenticationManager authenticationManager) : base(userManager, authenticationManager)
    {
    }

    public override Task<ClaimsIdentity> CreateUserIdentityAsync(User user)
    {
        return user.GenerateUserIdentityAsync((UserManager)UserManager);
    }

    public static SignInManager Create(IdentityFactoryOptions<SignInManager> options, IOwinContext context)
    {
        return new SignInManager(context.GetUserManager<UserManager>(), context.Authentication);
    }
}

UserManager class

internal class UserManager : UserManager<User, Guid>
{
    public UserManager(IUserStore<User, Guid> store) : base(store)
    {
    }

    public static UserManager Create(IdentityFactoryOptions<UserManager> options, IOwinContext context)
    {
        var manager = new UserManager(new UserStore<User, Role, Guid, UserLogin, UserRole, UserClaim>(context.Get<ApplicationDataContext>()));
        // Configure validation logic for usernames
        manager.UserValidator = new UserValidator<User, Guid>(manager)
        {
            AllowOnlyAlphanumericUserNames = false,
            RequireUniqueEmail = true
        };

        // Configure validation logic for passwords
        manager.PasswordValidator = new PasswordValidator
        {
            RequiredLength = 6,
            RequireNonLetterOrDigit = true,
            RequireDigit = true,
            RequireLowercase = true,
            RequireUppercase = true,
        };

        // Configure user lockout defaults
        manager.UserLockoutEnabledByDefault = true;
        manager.DefaultAccountLockoutTimeSpan = TimeSpan.FromMinutes(5);
        manager.MaxFailedAccessAttemptsBeforeLockout = 5;

        // Register two factor authentication providers. This application uses Phone and Emails as a step of receiving a code for verifying the user
        // You can write your own provider and plug it in here.
        manager.RegisterTwoFactorProvider("Phone Code", new PhoneNumberTokenProvider<User, Guid>
        {
            MessageFormat = "Your security code is {0}"
        });
        manager.RegisterTwoFactorProvider("Email Code", new EmailTokenProvider<User, Guid>
        {
            Subject = "Security Code",
            BodyFormat = "Your security code is {0}"
        });
        manager.EmailService = new EmailService();
        manager.SmsService = new SmsService();
        var dataProtectionProvider = options.DataProtectionProvider;
        if (dataProtectionProvider != null)
        {
            manager.UserTokenProvider = new DataProtectorTokenProvider<User, Guid>(dataProtectionProvider.Create("ASP.NET Identity"));
        }

        return manager;
    }
}

RoleStore class

internal class RoleStore : RoleStore<Role, Guid, UserRole>
{
    public RoleStore(DbContext context) : base(context)
    {
    }
}

UPDATE 1:

The culprit lies here...

public class Role : IdentityRole<Guid, UserRole>
{
    public const string Admininstrator = "Administrator";

    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public new Guid Id { get; set; }
}

...specifically...

[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public new Guid Id { get; set; }

I replaced this with a dirty hack, which works

public Role()
{
    Id = Guid.NewGuid();
}

If that's any help to anyone? personally I would prefer NOT to use a dirty hack!

Answer 1

This might be useful for someone else who is customizing the default IdentityUser

I had the same error with different configuration though. The actual problem was that AddToRoleAsync() method points to Id from default IdentityUser instead of my custom user which is of course inheriting from IdentityUser<int> having PK as UserId.

When AddToRoleAsync() method points to Id from default IdentityUser, but we are having a different Primary Key for the customized user, we have 0 in the Id column of IdentityUser. That's why it throws error due to Referential Integrity. This referential integrity demands that foreign key must also exist in the parent table. And in this case, the parent table User did not have Primary Key value 0.

Thus, I simply removed my UserId and used the existing Id that is coming from IdentityUser. Because I was not looking forward to modify the UserManager class.

Answer 2

Replace

[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public new Guid Id { get; set; }

with fluent api. In custom IdentityDbContext class add

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
       base.OnModelCreating(modelBuilder);
       // identity
       modelBuilder.Entity<User>().Property(r=>r.Id)
          .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
       modelBuilder.Entity<Role>().Property(r=>r.Id)
          .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
}

Answer 3

After the last update it is clear now - I'm guessing that DatabaseGenerated(DatabaseGeneratedOption.Identity) was added after the tables were created. And actual database does not know that it needs to generate primary key, it expects the clients to provide the key.

If you look in SSMS on ApplicationRoles table (or whatever corresponding table name you have) (right click on the table -> "Design" and look on "Default Value or Binding" on field Id it will be empty. But it should be saying newid():

Migrations not always pick up this change and you end up hanging with errors like you get. The solutions to this is to try adding another EF migration, it should give you something like this:

AlterColumn("dbo.ApplicationRoles", "Id", c => c.Guid(nullable: false, defaultValueSql: "newid()"));

Though this will not always work. Sometimes I had to drop and recreate the table entirely for EF to pick up that I want DB to generate the ID for me.

Or (I think this is a better solution) remove DatabaseGenerated(DatabaseGeneratedOption.Identity) from the attribute above Id field and keep

public Role()
{
    Id = Guid.NewGuid();
}

This allows you to define the key yourself before creating the record in the DB. This is better if you are using CQRS architecture. But EF might be funny about removing the attribute and will ask for another migration.