Forum Discussion

Ricardo Peres's avatar
Ricardo Peres
Copper Contributor
May 20, 2024
Solved

EF Core : foreign key column with same name as existing navigation property

 I want to use EF Core to map a legacy database. I cannot scaffold as there are hundreds of tables and I am only interested in very few and besides scaffolding generates names for the navigation properties which are not the ones I want. The situation is: I have a required many-to-one relationship linked by a column name that matches the name of the property that I want to give.

The code (simplified) is:

 

public class EntityProperty
{
    public ControlType ControlType { get; set; }
} 

public class ControlType
{
    public List<EntityProperty> Properties { get; set; } = new List<EntityProperty>();
}

Nothing more is required, eg, I'm not using foreign key properties, just navigation. 

The problem is, the foreign key column from the EntityProperties table to the ControlTypes is also called ControlType.

So, when I try to map it as:

 

builder.HasOne(x => x.ControlType)
       .WithMany(x => x.Properties)
       .IsRequired()
       .HasForeignKey("ControlType");

 I want to use EF Core to map a legacy database. I cannot scaffold as there are hundreds of tables and I am only interested in very few. The situation is: I have a required many-to-one relationship linked by a column name that matches the name of the property that I want to give.

The code (simplified) is:

public class EntityProperty
{
    public ControlType ControlType { get; set; }
} 

public class ControlType
{
    public List<EntityProperty> Properties { get; set; } = new List<EntityProperty>();
}

The problem is, the foreign key column from the EntityProperties table to the ControlTypes is also called ControlType.

So, when I try to map it as:

builder.HasOne(x => x.ControlType)
       .WithMany(x => x.Properties)
       .IsRequired()
       .HasForeignKey("ControlType");

I get the following exception:

 

InvalidOperationException: The property or navigation 'ControlType' cannot be added to the 'EntityProperty' type because a property or navigation with the same name already exists on the 'EntityProperty' type.'

 

The problem is with HasForeignKey, I guess it's because I am adding a shadow property when a "physical" property already exists, but I what I really mean to say is to use a different column name to link the two entities: what I want is to be able to specify a "not standard" column name for the foreign key, which I am not able to. I do not have a foreign key property, only a navigation property, and I do not want to have one. 

  • Found a way: define a shadow property with some random name that maps to the actual physical column and use it.
    As this:

     

    builder.Property("ControlTypeId").HasColumnType("int").HasColumnName("ControlType");
    builder.HasOne(x => x.ControlType).WithMany(x => x.Properties).IsRequired().HasForeignKey("ControlTypeId");

     

    This way I never have any "physical" foreign property polluting my model other than the navigation property, and I can give it any name I want,


1 Reply

  • Ricardo Peres's avatar
    Ricardo Peres
    Copper Contributor

    Found a way: define a shadow property with some random name that maps to the actual physical column and use it.
    As this:

     

    builder.Property("ControlTypeId").HasColumnType("int").HasColumnName("ControlType");
    builder.HasOne(x => x.ControlType).WithMany(x => x.Properties).IsRequired().HasForeignKey("ControlTypeId");

     

    This way I never have any "physical" foreign property polluting my model other than the navigation property, and I can give it any name I want,


Resources