Entity Framework - Invalid Column Name '*_ID"

Question

Check to see if you have any ICollections.

What I have figured out is when you have an ICollection that references a table and there is no column that it can figure out, it creates one for you to try to make the connection between the tables. This specifically happens with ICollection and has driven me "batty" trying to figure it out.

This is a late entry for those (like me) who didn't immediately understand the other 2 answers.

So...

EF is trying to map to the EXPECTED name from the PARENT TABLES KEY-REFERENCE...and since...the FOREIGN KEY name was "changed or shortened" in the databases CHILD TABLE relationship...you would get the message above.

(this fix may differ between versions of EF)

FOR ME THE FIX WAS:
ADDING the "ForeignKey" attribute to the model

public partial class Tour
{
    public Guid Id { get; set; }

    public Guid CategoryId { get; set; }

    [Required]
    [StringLength(200)]
    public string Name { get; set; }

    [StringLength(500)]
    public string Description { get; set; }

    [StringLength(50)]
    public string ShortName { get; set; }

    [StringLength(500)]
    public string TourUrl { get; set; }

    [StringLength(500)]
    public string ThumbnailUrl { get; set; }

    public bool IsActive { get; set; }

    [Required]
    [StringLength(720)]
    public string UpdatedBy { get; set; }

    [ForeignKey("CategoryId")]
    public virtual TourCategory TourCategory { get; set; }
}

I finally figured this out.

I am doing EF6 database first and I was wondering about the "extent unknown column" error - it was generating table name underscore column name for some reason, and trying to find a nonexistent column.

In my case, one of my tables had two foreign key references to the same primary key in another table - something like this:

Animals            Owners
=======            ======
AnimalID (PK)      Pet1ID    <- FK to AnimalID
                   Pet2ID    <- also FK to AnimalID

EF was generating some weird column name like Owners_AnimalID1 and Owners_AnimalID2 and then proceeded to break itself.

The trick here is that these confusing foreign keys need to be registered with EF using Fluent API!

In your main database context, override the OnModelCreating method and change the entity configuration. Preferably, you'll have a separate file which extends the EntityConfiguration class, but you can do it inline.

Any way you do it, you'll need to add something like this:

public class OwnerConfiguration : EntityTypeConfiguration<Owner>
{
    public OwnerConfiguration()
    {
        HasRequired(x => x.Animals)
            .WithMany(x => x.Owners)  // Or, just .WithMany()
            .HasForeignKey(x => x.Pet1ID);
    }
}

And with that, EF will (maybe) start to work as you expect. Boom.

Also, you'll get that same error if you use the above with a nullable column - just use .HasOptional() instead of .HasRequired().

---

Here's the link that put me over the hump:

https://social.msdn.microsoft.com/Forums/en-US/862abdae-b63f-45f5-8a6c-0bdd6eeabfdb/getting-sqlexception-invalid-column-name-userid-from-ef4-codeonly?forum=adonetefx

And then, the Fluent API docs help out, especially the foreign key examples:

http://msdn.microsoft.com/en-us/data/jj591620.aspx

You can also put the configurations on the other end of the key, as described here:

http://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx.

There's some new problems I'm running into now, but that was the huge conceptual gap that was missing. Hope it helps!

Assumptions:

• Table
• OtherTable
• OtherTable_ID

Now choose one of this ways:

---

A)

Remove ICollection<Table>

If you have some error related to OtherTable_ID when you are retrieving Table, go to your OtherTable model and make sure you don't have an ICollection<Table> in there. Without a relationship defined, the framework will auto-assume that you must have a FK to OtherTable and create these extra properties in the generated SQL.

All Credit of this answer is belongs to @LUKE. The above answer is his comment under @drewid answer. I think his comment is so clean then i rewrote it as an answer.

---

B)

• Add OtherTableId to Table

and

• Define OtherTableId in the Table in database