CODEWITHSUNDEEP
entity-framework
frumious
frumious

Reputation: 1575

Why is Entity Framework looking for the wrong foreign key column?

I've seen various questions on related topics, which seem like they would address my issue, but nothing I try seems to help.

I have an EF (6.1.3) model of an existing DB, which has been working fine. I've just added an additional column to a table, which represents a new relationship. Perhaps relevantly, the relationship is the second one between the two tables - the original Location is now joined by ActualDirectSite, both of them relating the Uniform and Location tables.

The moment I added the two new properties, ActualDirectSiteID and ActualDirectSite, my SELECT queries started failing with the error "Invalid column name 'Location_ID'". It's true that that column doesn't exist, but I don't see why EF is looking for it - it was happy before, but something has made it think the column name should be different. The failing name makes me think it's the original Location which is somehow no longer working.

Here's the Entity in question:

public partial class Uniform
{
    public int ID { get; set; }

    [Column("LocationID")]
    public int? LocationID { get; set; }

    [ForeignKey("LocationID")]
    public virtual Location Location { get; set; }

    public int? ActualDirectSiteID { get; set; }

    [ForeignKey("ActualDirectSiteID")]
    public virtual Location ActualDirectSite { get; set; }
}

And my (shortened) table def:

CREATE TABLE [dbo].[Uniforms](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [LocationID] [int] NULL,
    [ActualDirectSiteID] [int] NULL)

The obvious solution to relying on convention causing incorrect assumptions about column names is to specify them explicitly, and so I've tried using Column annotations, and also to make sure that the ID and navigation properties know about each other using ForeignKey, but no dice. Any ideas?

EDIT: added missing LocationID field (already present in full code)

EDIT2: to be clear, before I added ActualDirectSiteID to the Entity it all worked fine, with no annotations required. I've just had another look at the generated SQL, and it seems like the Location_ID reference corresponds to the ActualDirectSite property:

//[Extent1] is "Uniform"
... , [Extent1].[LocationID] AS [LocationID], [Extent1].[ActualDirectSiteID] AS [ActualDirectSiteID], [Extent1].[Location_ID] AS [Location_ID], //...[Extent4] begins

EDIT3: I didn't include any of my Location entity, here it is:

[Table("Location")]
public partial class Location
{
    public int ID { get; set; }

    public virtual ICollection<Uniform> Uniforms { get; set; }
}

Upvotes: 1

Views: 3304

Answers (2)

DevilSuichiro
DevilSuichiro

Reputation: 1059

As noted in the comments: with multiple navigation properties to the same table, EF will get confused as to which navigation property refers to which inverse navigation property and ignore the FK mapping of those. A similar issue I stumbled across some time ago can be found in this SO question.

There are only two ways (I know of) to fix this issue:

  • Ignore at least all but one of the navigation properties with [NotMapped] or .Ignore() or

  • Add a inverse navigation property to (at least) all but one navigation properties to this table and adjust the mapping accordingly.

Actually, this behavior smells like a bug on EF side (from a DB point of view, I don't see the problem there), but the workaround is simple enough.

Upvotes: 1

Muritiku
Muritiku

Reputation: 222

By convention every foreign key declaration include 2 properties.
If you create link to Location entity, then you must add property with name - LocationId type int. That is why you got an error
ForeignKey annotation is used to specify the name of used int id property for link (if you plan to use different name)
You can declare foreign key only like here:

public Location Location {get; set;}
public int LocationId {get; set;}

Or like here:

[ForeignKey("CustomIdProperty")]
public Location Location {get; set;}
public int CustomIdProperty {get; set;}

(Pardon me for possible typos - writting from phone)

Upvotes: 0

Related Questions