.NET Core 2.x Identity int foreign key cannot target int primary key

Question

I have a web application running .net core 2.x (just upgraded from .net core 1.x finally) and I've gotten most everything ported from .net core 1.x to 2.x. I have however hit a brick wall with my Identity implementation. It worked fine with .net core 1.x but now it refuses.

The implementation is running on Entity Framework Core and is built Database first (implemented in a pre-existing database).

My problem is that when I try to log in now with .net core 2.x I get an error message that states:

InvalidOperationException: The relationship from 'AspNetUserRole.AspNetRole' to 'AspNetRole.AspNetUserRoles' with foreign key properties {'RoleId' : int} cannot target the primary key {'Id' : int} because it is not compatible. Configure a principal key or a set of compatible foreign key properties for this relationship.

Which, to me, makes absolutely no sense. How can an int foreign key be incompatible with an int primary key?

The actual implementation of the context and the classes are as follow (it's a stupidly simple implementation):

public partial class AspNetUser : IdentityUser<int>
{ }
public partial class AspNetRole : IdentityRole<int>
{ }
public partial class AspNetRoleClaim : IdentityRoleClaim<int>
{ }
public partial class AspNetUserClaim : IdentityUserClaim<int>
{ }
public partial class AspNetUserRole : IdentityUserRole<int>
{ }
public partial class AspNetUserToken : IdentityUserToken<int>
{ }
public partial class AspNetUserLogin : IdentityUserLogin<int>
{ }

public class IdentityDataContext : IdentityDbContext<AspNetUser, AspNetRole, int, AspNetUserClaim, AspNetUserRole, AspNetUserLogin, AspNetRoleClaim, AspNetUserToken>
{
    public IdentityDataContext(DbContextOptions<IdentityDataContext> options) : base(options)
    { }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.Entity<AspNetUser>()
            .HasMany(e => e.AspNetUserClaims)
            .WithOne()
            .HasForeignKey(e => e.UserId)
            .IsRequired()
            .OnDelete(DeleteBehavior.Restrict);

        builder.Entity<AspNetUserClaim>()
           .HasOne(x => x.AspNetUser)
           .WithMany(x => x.AspNetUserClaims)
           .HasForeignKey(x => x.UserId);

        builder.Entity<AspNetUser>()
            .HasMany(e => e.AspNetUserLogins)
            .WithOne()
            .HasForeignKey(e => e.UserId)
            .IsRequired()
            .OnDelete(DeleteBehavior.Restrict);

        builder.Entity<AspNetUserLogin>()
           .HasOne(x => x.AspNetUser)
           .WithMany(x => x.AspNetUserLogins)
           .HasForeignKey(x => x.UserId);

        builder.Entity<AspNetUser>()
            .HasMany(e => e.AspNetUserRoles)
            .WithOne()
            .HasForeignKey(e => e.UserId)
            .IsRequired()
            .OnDelete(DeleteBehavior.Restrict);

        builder.Entity<AspNetUserRole>()
           .HasOne(x => x.AspNetUser)
           .WithMany(x => x.AspNetUserRoles)
           .HasForeignKey(x => x.UserId);

        builder.Entity<AspNetRole>()
            .HasMany(e => e.AspNetUserRoles)
            .WithOne()
            .HasForeignKey(e => e.RoleId)
            .IsRequired()
            .OnDelete(DeleteBehavior.Restrict);

        builder.Entity<AspNetUserRole>()
            .HasOne(x => x.AspNetRole)
            .WithMany(x => x.AspNetUserRoles)
            .HasForeignKey(x => x.RoleId);

        builder.Entity<AspNetUserRole>()
            .HasOne(x => x.AspNetUser)
            .WithMany(x => x.AspNetUserRoles)
            .HasForeignKey(x => x.UserId);

        builder.Entity<AspNetRole>()
            .HasMany(e => e.AspNetRoleClaims)
            .WithOne()
            .HasForeignKey(e => e.RoleId)
            .IsRequired()
            .OnDelete(DeleteBehavior.Restrict);

        builder.Entity<AspNetRoleClaim>()
           .HasOne(x => x.AspNetRole)
           .WithMany(x => x.AspNetRoleClaims)
           .HasForeignKey(x => x.RoleId);

        builder.Entity<AspNetUser>()
            .HasMany(e => e.AspNetUserTokens)
            .WithOne()
            .HasForeignKey(e => e.UserId)
            .IsRequired()
            .OnDelete(DeleteBehavior.Restrict);

        builder.Entity<AspNetUserToken>()
           .HasOne(x => x.AspNetUser)
           .WithMany(x => x.AspNetUserTokens)
           .HasForeignKey(x => x.UserId);
    }
}

and the two classes it complains about are defined as:

[Table("AspNetUserRoles")]
public partial class AspNetUserRole
{
    [Key]
    public int Id { get; set; }
    [ForeignKey("AspNetUser")]
    public override int UserId { get; set; }
    [ForeignKey("AspNetRole")]
    public override int RoleId { get; set; }
    public string ConcurrencyStamp { get; set; }
    public int CreatedById { get; set; }
    public System.DateTime CreatedDate { get; set; }
    public Nullable<int> ChangedById { get; set; }
    public Nullable<System.DateTime> ChangedDate { get; set; }
    public bool IsDisabled { get; set; }

    [JsonIgnore]
    public virtual AspNetRole AspNetRole { get; set; }
    [JsonIgnore]
    public virtual AspNetUser AspNetUser { get; set; }
}

[Table("AspNetRoles")]
public partial class AspNetRole
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public AspNetRole()
    {
        this.AspNetRoleClaims = new HashSet<AspNetRoleClaim>();
        this.AspNetUserRoles = new HashSet<AspNetUserRole>();
    }

    [Key]
    public override int Id { get; set; }
    public override string Name { get; set; }
    public override string NormalizedName { get; set; }
    public override string ConcurrencyStamp { get; set; }
    public int CreatedById { get; set; }
    public System.DateTime CreatedDate { get; set; }
    public Nullable<int> ChangedById { get; set; }
    public Nullable<System.DateTime> ChangedDate { get; set; }
    public bool IsDisabled { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<AspNetRoleClaim> AspNetRoleClaims { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<AspNetUserRole> AspNetUserRoles { get; set; }
}

This actually has me completely stumped.

EDIT: When breaking and trying to evaluate a DbSet where it throws the error, I get a stacktrace that points to the ModelEvaluator. Which, I'm going to be honest, helps me very little.

at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidateNoShadowKeys(IModel model)\r\n at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.Validate(IModel model)\r\n at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model)\r\n at Microsoft.EntityFrameworkCore.Internal.SqlServerModelValidator.Validate(IModel model)\r\n at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)\r\n at System.Lazy'1.ViaFactory(LazyThreadSafetyMode mode)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Lazy'1.CreateValue()\r\n at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()\r\n at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()\r\n at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)\r\n at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProviderEngineScope scope)\r\n at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)\r\n at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)\r\n at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)\r\n at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()\r\n at Microsoft.EntityFrameworkCore.DbContext.get_Model()\r\n at Microsoft.EntityFrameworkCore.Internal.InternalDbSet'1.get_EntityType()\r\n at Microsoft.EntityFrameworkCore.Internal.InternalDbSet'1.get_EntityQueryable()\r\n at Microsoft.EntityFrameworkCore.Internal.InternalDbSet'1.System.Collections.Generic.IEnumerable.GetEnumerator()\r\n at System.Collections.Generic.LargeArrayBuilder'1.AddRange(IEnumerable'1 items)\r\n at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable'1 source)\r\n at System.Linq.Enumerable.ToArray[TSource](IEnumerable'1 source)\r\n at System.Linq.SystemCore_EnumerableDebugView'1.get_Items()

EDIT 2: On recommendation, tried adding a OnModelCreating in where I define the different foreign keys (see the definition of my IdentityDataContext above) - no luck.

EDIT 3: The OnModelCreating WAS the answer: I had just missed the "reversed" definitions, if you will. For example, defining

builder.Entity<AspNetUser>()
    .HasMany(e => e.AspNetUserRoles)
    .WithOne()
    .HasForeignKey(e => e.UserId)
    .IsRequired()
    .OnDelete(DeleteBehavior.Restrict);
builder.Entity<AspNetRole>()
    .HasMany(e => e.AspNetUserRoles)
    .WithOne()
    .HasForeignKey(e => e.RoleId)
    .IsRequired()
    .OnDelete(DeleteBehavior.Restrict);

Is not enough - you have to also add the reverse:

builder.Entity<AspNetUserRole>()
    .HasOne(x => x.AspNetUser)
    .WithMany(x => x.AspNetUserRoles)
    .HasForeignKey(x => x.UserId);

builder.Entity<AspNetUserRole>()
    .HasOne(x => x.AspNetRole)
    .WithMany(x => x.AspNetUserRoles)
    .HasForeignKey(x => x.RoleId);

Photoreconnaissance

Can you post the fluent configuration (OnModelCreating) of your IdentityDataContext? – Prescience

I'm not using Fluent at all. The IdentityDataContext is only what's in the code above. The partial classes (AspNetRole, AspNetUserRole and so on) are generated from an edmx (Database First). – Photoreconnaissance

The problem is that navigation properties have been removed from the identity classes, and that is reflected in the base IdentityDbContext fluent configuration, which takes precedence over data annotations / conventions. Since your models have navigation properties, you have to override that after calling base implementation. See for instance #45864022. – Prescience

Tried your recommendation and added a OnModelCreating, in where I define the different foreign keys (see the definition of my IdentityDataContext above) - no luck. Same error. – Photoreconnaissance

That's a good starting point! However some of the WithOne are missing the inverse navigation property, e.g. WithOne(e => e.AspNetUser), WithOne(e => e.AspNetRole). Also the AspNetUserRole PK - builder.Entity<AspNetUserRole>().HasKey(e => e.Id). Basically everything that is different from the base identity context configuration. – Prescience

Yup, I noticed and just updated the question. Please, post your comments as an answer so I can accept and reward the bounty! :) – Photoreconnaissance

Answer 1

The exception message is not quite clear, but usually indicates improper model configuration.

There are several factors to be considered here.

First, in version 2.0 the navigation properties have been removed from identity model, and the base IndentityDbCOntext implementation explicitly configures the relationships with no navigation property at either side.

The last is very important. EF Core uses conventions, data annotations and explicit configuration (via fluent API), with conventions being a lowest priority and explicit configuration being the highest priority. What that means is that data annotations can override conventions, but not explicit configuration. Explicit configuration can override both conventions and data annotations, as well as the previous explicit configuration (the last wins). In other words, the only way to override explicit configuration is to use fluent API after the base configuration.

Since your model adds some navigation properties, you have to re configure the relationships to reflect that. The common mistake with relationship configuration is to use the Has / With methods without specifying the navigation property name / expression when in fact the model do have navigation property. Logically you think that skipping the optional argument means use default, but here it actually means no navigation property. Which in turn leads to the following unexpected behavior.

The navigation properties are still discovered by EF. Since they are not a part of a configured relationship, EF considers them being a part of a separate relationship and conventionally maps them with default shadow FK property / column name. Which is definitely not what you want.

There is no need to configure the relationship twice. Actually it's better to configure it once, but using the correct With / Has call arguments that represent the presence / absence of the navigation property at that end.

With that being said, you have to override OnModelCreating, call the base implementation and then add the following to reflect the navaigation properties introduced in your identity model derived entities:

builder.Entity<AspNetUserRole>()
    .HasOne(x => x.AspNetUser)
    .WithMany(x => x.AspNetUserRoles)
    .HasForeignKey(x => x.UserId);

builder.Entity<AspNetUserRole>()
    .HasOne(x => x.AspNetRole)
    .WithMany(x => x.AspNetUserRoles)
    .HasForeignKey(x => x.RoleId);

and similar for other navigation properties like AspNetRole.AspNetRoleClaims collection etc. For more info, see the Relationships EF Core documentation topic explaining different relationship configurations

Also, since by default the IdentityUserRole is (again explicitly) configured to use composite PK ({ UserId, RoleId }) and your derived AspNetUserRole entity defines its own PK (Id), you should also explicitly specify that:

builder.Entity<AspNetUserRole>()
    .HasKey(e => e.Id);

Prescience

Dear Ivan Stoev, why following code does not work: builder.Entity<AspNetUserRole>().HasOne(x => x.AspNetUser) .WithMany().HasForeignKey(x => x.UserId); and builder.Entity<AspNetUserRole>().HasOne(x => x.AspNetRole).WithMany().HasForeignKey(x => x.RoleId); Any idea please? – Cnidus

@Cnidus Probably because now you are leaving unmapped collection navigation properties. Parameterless WithMany() doesn't mean "use default conventional navigation property" - it means "this relationship has no collection navigation property". Basically what I've tried to explain in the answer section starting with "The common mistake with relationship configuration...". – Prescience

Thank you! I shall give a try after removing the navigation properties! – Cnidus

Answer 2

For me this was fixed by calling the base OnModelCreating method before any other fluent code:

        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<UserDefinition>()
            .ToTable("UserDefinition");
        //    .HasOne(x => x.Subscription)
        //    .WithMany(x => x.UserDefinitions)
        //    .HasForeignKey(x => x.SubscriptionId);

        modelBuilder.Entity<Subscription>()
            .ToTable("Subscription");
        //    .HasMany(x => x.UserDefinitions)
        //    .WithOne()
        //    .HasForeignKey(x => x.SubscriptionId);

I found the answer here EF Core 2.0 Identity - Adding navigation properties.

Goodin

After all these changes, this saved my day. Thank you! (core 3.1) – Estuary

Answer 3

We can use HasPrincipalKey to define with which field we want to link our foreign key

public class Menu
{
  [Key]
  public int Id { get; set; }
  public string MenuCode { get; set; }
  public Route Route { get; set; }
}
 
public class Route
{
  [Key]
  public int Id { get; set; }
  public string MenuCode { get; set; }
  public string Name { get; set; }
  public Menu Menu { get; set; }
}

the fix

modelBuilder.Entity<Menu>()
.HasOne(x => x.Route)
.WithOne(x => x.Menu)
.HasPrincipalKey<Menu>(x => x.MenuCode)
.HasForeignKey<Route>(x => x.MenuCode);

Please check this blog for more details.

https://gavilan.blog/2019/04/14/entity-framework-core-foreign-key-linked-with-a-non-primary-key/

Antonetta

Answer 4

I think the id is Long Not int so the relationship should be between long data type, not int try it!!

in the past, I do the relationship using int data type, and the EF migration unaccepted it.

Balenciaga

Nope, both are int's. The problem was correctly identified by @IvanStoev in the comments to the question- navigation properties have been removed from the identity classes since Identity Core 2.x, thus they have to be added in OnModelCreating. I'm waiting for him to post the answer as an answer so I can accept it – Photoreconnaissance