.NET EF Core context querying Postgresql tries to query unwanted shadow FK column-CodePudding

After changing entity, adding or removing properties, a "random" column was added to query and it's causing problems.

Npgsql.PostgresException (0x80004005): 42703: column u.UserOldId does not exist

I am querying this entity

[Table("user_visuals", Schema = "present")]
    public class UserVisual
    {
        #region Columns
        [Key]
        [Column("user_visual_id")]
        public long Id { get; set; }
        [Column("user_id")]
        public long UserId { get; set; }
        [Column("visual_id")]
        public long VisualId { get; set; }
        [Column("render_position")]
        public int RenderPosition { get; set; }
        #endregion

        #region Relations
        public Visual Visual { get; set; } = default!;
        //public UserOld User { get; set; } = default!;
        #endregion
    }

After some investigation I've found out that context somehow contains shadow FK and tries to query it though it doesn't exist

Seems like context has this kind of information about it

EntityType: UserVisual
  Properties: 
    Id (long) Required PK AfterSave:Throw ValueGenerated.OnAdd
      Annotations: 
        Npgsql:ValueGenerationStrategy: IdentityByDefaultColumn
        Relational:ColumnName: user_visual_id
    RenderPosition (int) Required
      Annotations: 
        Npgsql:ValueGenerationStrategy: None
        Relational:ColumnName: render_position
    UserId (long) Required FK Index
      Annotations: 
        Npgsql:ValueGenerationStrategy: None
        Relational:ColumnName: user_id
    UserOldId (no field, long?) Shadow FK Index
      Annotations: 
        Npgsql:ValueGenerationStrategy: None
    VisualId (long) Required FK Index
      Annotations: 
        Npgsql:ValueGenerationStrategy: None
        Relational:ColumnName: visual_id
  Navigations: 
    Visual (Visual) ToPrincipal Visual Inverse: UserVisuals
  Keys: 
    Id PK
  Foreign keys: 
    UserVisual {'UserId'} -> User {'Id'} ToDependent: Visuals Cascade
    UserVisual {'UserOldId'} -> UserOld {'Id'} ToDependent: Visuals ClientSetNull
    UserVisual {'VisualId'} -> Visual {'Id'} ToDependent: UserVisuals ToPrincipal: Visual Cascade
  Indexes: 
    UserId 
    UserOldId 
    VisualId 
  Annotations: 
    DiscriminatorProperty: 
    Relational:FunctionName: 
    Relational:Schema: present
    Relational:SqlQuery: 
    Relational:TableName: user_visuals
    Relational:ViewName: 
    Relational:ViewSchema:

Context configuration

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<UserIdentity>()
                .HasKey(x => x.Id);
            modelBuilder.Entity<UserRole>()
                .HasKey(x => x.Id);
            modelBuilder.Entity<IdentityUserRole<long>>().HasKey(p => new { p.UserId, p.RoleId });
            modelBuilder.Entity<UserVisual>().HasOne<Visual>(x => x.Visual).WithMany(x => x.UserVisuals).HasForeignKey(x => x.VisualId);
            modelBuilder.Entity<UserDevice>().HasOne<Device>(x => x.Device).WithMany(x => x.UserDevices).HasForeignKey(x => x.DeviceId);
            modelBuilder.Entity<Device>().HasOne<DeviceType>(x => x.DeviceType).WithMany(x => x.Devices).HasForeignKey(x => x.TypeId);
        }

Out of ideas, what should I do?

CodePudding user response：

Phantom shadow FKs usually indicate some relationship misconfiguration via fluent API, or like in this case, relationship created by EF Core default conventions.

Note that Single navigation property in one of the entities is enough to imply relationship in the model. So even if you removed the reference navigation property from the dependent entity (UserOld User in UserVisual), if the principal type (UserOld in this case) is still included in the model, and has collection navigation property referring to the dependent entity type (ICollection<UserVisual or similar), EF Core still considers a relationship with conventional shadow FK.

So, always check the usage (find all references) of a entity class inside other entities navigation properties, and remove the ones which are not intended to create relationship. Also (even though it's not the case here) the correct pairing of Has / With` calls to not leave existing navigation property out of the fluent configuration, thus creating additional conventional relationship.