Entity Framework : Invalid column name *_ID even with fluent api or data annotations

Question

Odd issue that I've been looking at all day. I am working with Entity Framework 6. The issue I have is that I have three entities:

public partial class Order : ILocationBearingObject
{
    public int Id { get; set; }
    // other properties and relationships here
    public int? OrderProfileId { get; set; }
    public int OrderTemplateId { get; set; }

    public virtual OrderProfile Profile { get; set; } // optional property
    public virtual OrderTemplate OrderTemplate{ get; set; }
}

public class OrderProfile 
{
    public int Id { get; set; }
    // other properties
    
    // added here 6/15/2021
    public virtual OrderTemplate OrderTemplate{ get; set; }
}

public class OrderTemplate : EntityMetaData
{
    public int Id { get; set; }
    // other properties

    public int? OrderProfileId{ get; set; }
    public OrderProfile OrderProfile { get; set; }
}

In our model builder, we have these definitions:

modelBuilder.Entity<Order>()
    .HasOptional(x => x.OrderProfile)
    .WithMany(x => x.Orders)
    .HasForeignKey(x => x.OrderProfileId);

modelBuilder.Entity<OrderProfile>()
    .HasOptional(x => x.OrderTemplate)
    .WithOptionalPrincipal(x => x.OrderProfile);

But even with the above fluent api model, we get the error

Invalid column name 'OrderProfile_Id'

Throughout various testing I was unable to find why this issue was occurring, so I looked at our logs and found when this error started popping it's head up and then was able to find the changes associated to OrderProfile and found that the only change that was made was adding the relationship from OrderProfile to OrderTemplate.

When I removed that fluent api relationship OrderProfile to OrderTemplate, it worked as expected... I don't need that relationship to OrderTemplate, but would like it to be there, how can I establish a optional 1 to optional 1 relationship without breaking other relationships? Also, why would additional relationships be effected by this?

UPDATE 6/15/2021 So I found I had a reverse navigation property in the OrderProfile model:

public virtual OrderTemplate OrderTemplate{ get; set; }

removing that and the associated fluent relationship

        modelBuilder.Entity<OrderProfile>()
            .HasOptional(x => x.OrderTemplate)
            .WithOptionalPrincipal(x => x.OrderProfile);

Doing the above resolved the issue, but for some reason, the issue seems to have cascaded down to another relationship that has a circular reference like the above. The Order class is involved with this cascaded issue. I guess this is a pretty big cause for concern since this application worked fine for the last 4 years and for these relationships to be decaying like this is worrisome. Does anyone know why this is happening?

Answer 1

if you use the right naming convention, EF will do magic. in this sample, you don't need fluent API to relate entities.

public partial class Order : ILocationBearingObject
{
    public int Id { get; set; }
    public int? OrderProfileId { get; set; } //means HasOptional (nullable) and ForeignKey

    //variable name must be OrderProfile not Profile
    public virtual OrderProfile OrderProfile { get; set; } 
}

public class OrderProfile
{
    public OrderProfile()
    {
       Orders = new HashSet<Order>();
    }

    public int Id { get; set; }

    //be aware circular reference at any conversion or mapping
    public virtual ICollection<Order> Orders {get; set;} //means WithMany
}

Answer 2

I've got an error like this too. It's caused by unmatching OrderProfileId property in OrderTemplate class with the fluent api model

If I'm not wrong, you want the OrderProfile model a many to many relation between Order and OrderTemplate. Then if it was the case, add the nvaigation property in OrderProfile.

public class OrderProfile 
{
    public int Id { get; set; }
    // other properties

    public virtual ICollection<Order> Orders { get; set; }
    public virtual OrderTemplate OrderTemplate { get; set; }
}

Then change the fluent api model to be like this

// the EF has modelled the relation for normal 1 to many relation
// modelBuilder.Entity<Order>()
//     .HasOptional(x => x.OrderProfile)
//     .WithMany(x => x.Orders)
//     .HasForeignKey(x => x.OrderProfileId);

modelBuilder.Entity<OrderTemplate>()
    .HasOptional(x => x.OrderProfile)
    .WithOptional(x => x.OrderTemplate);

Answer 3

You're working database-first, which always leaves room for a mismatch between the actual database model and the model EF infers from class and property names and mapping code (= conceptual model). If this happens, it may help to make EF generate a database from the conceptual model and see where it creates the column it expects, OrderProfile_Id.

This is what you'll see when logging the SQL statements:

CREATE TABLE [dbo].[OrderTemplates] (
    [Id] [int] NOT NULL IDENTITY,
    [OrderProfileId] [int],
    [OrderProfile_Id] [int],
    CONSTRAINT [PK_dbo.OrderTemplates] PRIMARY KEY ([Id])
)

...

ALTER TABLE [dbo].[OrderTemplates]
    ADD CONSTRAINT [FK_dbo.OrderTemplates_dbo.OrderProfiles_OrderProfile_Id]
    FOREIGN KEY ([OrderProfile_Id]) REFERENCES [dbo].[OrderProfiles] ([Id])

There you see the expected nullable column OrderProfile_Id which is the FK to OrderProfiles. It's noteworthy to see that EF does not use OrderProfileId as a foreign key field. It's just a field that could be used for anything.

That's because EF6 doesn't support 1:1 associations as foreign key associations (reference property and primitive FK property).

Knowing this, the remedy is simple: remove the property OrderTemplate.OrderProfileId and tell EF to use the field OrderTemplate.OrderProfileId in the database:

modelBuilder.Entity<OrderProfile>()
    .HasOptional(x => x.OrderTemplate)
    .WithOptionalPrincipal(x => x.OrderProfile)
        .Map(m => m.MapKey("OrderProfileId"));

That said, I wonder why Order has a foreign key to OrderProfile. Isn't its OrderProfile determined by its OrderTemplate? If it's a redundant relationship it may be better to remove it.