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

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

About this issue

Original URL
State: closed
Created 7 years ago
Reactions: 1
Comments: 22 (7 by maintainers)

Most upvoted comments

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

+19

kosstianmailru on Oct 19, 2018

@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.

divega on Aug 27, 2017

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);

adnan-kamili on Aug 27, 2017

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

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

builder.Entity<User>()
    .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 to WithOne() 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

divega on Aug 27, 2017