EF Core 5 HasForeignKey Problem with Differing Column Name

user_9836310 0
Viewed 725

Environment: .Net 5.0, EntityFrameworkCore 5.0, MS Sql Server

I've set up a one-to-many relationship in my database (using HasForeignKey), but when I try to access the parent record from the child using .Include(x => x.Parent) I get an invalid column name error. I've done this type of thing many times in the past, but it was always with the Child table having a property (and FK on said property) called ParentTableName + Id. In this case where it's failing, the property doesn't match that pattern.

So my question is: why is EF ignoring my Fluent API configuration? It seems to me that it shouldn't matter what the column is called if I've set up the relationship correctly (and the data types on each column match). Is there some additional step I need to take in the fluent api setup? See example below:

public class Parent
{
    public int Id { get; set; }
    public IEnumerable<Child> Children { get; set; }
}

public class Child 
{
    public int Id { get; set; }
    public int ForeignKeyName { get; set; } // This could say anything, just not "ParentId"
    
    [NotMapped] // Included so add-migration doesn't try to create a mapping, but when removed I get the same error
    public Parent Parent { get; set; }
}

And my configuration

modelBuilder.Entity<Parent>(entity =>
{
    entity.HasMany<Child>()
    .WithOne(x => x.Parent)
    .HasForeignKey(x => x.ForeignKeyName);
});

The usage that fails:

var children = _context.Children.Include(x => x.Parent).ToList(); // Says "Invalid Column Name 'ParentId'"

The foreign key is set up on the database already, but when I try to run add-migration it still suggests creating a) a new column on Child called ParentId (why?), b) an index on said column, and c) a foreign key using ParentId. I'd rather understand what's going on here than just change the property name and remain blissfully ignorant.

EDIT Updating to make answer clear since comment thread is long. The key was to make sure that [NotMapped] is on both navigation properties, AND (in answer below) to have the fluent API reference the relationship in both directions (Parent to Child and Child to Parent).

entity-framework-core .net-core entity-framework c#
1 Answers

This code was tested in VS 2019. Data base with ForeignKeyName ForeignKey was created successfully.

you have to fix the classes

public class Parent
{
   [Key]
    public int Id { get; set; }
    [InverseProperty(nameof(Child.Parent))]
    public IEnumerable<Child> Children { get; set; }
}

public class Child 
{
    [Key]
    public int Id { get; set; }

    public int ForeignKeyName { get; set; } 

    [ForeignKey(nameof(ForeignKeyName))]
    [InverseProperty("Children")]
    public Parent Parent { get; set; }
}

or db context

          modelBuilder.Entity<Child>(entity =>
            {
                entity.HasOne(d => d.Parent)
                   .WithMany(p => p.Children)
                   .HasForeignKey(d => d.ForeignKeyName);
            });

you have to delete a previous Migrations folder and delete a previous database too. After this make a clear migration using Add-Migration InitialCreate. And after this use Update-Database.

user_113922900
Related