Invalid Column Name Error When Trying to Associate Objects with EntityFramework

Question

The entity framework is giving me some trouble.

Here are my tables:

Products

Products Table

Related Products

RelatedProducts Table

And my DB context (part of it, anyway)...

ApplicationContext.cs

public class ApplicationContext : DbContext
{
        public ApplicationContext() : base("DefaultConnection")
        {
        }

        public DbSet<Product> Products { get; set; }
        public DbSet<RelatedProduct> RelatedProducts { get; set; }
}

And the models...

Product.cs

public class Product
{
    public int ID              { get; set; }
    public string Manufacturer { get; set; }
    public string Model        { get; set; }
    public string PartNumber   { get; set; }
    public int CategoryID      { get; set; }
    public string Description  { get; set; }
    public decimal Price { get; set; }

    public virtual ICollection<RelatedProduct> RelatedProducts { get; set; }
}

RelatedProduct.cs

public class RelatedProduct
{
    public int ID        { get; set; }
    public int OwnerID   { get; set; }
    public int ProductID { get; set; }

    public virtual Product Owner { get; set; }
    public virtual Product Product { get; set; }
}

What I am trying to do

Loop through a list of related products like this:

<ul class="activity">
      @foreach (var related in @Model.RelatedProducts)
      {
             <li>
                  <i class="fa fa-chevron-right red"></i> <strong>@related.Product.Manufacturer:</strong> @related.Product.Model
             </li>
      }
</ul>

The Problem

I keep getting this errorL

{"Invalid column name 'Product_ID'.\r\nInvalid column name 'Product_ID'.\r\nInvalid column name 'Product_ID'."}

Any ideas?

Is this usign code first I assume? If so try create a brand new DB and run the EF migrations and see if it works. EF has very weird issues sometimes. What is likely happening is it added ProductID because of the Property but then expects Product_ID for the foreign key but that wasn't added. Very odd... Maybe try using the explicit Fluent API or the foreign key attribute — David Esteves, Jun 27 '14 at 15:29
@DavidEsteves Actually, I created the DB first, then added this code. Can you tell me more about the foreign key attribute? As silly as it is, I don't mind explicitly defining the foreign key here. — drewwyatt, Jun 27 '14 at 15:31
I've never actually done the DB first approach like this so I'm not completely sure. I've only done DB first where I created the EDMX and it generated all the code for me. The foreignkey attribute is just mapping the property to the navigation property. You would just add [ForeignKey("ProductID")] above the "Product" property. If that doesn't work, since you did mention doing the DB first, it may be an issue with a constraint not being in place or something like that as EF is very finicky. — David Esteves, Jun 27 '14 at 15:43

score 3 · Accepted Answer · answered Jun 27 '14 at 16:00

You must tell EF if Product.RelatedProducts is the inverse navigation property of RelatedProduct.Owner or of RelatedProduct.Product. Both would be possible and valid and EF can't decide it on its own.

Solution with data annotations (assuming Owner is the inverse of RelatedProducts):

[InverseProperty("RelatedProducts")]
public virtual Product Owner { get; set; }

public virtual Product Product { get; set; }

Or with Fluent API:

modelBuilder.Entity<RelatedProduct>()
    .HasRequired(r => r.Owner)
    .WithMany(p => p.RelatedProducts)
    .HasForeignKey(r => r.OwnerID);

modelBuilder.Entity<RelatedProduct>()
    .HasRequired(r => r.Product)
    .WithMany()
    .HasForeignKey(r => r.ProductID)
    .WillCascadeOnDelete(false);

Probably only the Fluent API solution will work because you also need to disable cascading delete for one of the relationships which isn't possible with data annotations.

score 2 · Answer 2 · answered Jun 27 '14 at 16:10

2

Actually, the problem here was Entity Framework's "conventions over configuration". The convention for a foreign key column, when it is not explicitly added as a property, is to name it [RelatedProperty]_[RelatedClassPK]. In your situation that would be Owner_ID and Product_ID. However, these columns don't exist on your tables, so you get an error. The usual fix is to just tell EF explicitly what your foreign key properties are:

[ForeignKey("Owner")]
public int OwnerID { get; set; }

public virtual Product Owner { get; set; }

With that, Entity Framework looks for an OwnerID column, instead, and all is good. @Slauma inadvertently solved the underlying problem with the Fluent API approach, which explicitly declares the properties to use for the foreign keys with HasForeignKey. This is an equally valid approach, but I felt you should know what was actually your problem.

answered Jun 27 '14 at 16:10

Chris Pratt

232,153
36
385
444

I'm relatively sure the FK names are not the reason for the problem, i.e. adding a `[ForeignKey]` attribute would not fix it. EF recognizes `OwnerID` and `ProductID` as FKs because the names follow the naming conventions. The real problem is that he has 2 nav. properties (of the same type) in one class and only 1 in the related class. The result is that EF cannot apply the `AssociationInverseDiscoveryConvention` (http://stackoverflow.com/a/6850572/270591) and creates one relationship for each navigation property, i.e. 3 relationships. For the third one it assumes the FK with underscore. – Slauma Jun 27 '14 at 20:03
@Slauma: The actual error the OP got said column Product_ID doesn't exist. This is very obviously because EF is looking for that foreign key column and *not* ProductID as the OP intended. – Chris Pratt Jun 27 '14 at 20:37
It's correct that EF is looking for a `Product_ID`, but incorrect that's it's *not* looking for `ProductID`. Actually EF is looking for *both*, a `ProductID` and a `Product_ID` column (plus `OwnerID` column). It expects 3 FK columns in the `RelatedProducts` table: `OwnerID` belonging to `RelatedProduct.Owner`, `ProductID` belonging to `RelatedProduct.Product` and `Product_ID` belonging to the `Product.RelatedProducts` collection. The point is the EF doesn't recognize that `RelatedProduct.Owner` (or `RelatedProduct.Product`) and `Product.RelatedProducts` are the ends of the *same* relationship. – Slauma Jun 27 '14 at 21:09
@Slauma: that might have also been a problem at some point but it's not what brought the OP here and it's *not* what that error is about. Heck, just try it for yourself. EF will not look for anything but a column name with an underscore for a foreign key without configuration to the contrary. – Chris Pratt Jun 27 '14 at 22:43
Tested it: Calling `context.RelatedProducts.ToString()` returns: `SELECT [Extent1].[ID] AS [ID], [Extent1].[OwnerID] AS [OwnerID], [Extent1].[ProductID] AS [ProductID], [Extent1].[Product_ID] AS [Product_ID] FROM [dbo].[RelatedProducts] AS [Extent1]`. Putting the `[ForeignKey]` attribute on the properties has no effect. DB schema for `RelatedProducts` has 3 FK constraints for `OwnerID`, `ProductID`, `Product_ID`. – Slauma Jun 27 '14 at 23:12
BTW: The `HasForeignKey` in my Fluent mapping is as redundant as the `[ForeignKey]` attribute. You can actually omit it. Important is only that `Owner` and `RelatedProducts` are bound together to *one* relationship with `HasRequired(r => r.Owner).HasMany(p => p.RelatedProduct)`. That's the counterpart of the `[InverseProperty]` attribute and the only way to fix the problem, not the `[ForeignKey]` attribute nor `HasForeignKey`. – Slauma Jun 27 '14 at 23:12
@ChrisPratt You have come to my rescue on a few occasions, if it's not too much trouble, do you think you could take a look at this question that I posted earlier? http://stackoverflow.com/questions/24493507/how-do-i-get-the-entityframework-to-check-for-2-parameters – drewwyatt Jun 30 '14 at 17:04

Invalid Column Name Error When Trying to Associate Objects with EntityFramework

What I am trying to do

The Problem

2 Answers2

Linked