CODEWITHSUNDEEP
c#asp.net.netasp.net-mvc
Lenart Golob
Lenart Golob

Reputation: 191

Foreign key created in shadow and appending random 1 to column name

When I migrate my new models and data I get the following error for multiple foreign keys:

The foreign key property 'InsurancePolicy.InsuranceSubjectID1' was created in shadow state because a conflicting property with the simple name 'InsuranceSubjectID' exists in the entity type, but is either not mapped, is already used for another relationship, or is incompatible with the associated primary key type.

The weird thing is that I define my relationships the same throughout all models, but some work okay (FK without 1 is stored) and some don't.

Example of my models:

public class InsurancePolicy
{
    public int InsurancePolicyID { get; set; }
    [DataType(DataType.Currency)]
    [Column(TypeName = "money")]
    public decimal FinalSum { get; set; }
    public DateTime DateFrom { get; set; }
    public DateTime DateTo { get; set; }
    public int? InsuredID { get; set; }
    public Insured? Insured { get; set; }
    public int? InsuranceSubjectID;
    public InsuranceSubject? InsuranceSubject { get; set; }
    public int? InsuranceSubtypeID;
    public InsuranceSubtype? InsuranceSubtype { get; set; }
}

public class InsuranceSubject
{
    public int InsuranceSubjectID { get; set; }
    [Required]
    [StringLength(50)]
    public string Title { get; set; }
    public string Description { get; set; }
    [DataType(DataType.Currency)]
    [Column(TypeName = "money")]
    public decimal EstimatedValue { get; set; }
    public int? InsuredID;
    public Insured? Insured;
    public int? InsuranceSubjectTypeID { get; set; }
    public InsuranceSubjectType? InsuranceSubjectType { get; set; }
    public ICollection<InsurancePolicy>? InsurancePolicies { get; set; }
}

I tried my code without the foreign key properties and only left the reference navigation property (deleted the int and left the object) and it worked okay, but I need FK ints for future implementation.

Here is GitHub repo link for more insight (branch model-extension): https://github.com/lenartgolob/Insurance-IS/tree/model-extension

Upvotes: 16

Views: 24970

Answers (9)

granadaCoder
granadaCoder

Reputation: 27904

Ok.. For me.

I had Parent/Child relationship.

And I had put in properties (on the poco's) on (all the) scalar's (on the child) (where one of those scalars was the parent-FK (as int)), and had defined the ORM mapping (again, on the child) for this (same) scalar FK "int".

public class MyParent
{
    public int MyParentKey { get; set; }


    public ICollection<MyChild> MyKids { get; set; }

}


public class MyChild
{
    public int MyChildKey { get; set; }

    public int MyParentKey { get; set; } /* FK Scalar */

    public MyParent TheMyParent { get; set; }

}

(repeating myself) : I had Orm-Mapped the MyParentKey the SCALAR (in this case an "int") on the MyChildOrmMap.

But I had NOT orm-mapped the OBJECT (in this case the "MyParent" object).. via the property that pointed to that object (in this case the "TheMyParent" property).

Once I coded the 1:N relationship for the OBJECT (in this case the 'TheMyParent') (and the reciprocal MyKids), the error went away.

Aka, I was missing the below "HasOne/WithMany" orm-mapping (the below code would be in the MyChildMap (the fluent orm map)

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

        builder.HasOne<MyParent>(me => me.TheMyParent)
            .WithMany(parent => parent.MyKids)
            .HasForeignKey(me => me.MyParentKey)
            .HasConstraintName("FK_MyChild_To_MyParent_MyParentKey");

Upvotes: 19

stillLearning
stillLearning

Reputation: 1

I had a similiar problem for PositionId and PlayerId. However MatchId was fine.

namespace example.Models
{
    public class MatchPlayer
    {
        //not important part


        public int PositionId;
        public virtual Position? Position { get; set; }
        
        public int MatchId { get; set; }
        public virtual Match Match { get; set; } = null!;


        public int PlayerId;
        public virtual Player Player { get; set; } = null!;
    }
}

Correct code:

namespace example.Models
{
    public class MatchPlayer
    {
        //not important part


        public int PositionId { get; set; }
        public virtual Position? Position { get; set; }

        public int MatchId { get; set; }
        public virtual Match Match { get; set; } = null!;


        public int PlayerId { get; set; }
        public virtual Player Player { get; set; } = null!;
    }
}

My problem was using public int PositionId; instead of public int PositionId { get; set; }

Lenart Golob had issue with InsurancePolicy.InsuranceSubjectID1 so correct code should look like this:

public class InsurancePolicy
{
    public int InsurancePolicyID { get; set; }
    [DataType(DataType.Currency)]
    [Column(TypeName = "money")]
    public decimal FinalSum { get; set; }
    public DateTime DateFrom { get; set; }
    public DateTime DateTo { get; set; }
    public int? InsuredID { get; set; }
    public Insured? Insured { get; set; }
    //public int? InsuranceSubjectID; problematic fragment
    public int? InsuranceSubjectID { get; set;} //should resolve the issue
    public InsuranceSubject? InsuranceSubject { get; set; }
    public int? InsuranceSubtypeID; //and i think this should be changed as well
    //public int? InsuranceSubtypeID { get; set;}
    //if there's problem with InsurancePolicy.InsuranceSubtypeID 
    public InsuranceSubtype? InsuranceSubtype { get; set; }
}

Upvotes: 0

Andrii Hnatushchenko
Andrii Hnatushchenko

Reputation: 71

This error could occur if the HasOne method is used without providing a lambda expression that defines the relationship between entities. Try using

builder.HasOne<MyParent>(me => me.TheMyParent);

instead of

builder.HasOne<MyParent>()

Upvotes: 6

TsvetiZlateva
TsvetiZlateva

Reputation: 380

The exception says that there are three options. The property:

  1. is either not mapped,
  2. is already used for another relationship, or
  3. is incompatible with the associated primary key type

Here the problem is the third one: your PK InsuranceSubjectID is of type int, but you are trying to map it to FK, that is int?. Make the FK not nullable and it should work as expected.

Upvotes: 0

Alisson Honostorio
Alisson Honostorio

Reputation: 121

For me, my mistack was not give the WithOne relation letting the EF try to create the relation for his self, like this:

            modelBuilder.Entity<Catalog>(builder =>
            {
                builder.ToTable("Catalogs");
                builder
                    .HasMany(catalog => catalog.Products)
                    .WithOne()
                    .HasForeignKey(product => product.CatalogId)
                    .IsRequired();
            });

And when i changed for this syntax the migration executed with success and without duplicated property:

            modelBuilder.Entity<Catalog>(builder =>
            {
                builder.ToTable("Catalogs");
                builder
                    .HasMany(catalog => catalog.Products)
                    .WithOne(product => product.Catalog)
                    .HasForeignKey(product => product.CatalogId)
                    .IsRequired();
            });

Upvotes: 9

deepak revidagar
deepak revidagar

Reputation: 11

for me it was repetition of key.

        modelBuilder.Entity<PokemonCategory>()
            .HasKey(pc => new { pc.pokemonId, pc.categoryId });
        modelBuilder.Entity<PokemonCategory>()
            .HasOne(p => p.Pokemon)
            .WithMany(pc => pc.PokemonCategories)
            .HasForeignKey(p => p.pokemonId);
        modelBuilder.Entity<PokemonCategory>()
            .HasOne(p => p.Pokemon)       //it should be p.Category
            .WithMany(pc => pc.pokemonCategories)
            .HasForeignKey(c => c.categoryId);

Upvotes: 0

Edi
Edi

Reputation: 2301

For me was that I mistakenly referred the same entity twice:



        modelBuilder.Entity<ProductCategoryProduct>()
            .HasKey(tl => new {tl.ProductCategoryId, tl.ProductId});
        modelBuilder.Entity<ProductCategoryProduct>()
            .HasOne(t => t.ProductCategory)
            .WithMany(t => t.ProductCategoryProducts)
            .HasForeignKey(t => t.ProductCategoryId)
            .OnDelete(DeleteBehavior.Restrict);
        modelBuilder.Entity<ProductCategoryProduct>()
            .HasOne(p => p.ProductCategory) // this should be Product
            .WithMany(p => p.ProductCategoryProducts)
            .HasForeignKey(p => p.ProductId)
            .OnDelete(DeleteBehavior.Restrict);

Upvotes: 1

Edi
Edi

Reputation: 2301

For me the problem was the type of property ID in the FK part differed from the one in PK entity part:

    [Range(1, int.MaxValue, ErrorMessage = "Please provide valid payment id")]
    public int PaymentId { get; set; } // NOTICE INT HERE SHOULD BE LONG

    public virtual Payment Payment { get; set; }

where Payment had long property for ID:

public class Payment {
 public long Id {get;set;}
 //...
}

Upvotes: 2

Lenart Golob
Lenart Golob

Reputation: 191

I added public virtual in front of my reference navigation properties and it solved the issue.

Upvotes: 0

Related Questions