Efcore: Duplicated foreign keys after restoring missing IdentityUser and IdentityRole navigation properties

[bdominguez]

Previously on 1.1.2 IdentityUser and IdentityRole provided the following navigation properties:

User

public virtual ICollection<UserRole> Roles { get; } = new List<UserRole>();
public virtual ICollection<UserClaim> Claims { get; } = new List<UserClaim>();
public virtual ICollection<UserLogin> Logins { get; } = new List<UserLogin>();

Role

public virtual ICollection<UserRole> Users { get; } = new List<UserRole>();
public virtual ICollection<RoleClaim> Claims { get; } = new List<RoleClaim>();

Now if I manually add them (like it's written in 1.x to 2.0 migration docs) I get this when running migrations script:

CREATE TABLE [UserClaims] (
    [Id] int NOT NULL IDENTITY,
    [ClaimType] nvarchar(max) NULL,
    [ClaimValue] nvarchar(max) NULL,
    [UserId] int NOT NULL,
    [UserId1] int NULL,
    CONSTRAINT [PK_UserClaims] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_UserClaims_Users_UserId] FOREIGN KEY ([UserId]) REFERENCES [Users] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_UserClaims_Users_UserId1] FOREIGN KEY ([UserId1]) REFERENCES [Users] ([Id]) ON DELETE NO ACTION
);

CREATE TABLE [UserLogins] (
    [LoginProvider] nvarchar(450) NOT NULL,
    [ProviderKey] nvarchar(450) NOT NULL,
    [ProviderDisplayName] nvarchar(max) NULL,
    [UserId] int NOT NULL,
    [UserId1] int NULL,
    CONSTRAINT [PK_UserLogins] PRIMARY KEY ([LoginProvider], [ProviderKey]),
    CONSTRAINT [FK_UserLogins_Users_UserId] FOREIGN KEY ([UserId]) REFERENCES [Users] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_UserLogins_Users_UserId1] FOREIGN KEY ([UserId1]) REFERENCES [Users] ([Id]) ON DELETE NO ACTION
);

CREATE TABLE [UserRoles] (
    [UserId] int NOT NULL,
    [RoleId] int NOT NULL,
    [RoleId1] int NULL,
    [UserId1] int NULL,
    CONSTRAINT [PK_UserRoles] PRIMARY KEY ([UserId], [RoleId]),
    CONSTRAINT [FK_UserRoles_Roles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [Roles] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_UserRoles_Roles_RoleId1] FOREIGN KEY ([RoleId1]) REFERENCES [Roles] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FK_UserRoles_Users_UserId] FOREIGN KEY ([UserId]) REFERENCES [Users] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_UserRoles_Users_UserId1] FOREIGN KEY ([UserId1]) REFERENCES [Users] ([Id]) ON DELETE NO ACTION
);

Manual workaround:

builder.Entity<Role>()
    .HasMany(e => e.Claims)
    .WithOne()
    .HasForeignKey(e => e.RoleId)
    .IsRequired()
    .OnDelete(DeleteBehavior.Cascade);

builder.Entity<Role>()
    .HasMany(e => e.Users)
    .WithOne()
    .HasForeignKey(e => e.RoleId)
    .IsRequired()
    .OnDelete(DeleteBehavior.Cascade);

builder.Entity<User>()
    .HasMany(e => e.Claims)
    .WithOne()
    .HasForeignKey(e => e.UserId)
    .IsRequired()
    .OnDelete(DeleteBehavior.Cascade);

builder.Entity<User>()
    .HasMany(e => e.Logins)
    .WithOne()
    .HasForeignKey(e => e.UserId)
    .IsRequired()
    .OnDelete(DeleteBehavior.Cascade);

builder.Entity<User>()
    .HasMany(e => e.Roles)
    .WithOne()
    .HasForeignKey(e => e.UserId)
    .IsRequired()
    .OnDelete(DeleteBehavior.Cascade);

Further technical details

EF Core version: 2.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Visual Studio 2017

[ajcvickers]

@bdominguez Are you saying that when you configure the navigations in the EF model (Labeled as "manual workaround" above) then there are still duplicated FKs? Or only if you add the navigation properties but don't explicitly configure them?

[bdominguez]

I mean the second case. If I don't put that workaround code (explicit relationships) I get duplicated FKs because it seems that it doesn't resolve relationships correctly.

[ajcvickers]

This is an issue with the Identity documentation, as discussed in https://github.com/aspnet/Identity/issues/1364. When adding navigations, the existing relationships in the model need to be configured to use the navigations, which requires the code above. If this is not done, then the naigations form a second set of relationships with a new FK.

[adnan-kamili]

The above fix removed the extra foreighn keys, but when I added following navigation keys in the UserRole : IdentityUserRole model, it adds duplicate foreign keys in AspNetUserRoles table even after setting the relationship in modelbuilder:

    public class UserRole : IdentityUserRole<string>
    {
        public User User { get; set; }
        public Role Role { get; set; }
    }

     modelBuilder.Entity<UserRole>().HasOne<User>(e => e.User)
                .WithOne().HasForeignKey<UserRole>(e => e.UserId);
            modelBuilder.Entity<UserRole>().HasOne<Role>(e => e.Role)
                .WithOne().HasForeignKey<UserRole>(e => e.RoleId);

[divega]

@adnan-kamili the following parts of the original workaround are already configuring the relationships of UserRole:

``` C#
builder.Entity()
.HasMany(e => e.Users)
.WithOne()
.HasForeignKey(e => e.RoleId)
.IsRequired()
.OnDelete(DeleteBehavior.Cascade);
...

builder.Entity()
.HasMany(e => e.Roles)
.WithOne()
.HasForeignKey(e => e.UserId)
.IsRequired()
.OnDelete(DeleteBehavior.Cascade);
`` You should be able to map the navigation properties you created by referencing them in the calls toWithOne()` in this code.

It seems you are trying to configure the same relationships from the other side, but you are using the wrong cardinality (one-to-one as opposed to one-to-many).

Since you are specifying two conflicting associations on each of the FKs, I assume EF Core let's the last one specified get the FK and resorts to creating an extra FK in shadow state for the original one.

cc @AndriySvyryd

[adnan-kamili]

If I comment the code, as you said, It creates two additional foreign keys with names RoleId1 & UserId2

       protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Role>()
                .HasMany(e => e.Claims)
                .WithOne()
                .HasForeignKey(e => e.RoleId)
                .IsRequired()
                .OnDelete(DeleteBehavior.Cascade);

            modelBuilder.Entity<User>()
                .HasMany(e => e.Roles)
                .WithOne()
                .HasForeignKey(e => e.UserId)
                .IsRequired()
                .OnDelete(DeleteBehavior.Cascade);

            // modelBuilder.Entity<UserRole>().HasOne<User>(e => e.User)
            //     .WithOne().HasForeignKey<UserRole>(e => e.UserId);
            // modelBuilder.Entity<UserRole>().HasOne<Role>(e => e.Role)
            //     .WithOne().HasForeignKey<UserRole>(e => e.RoleId);

            modelBuilder.Entity<Role>(model =>
            {
                model.HasIndex(r => r.NormalizedName).HasName("RoleNameIndex").IsUnique(false);
                model.HasIndex(r => new { r.NormalizedName, r.TenantId }).HasName("TenantRoleNameIndex").IsUnique();
            });
        }

Additionally, If I don't set the reverse relationship, the include({ "Roles.Role" }) fails with a null exception which otherwise works.

[divega]

@adnan-kamili what I suggested was actually not just to comment the code but to reference your new navigations in the WithOne() calls. Did you try that?

The empty WithOnes() are otherwise specifying that those those navigation properties do not represent that relationship, and hence EF Core must assume they represent a separate relationship.

[adnan-kamili]

Thanks, it worked. So following is the final setting required:

           modelBuilder.Entity<Role>()
                .HasMany(e => e.Claims)
                .WithOne()
                .HasForeignKey(e => e.RoleId)
                .IsRequired()
                .OnDelete(DeleteBehavior.Cascade);

            modelBuilder.Entity<User>()
                .HasMany(e => e.Roles)
                .WithOne("User")
                .HasForeignKey(e => e.UserId)
                .IsRequired()
                .OnDelete(DeleteBehavior.Cascade);

            modelBuilder.Entity<Role>()
                .HasMany(e => e.Users)
                .WithOne("Role")
                .HasForeignKey(e => e.RoleId)
                .IsRequired()
                .OnDelete(DeleteBehavior.Cascade);

[snibe]

can someone explain specifically where to add these? I am at a loss here sorry I thought I could figure it out but I am stuck.

[ajcvickers]

@snibe See _Add IdentityUser navigation properties_ here: https://docs.microsoft.com/en-us/aspnet/core/migration/1x-to-2x/identity-2x

[snibe]

@ajcvickers thanks! Guess I am dense today because i can't figure out where the following lines of code need to go. the rest seems clear to me

Copy
///

/// Navigation property for the roles this user belongs to.
///

public virtual ICollection> Roles { get; } = new List>();

///

/// Navigation property for the claims this user possesses.
///

public virtual ICollection> Claims { get; } = new List>();

///

/// Navigation property for this users login accounts.
///

public virtual ICollection> Logins { get; } = new List>();

[ajcvickers]

@snibe In ApplicationUser.

[snibe]

ok i think i got it.. still stuck on this error though " 'ApplicationRole' does not contain a definition for 'Users' and no extension method 'Users' accepting a first argument of type 'ApplicationRole' could be found"

[snibe]

sorry i am still at a loss with this stuff. I am now getting this error when trying to run in debug or add migrations.
The entity type 'IdentityUserLogin' requires a primary key to be defined.

is there a better walk through of trying to do this?

I am trying to convert this project >> https://code.msdn.microsoft.com/ASPNET-Core-MVC-Authenticat-ef5942f5

from 1.1 to 2.0 and i have followed >> https://docs.microsoft.com/en-us/aspnet/core/migration/1x-to-2x/identity-2x

with adding the IdentityUser POCO section... I just can't seem to get passed this.

[ranouf]

Hi @ajcvickers

I followed your instructions:
in user.cs

public class User : IdentityUser<Guid>{
            [...]
        //I added:
        public virtual ICollection<IdentityUserRole<Guid>> Roles { get; } = new List<IdentityUserRole<Guid>>();
            [...]
}

in apidbcontext.cs:

public class AppDbContext : IdentityDbContext<User, Role, Guid>{
        protected override void OnModelCreating(ModelBuilder builder)
        {
            [...]
            //I added:
            builder.Entity<User>()
                .HasMany(e => e.Roles)
                .WithOne()
                .HasForeignKey(e => e.UserId)
                .IsRequired()
                .OnDelete(DeleteBehavior.Cascade);

            [...]
        }
}

But each time I add-migration I have:

 migrationBuilder.AddColumn<Guid>(
                name: "UserId1",
                table: "AspNetUserRoles",
                nullable: true);

            migrationBuilder.CreateIndex(
                name: "IX_AspNetUserRoles_UserId1",
                table: "AspNetUserRoles",
                column: "UserId1");

            migrationBuilder.AddForeignKey(
                name: "FK_AspNetUserRoles_AspNetUsers_UserId1",
                table: "AspNetUserRoles",
                column: "UserId1",
                principalTable: "AspNetUsers",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);

I m using:

<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.2" />

Maybe your fix doesn t work anymore with the new version of Microsoft.EntityFrameworkCore.Tools?
Or most probably, I missed something in my code :)

How can I add the list of Roles to User?

[ranouf]

Hi @adnan-kamili

I followed your instructions:
in ApiDbContext:

            modelBuilder.Entity<User>()
                .HasMany(e => e.Roles)
                .WithOne("User")
                .HasForeignKey(e => e.UserId)
                .IsRequired()
                .OnDelete(DeleteBehavior.Cascade);

But as I said previously I have UserId1 created in AspNetUserRoles.

Can you give more details about what you have done to fix the issue?

Thanks

[ranouf]

Hi,

Still not working.

Is there someone who found a fix?

[MaverickMartyn]

Same issue here.
I have been banging my head into the wall for hours now, getting nowhere.
Someone please help me. You will have my eternal grattitude.
After setting up ApplicationRole <-> ApplicationUser mmany-toMany relation as follows:

            builder.Entity<ApplicationRole>()
                .HasMany(e => e.Users)
                .WithOne()
                .HasForeignKey(e => e.RoleId)
                .IsRequired()
                .OnDelete(DeleteBehavior.Cascade);
            builder.Entity<ApplicationUser>()
                .HasMany(e => e.Roles)
                .WithOne()
                .HasForeignKey(e => e.UserId)
                .IsRequired()
                .OnDelete(DeleteBehavior.Cascade);

I keep getting this in my migration:

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddForeignKey(
                name: "FK_AspNetUserRoles_AspNetRoles_RoleId1",
                table: "AspNetUserRoles",
                column: "RoleId",
                principalTable: "AspNetRoles",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropForeignKey(
                name: "FK_AspNetUserRoles_AspNetRoles_RoleId1",
                table: "AspNetUserRoles");
        }

[ajcvickers]

@MaverickMartyn @ranouf I have written the first draft of a document describing how to customize the Identity model--the PR is here: https://github.com/aspnet/Docs/pull/7288

I was not able to reproduce the issues you are seeing when following the steps from the document.

[kosstianmailru]

My the problem was solved after the transfer base.OnModelCreating(modelBuilder); at beginning of method override OnModelCreating

[Hatef-Rostamkhani]

My the problem was solved after the transfer base.OnModelCreating(modelBuilder); at beginning of method override OnModelCreating

This is correct like this
https://docs.microsoft.com/en-us/aspnet/core/security/authentication/customize-identity-model?view=aspnetcore-2.2

[pgkdev]

I had the same problem with .net core 3.1, with the creation of duplicated Foreign Keys in the migration.
The solution was this:
public class ApplicationDbContext : IdentityDbContext<ApplicationUser, ApplicationRole, string> { ... }
I was missing this part <ApplicationUser, ApplicationRole, string> where i specify the customized user and role classes.