Efcore: EF mismatches the inherited navigation property with the inverse key property

[Sojaner]

Steps to reproduce
Consider these View Models:

``` C#
public abstract class PersonBaseViewModel
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string IdNumber { get; set; }

public virtual CityViewModel CityVM { get; set; }

}

public abstract class ServicePersonViewModel: PersonBaseViewModel
{
public string PersonnelNumber { get; set; }
}

public class CitizenViewModel : PersonBaseViewModel
{
//...
}

public class PoliceViewModel : ServicePersonViewModel
{
//...
}

public class CityViewModel
{
public int Id { get; set; }

public ICollection<CitizenViewModel> People { get; set; }
public ICollection<PoliceViewModel> Securities { get; set; }

}

The migrations being generated include codes similar to these:

``` C#
modelBuilder.Entity("PersonBaseViewModel", b =>
{
   //...

   b.Property<int?>("CityVMId");

   //...

   b.HasIndex("CityVMId");

   //...
});

modelBuilder.Entity("CitizenViewModel", b =>
{
   b.HasBaseType("PersonBaseViewModel");

   //...

   b.Property<int?>("CityViewModelId");

   //...

   b.HasIndex("CityViewModelId");

   //...
 });

modelBuilder.Entity("PoliceViewModel", b =>
{
   b.HasBaseType("ServicePersonViewModel");

   //...

   b.Property<int?>("CityViewModelId");

   //...

   b.HasIndex("CityViewModelId");

   //...
 });

 //--------------------------------------------------

modelBuilder.Entity("PersonBaseViewModel", b =>
{
   b.HasOne("CityViewModel", "CityVM")
    .WithMany()
    .HasForeignKey("CityVMId");
});

modelBuilder.Entity("CitizenViewModel", b =>
{
   b.HasOne("CityViewModel", "CityVM")
    .WithMany()
    .HasForeignKey("CityViewModelId");
});

modelBuilder.Entity("PoliceViewModel", b =>
{
   b.HasOne("CityViewModel", "CityVM")
    .WithMany()
    .HasForeignKey("CityViewModelId");
});

//--------------------------------------------------

migrationBuilder.CreateTable(
name: "PersonBases",
columns: table => new
{
    //...

       CityVMId = table.Column<int>(nullable: true),

    //...

    CityViewModelId = table.Column<int>(nullable: true),

    //...
},
constraints: table =>
{
    table.PrimaryKey("PK_PersonBases", x => x.Id);
    table.ForeignKey(
        name: "FK_PersonBases_Cities_CityVMId",
        column: x => x.CityVMId,
        principalTable: "Cities",
        principalColumn: "Id",
        onDelete: ReferentialAction.Restrict);
    table.ForeignKey(
        name: "FK_PersonBases_Cities_CityViewModelId",
        column: x => x.CityViewModelId,
        principalTable: "Cities",
        principalColumn: "Id",
        onDelete: ReferentialAction.Restrict);
});

The issue
As you can see, there are two different foreign keys being generated between Cities and PersonBases tables, however the CityVMId column is always empty and contains NULLs.

Removing the extra foreign key from migrations fixes the database creation, however when inserting data into the table using EF Core, an exception with message 'Column CityVMId could not be found' (Or something similar) is being raised.

Therefore, an empty column for CityVMId must be existing in the database for the EF Core to work when inserting/updating data.

Further technical details
Dot Net Core version: 1.0.1 (1.0.0-preview2-003131/DotNetCore.1.0.1-VS2015Tools.Preview2.0.2)
Entity Framework Core version: 1.0.1 (1.0.0-preview2-21431)
Operating system: Windows 10 x64
Visual Studio version: VS 2015 Update 3

[Sojaner]

Suggested workaround by @AndriySvyryd is:

C# modelBuilder.Entity<CitizenViewModel>().HasOne(c => c.CityVM).WithMany(c => c.People); modelBuilder.Entity<PoliceViewModel>().HasOne(p => p.CityVM).WithMany(c => c.Securities);

However, I haven't been able to try it yet. I will report as soon as I try and can confirm.

[Sojaner]

The workaround fails.

When adding the suggested code, the migrations tool throws this exception and does not generate the migrations.

The navigation property 'People' cannot be added to the entity type 'CityViewModel' because its CLR type 'ICollection<CitizenViewModel>' does not match the expected CLR type 'PersonBaseViewModel'.

Or when the code sequence is altered, this exception:

The navigation property 'Securities' cannot be added to the entity type 'CityViewModel' because its CLR type 'ICollection<PoliceViewModel>' does not match the expected CLR type 'PersonBaseViewModel'.

Obviously regarding that there are no migrations being generated, I haven't been able to test the double foreign key creation nor the database data insert or update exception yet.

Seems like more than one problem is involved in such scenarios.

[Sojaner]

I have created a code sample project for this issue here which also includes some descriptions and workaround codes (faulty ones) in this file.

Hope it help fixing the issue easier.

[AndriySvyryd]

@Sojaner Thanks for the great repro project. The second workaround could actually work if you ignore the base property first:

C# builder.Entity<PersonBaseViewModel>().Ignore(model => model.CityVM); builder.Entity<CityViewModel>().HasMany(model => model.People).WithOne(model => model.CityVM).HasForeignKey(model => model.CityVMId); builder.Entity<CityViewModel>().HasMany(model => model.Medics).WithOne(model => model.CityVM).HasForeignKey(model => model.CityVMId); builder.Entity<CityViewModel>().HasMany(model => model.Securities).WithOne(model => model.CityVM).HasForeignKey(model => model.CityVMId);

[Sojaner]

You are welcome @AndriySvyryd hope it helps finding the problem faster.

About ignoring the property, I haven't tried it yet, however, I had not noticed that Entity<> offers an Ignore method itself, I was trying something like builder.Entity<PersonBaseViewModel>().Property(model => model.CityVM).Ignore(); and if I remember correctly it was raising an exception something like Can not add the property CityVM to PersonBaseViewModel because it already exists..

I don't really know the whole strategy here but from the developer's experience point of view, looks like builder.Entity<PersonBaseViewModel>().Property(model => model.CityVM).Ignore(); and builder.Entity<PersonBaseViewModel>().Ignore(model => model.CityVM); should be working the same way to me. :)

I will report here if the Ignore method works as a workaround for me or not as soon as I try it. :) (Y)

[Sojaner]

@AndriySvyryd unfortunately the second workaround doesn't work either.

I have updated the code on repo to apply the Ignore method, however it doesn't have any effect on the issue and I still receive the following message:
Unable to determine the relationship represented by navigation property 'PersonBaseViewModel.CityVM' of type 'CityViewModel'. Either manually configure the relationship, or ignore this property from the model.

If you pull the changes and try the dotnet ef migrations remove command, you will see the exception.

Practically none of the workarounds has worked for me so far and my database structure is still problematic.

Is there any chance for solving this problem sometime soon? I really need to progress on this project.

Thanks in advance.

[AndriySvyryd]

@Sojaner I see, that workaround only works for 1.1.0 nightly build. For 1.0.0 this seems to work:

C# builder.Entity<CityViewModel>().HasMany(model => model.People).WithOne(model => model.CityVM) .HasForeignKey(model => model.CityVMId).ForSqlServerHasConstraintName("FK_CitizenBaseViewModel_Cities_CityVMId"); builder.Entity<CityViewModel>().HasMany(model => model.Medics).WithOne(model => model.CityVM).OnDelete(DeleteBehavior.Restrict) .HasForeignKey(model => model.CityVMId).ForSqlServerHasConstraintName("FK_DoctorBaseViewModel_Cities_CityVMId"); builder.Entity<CityViewModel>().HasMany(model => model.Securities).WithOne(model => model.CityVM).OnDelete(DeleteBehavior.Restrict) .HasForeignKey(model => model.CityVMId).ForSqlServerHasConstraintName("FK_PoliceBaseViewModel_Cities_CityVMId"); builder.Entity<PersonBaseViewModel>().Ignore(model => model.CityVM);

[Sojaner]

This doesn't work either @AndriySvyryd

I have updated the repo to include the new code but here is the summery.

``` C#
//This solution doesn't work either. Seems like because we are ignoring the base class, EF has problem finding what it should cast the values to and tries the first type it finds.
//Exception: Unable to cast object of type 'CitySample.Models.CityViewModels.CitizenViewModel' to type 'CitySample.Models.CityViewModels.PoliceViewModel'.
builder.Entity().HasMany(model => model.People).WithOne(model => model.CityVM).HasForeignKey(model => model.CityVMId).HasConstraintName("FK_PersonBaseViewModel_Cities_CityVMId").OnDelete(DeleteBehavior.Restrict);
builder.Entity().HasMany(model => model.Medics).WithOne(model => model.CityVM).HasForeignKey(model => model.CityVMId).HasConstraintName("FK_PersonBaseViewModel_Cities_CityVMId").OnDelete(DeleteBehavior.Restrict);
builder.Entity().HasMany(model => model.Securities).WithOne(model => model.CityVM).HasForeignKey(model => model.CityVMId).HasConstraintName("FK_PersonBaseViewModel_Cities_CityVMId").OnDelete(DeleteBehavior.Restrict);
builder.Entity().Ignore(model => model.CityVM);

//=============================================

//This solution doesn't work either. Having different FKs for each type doesn't change anything except for the duplicated FK exception raised by dotnet ef database update.
//Exception: Unable to cast object of type 'CitySample.Models.CityViewModels.CitizenViewModel' to type 'CitySample.Models.CityViewModels.PoliceViewModel'.
builder.Entity().HasMany(model => model.People).WithOne(model => model.CityVM).HasForeignKey(model => model.CityVMId).HasConstraintName("FK_CitizenViewModel_Cities_CityVMId").OnDelete(DeleteBehavior.Restrict);
builder.Entity().HasMany(model => model.Medics).WithOne(model => model.CityVM).HasForeignKey(model => model.CityVMId).HasConstraintName("FK_DoctorViewModel_Cities_CityVMId").OnDelete(DeleteBehavior.Restrict);
builder.Entity().HasMany(model => model.Securities).WithOne(model => model.CityVM).HasForeignKey(model => model.CityVMId).HasConstraintName("FK_PoliceViewModel_Cities_CityVMId").OnDelete(DeleteBehavior.Restrict);
builder.Entity().Ignore(model => model.CityVM);

//=============================================

//Not working either. Back to first place! The same problem with two different FKs.
builder.Entity().HasMany(model => model.People).WithOne(model => model.CityVM).HasForeignKey(model => model.CityVMId).HasConstraintName("FK_CitizenViewModel_Cities_CityVMId").OnDelete(DeleteBehavior.Restrict);
builder.Entity().HasMany(model => model.Medics).WithOne(model => model.CityVM).HasForeignKey(model => model.CityVMId).HasConstraintName("FK_DoctorViewModel_Cities_CityVMId").OnDelete(DeleteBehavior.Restrict);
builder.Entity().HasMany(model => model.Securities).WithOne(model => model.CityVM).HasForeignKey(model => model.CityVMId).HasConstraintName("FK_PoliceViewModel_Cities_CityVMId").OnDelete(DeleteBehavior.Restrict);
builder.Entity().HasOne(model => model.CityVM).WithMany().HasForeignKey(model => model.CityVMId).HasConstraintName("FK_PersonBaseViewModel_Cities_CityVMId").OnDelete(DeleteBehavior.Restrict);

//=============================================

//Defining the base types don't work either. Still receiving the following exception.
//Exception: Unable to cast object of type 'CitySample.Models.CityViewModels.CitizenViewModel' to type 'CitySample.Models.CityViewModels.PoliceViewModel'.
builder.Entity().HasMany(model => model.People).WithOne(model => model.CityVM).HasForeignKey(model => model.CityVMId).HasConstraintName("FK_CitizenViewModel_Cities_CityVMId").OnDelete(DeleteBehavior.Restrict);
builder.Entity().HasMany(model => model.Medics).WithOne(model => model.CityVM).HasForeignKey(model => model.CityVMId).HasConstraintName("FK_DoctorViewModel_Cities_CityVMId").OnDelete(DeleteBehavior.Restrict);
builder.Entity().HasMany(model => model.Securities).WithOne(model => model.CityVM).HasForeignKey(model => model.CityVMId).HasConstraintName("FK_PoliceViewModel_Cities_CityVMId").OnDelete(DeleteBehavior.Restrict);
builder.Entity().HasBaseType();
builder.Entity().HasBaseType();
builder.Entity().HasBaseType();
builder.Entity().HasBaseType();
builder.Entity().Ignore(model => model.CityVM);
```

I have also updated the index view and included a link to click and insert some data into the database so you can see the exception.

Also, I think this had not been discovered before the latest nightly build either so I don't see any reason for trying it with the nightly build assemblies yet.

[AndriySvyryd]

@Sojaner You are hitting a different bug now. Try adding the persons after saving the city to the database, see attached.
Also you would need to change the max length for PersonnelNumber and CellphoneNumber:

``` C#
builder.Entity().Property(model => model.PersonnelNumber).HasMaxLength(50);
builder.Entity().Property(model => model.CellphoneNumber).HasMaxLength(50);

```

HomeController.cs.txt

[Sojaner]

Thanks @AndriySvyryd the code is finally working.

I have also updated the repo for your further investigation or anyone else who is interested.

However I appreciate it if you can point me to the problematic part of the code inside HomeController.cs so I will know what to avoid next time in similar situation. Was it because of the One-to-One relationship between RegionViewModel and CityViewModel that I had to save and load the City instance for it to work or was it because of the PersonBaseViewModel based hierarchy of classes which are being added to the CityVM instance?

Thanks for all the follow ups and help, and I hope this thread will helps you fix these issues faster and easier.

PS: I should leave this thread open for you to close it when the bug is fixed, right?

[AndriySvyryd]

@Sojaner It's because CityViewModel participates in different relationships to entity types in the same hierarchy.
Yes, leave this open so we can make sure all these issues get fixed.

[srungta]

@Sojaner @AndriySvyryd
I have a similar issue.
A part of our models is as below.

    public abstract class IntentBaseModel 
    {
        /// <inheritdoc />
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid Id { get; set; }
    }

    [Table("IntentStaging")]
    public class IntentStaging : IntentBaseModel
    {
        /// <summary>
        /// Gets or sets foreign key reference in topic table.
        /// </summary>
        /// <value>
        /// The topics associated with this intent.
        /// </value>
        public virtual ICollection<TopicStaging> Topics { get; set; }
    }

    [Table("TopicStaging")]
    public class TopicStaging 
    {
       /// <inheritdoc />
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid Id { get; set; }
    }

This created a migration that added two foreign keys IntentId and IntentStagingId to the TopicStaging table.The migrations have already been applied to the database. Is there any way to drop the extra IntentStagingId column and maintain a single reference?

My main concern is that when i try to include the topics from intents, it refers to the IntentStagingId column which always has a null value.
Any help would be appreciated.

[Sojaner]

@srungta I highly recommend it to start using the fluent api for configuring your model-first ef.
I'm not at my computer right now so I can't write the piece of code needed but I'm pretty sure that you will need to define the parent-child inheritance (in my preference using the fluent api) before it would start working properly.

Also, using the fluent api, you can define the keys including the foreign keys and principle keys and what their name should be. Therefore you can control the generated keys and avoid the double key generations.

Take a look at this document here: https://docs.microsoft.com/en-us/ef/core/modeling/relationships

[ajcvickers]

@srungta Those types for me result in the following model:

Model: 
  EntityType: IntentStaging
    Properties: 
      Id (Guid) Required PK AfterSave:Throw ValueGenerated.OnAdd 0 0 0 -1 0
    Navigations: 
      Topics (<Topics>k__BackingField, ICollection<TopicStaging>) Collection ToDependent TopicStaging 0 -1 1 -1 -1
    Keys: 
      Id PK
  EntityType: TopicStaging
    Properties: 
      Id (Guid) Required PK AfterSave:Throw ValueGenerated.OnAdd 0 0 0 -1 0
      IntentStagingId (no field, Nullable<Guid>) Shadow FK Index 1 1 1 0 1
    Keys: 
      Id PK
    Foreign keys: 
      TopicStaging {'IntentStagingId'} -> IntentStaging {'Id'} ToDependent: Topics

There is only a single FK generated, as expected. If you are seeing otherwise, then please file a new issue with a runnable project/solution or complete code listing that demonstrates the behavior.

This is my repro code:
```C#
public abstract class IntentBaseModel
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Id { get; set; }
}

[Table("IntentStaging")]
public class IntentStaging : IntentBaseModel
{
public virtual ICollection Topics { get; set; }
}

[Table("TopicStaging")]
public class TopicStaging
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Id { get; set; }
}

public class BloggingContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(@"Server=(localdb)mssqllocaldb;Database=Test;ConnectRetryCount=0");
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<IntentStaging>();
    modelBuilder.Entity<TopicStaging>();
}

}

public class Program
{
public static void Main()
{
using (var context = new BloggingContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
}
}
}
```