Entity Framework - Invalid Column Name '*_ID"

Question

I've narrowed this down to some issue between Code First and Database first EF, but I'm not sure how to fix it. I'll try to be as clear as I can, but I honestly am missing some of the understanding here myself. This is Entity Framework 4.4

I inherited a project where Entity Framework was used, but many of the actual files were deleted with no real way to go back. I re-added EF (Database first) and replicated a T4 setup that the project was built around. It generated code versions of all the database models and a DBContext code file.

If my connection string looks like a "normal" .NET connection string I get an error about an invalid column Name "ProcessState_ID" does not exist. ProcessState_ID is not in the code base at all, it is not in the EDMX file or anything. This appears to be some automatic EF conversion in the query.

When I make the connection string match the Entity Framework model it works fine.

Now in trying to match the previous code with Entity Framework I'd like to keep the "normal" .NET connection string.

So I have two questions here: 1. What is a good way to go from a normal connection string to an EF connection string in code? 2. Is there another fix here that I'm not seeing to stop the invalid column name error?

Answer 1

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.

Answer 2

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 using the PARENT TABLES KEY-REFERENCE
• Since the actual FOREIGN KEY name was "changed or shortened" in the databases CHILD TABLE relationship
• You get the message mentioned 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; }
}

Answer 3

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!

Answer 4

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

Answer 5

For me cause of this behavior was because of issue with defined mapping with Fluent API. I had 2 related types, where type A had optional type B object, and type B had many A objects.

public class A 
{
    …
    public int? BId {get; set;}
    public B NavigationToBProperty {get; set;}
}
public class B
{
    …
    public List<A> ListOfAProperty {get; set;}
}

I had defined mapping with fluent api like this:

A.HasOptional(p=> p.NavigationToBProperty).WithMany().HasForeignKey(key => key.BId);

But the problem was, that type B had navigation property List<A>, so as a result I had SQLException Invalid column name A_Id

I attached Visual Studio Debug to EF DatabaseContext.Database.Log to output generated SQL to VS Output->Debug window

db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

And generated SQL had 2 relations from B table -> one with correct id and other with the A_Id

The issue for the problem was, that I did not add this B.List<A> navigation property into mapping.

So this is how in my case correct mapping had to be:

A.HasOptional(p=> p.NavigationToBProperty).WithMany(x => x.ListOfAProperty).HasForeignKey(key => key.BId);

Answer 6

In my case I was incorrectly defining a primary key made up of two foreign keys like this:

HasKey(x => x.FooId);
HasKey(x => x.BarId);

HasRequired(x => x.Foo)
    .WithMany(y => y.Foos);
HasRequired(x => x.Bar);

The error I was getting was, "invalid column name Bar_ID".

Specifying the composite primary key correctly fixed the problem:

HasKey(x => new { x.FooId, x.BarId });

...

Answer 7

In my case the cause for this problem was a missing FOREIGN KEY constraint on a migrated database. So the existing virtual ICollection was not loaded successfully.

Answer 8

For me, it happened because of EF's pluralization issues. For tables that ends with something like "-Status", EF thinks that it's singular is "-Statu". Changing the entity and DB table name to "-StatusTypes" fixed it.

This way, you would not need to rename entity models everytime it gets updated.

Answer 9

I also had this problem and it seems like there are a few different causes. For me it was having an id property mistakenly defined as int instead of long in the parent class that contained a navigation object. The id field in the database was defined as bigint which corresponds to long in C#. This didn't cause a compile time error but did cause the same run time error as the OP got:

// Domain model parent object
public class WidgetConfig 
{
    public WidgetConfig(long id, int stateId, long? widgetId)
    {
        Id = id;
        StateId = stateId;
        WidgetId = widgetId;
    }

    private WidgetConfig()
    {
    }

    public long Id { get; set; }

    public int StateId { get; set; }

    // Ensure this type is correct
    public long? WidgetId { get; set; } 

    public virtual Widget Widget { get; set; }
}

// Domain model object
public class Widget
{
    public Widget(long id, string name, string description)
    {
        Id = id;
        Name = name;
        Description = description;
    }

    private Widget()
    {
    }

    public long Id { get; set; }

    public string Name { get; set; }

    public string Description { get; set; }
}

// EF mapping
public class WidgetConfigMap : EntityTypeConfiguration<WidgetConfig>
{
    public WidgetConfigMap()
    {
        HasKey(x => x.Id);
        ToTable(nameof(WidgetConfig));
        Property(x => x.Id).HasColumnName(nameof(WidgetConfig.Id)).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).IsRequired();
        Property(x => x.StateId).HasColumnName(nameof(WidgetConfig.StateId));
        Property(x => x.WidgetId).HasColumnName(nameof(WidgetConfig.WidgetId));
    }
}   

// Service
public class WidgetsService : ServiceBase, IWidgetsService
{
    private IWidgetsRepository _repository;

    public WidgetsService(IWidgetsRepository repository)
    {
        _repository = repository;
    }

    public List<WidgetConfig> ListWithDetails()
    {
        var list = _repository.ListWithDetails();

        return new WidgetConfigMapping().ConvertModelListToDtoList(list).ToList();
    }
}   

// Repository
public class WidgetsRepository: BaseRepository<WidgetConfig, long>, IWidgetsRepository
{
    public WidgetsRepository(Context context)
        : base(context, id => widget => widget.Id == id)
    {
    }

    public IEnumerable<WidgetConfig> ListWithDetails()
    {
        var widgets = Query
            .Include(x => x.State)
            .Include(x => x.Widget);

        return widgets;
    }
}

Answer 10

For me the problem is that I had the table mapped in my app twice - once via Code First, once via Database First.

Removing either one solves the problem in my case.

Answer 11

In my case, I already have a database (Database firts). Thanks to all comments here, I found my solution:

The tables must have the relationship but the name of the columns need to be different and add ForeignKey attribute.

[ForeignKey("PrestadorId")] public virtual AwmPrestadoresServicios Colaboradores { get; set; }

That is, PRE_ID is PK, but FK in the other table is PRESTADOR_ID, then it works. Thanks to all comments here I found my solution. EF works in mysterious ways.

Answer 12

I solved it by implementing this solution

https://jeremiahflaga.github.io/2020/02/16/entity-framework-6-error-invalid-column-name-_id/

(Internet Archive archived version: https://web.archive.org/web/20210224204440/https://jeremiahflaga.github.io/2020/02/16/entity-framework-6-error-invalid-column-name-_id/)

Resume>

modelBuilder.Entity<Friendship>()
         .ToTable("Friendship")
         .HasKey(x => new { x.Person1Id, x.Person2Id })
         .HasRequired(x => x.PersonOne)
            .WithMany()
            .HasForeignKey(x => x.Person1Id); // WRONG, “Invalid column name ‘Person_Id’.”

modelBuilder.Entity<Friendship>()
         .ToTable("Friendship")
         .HasKey(x => new { x.Person1Id, x.Person2Id })
         .HasRequired(x => x.PersonOne)
            .WithMany(x => x.Friendships) 
            .HasForeignKey(x => x.Person1Id); 

// The solution is to add .WithMany(x => x.Friendships) in your configuration

Answer 13

In my case my seed method data was still calling a table column which had been dropped in a previous migration. Double check your mappings if you are using Automapper.

Answer 14

If you have this issue with a navigation property on the same table, you'll have to change the name of our property.

For example :

Table : PERSON
Id
AncestorId (with a foreign key which references Id named Parent) 

You'll have to change AncestorId for PersonId.

It seems that EF is trying to create a key ParentId because it couldn't find a table named Ancestor...

EDIT : This is a fix for Database first !

Answer 15

If you have foreign key references to the same table more than once, then you can use InverseProperty

Something like this-

[InverseProperty("MyID1")]
public virtual ICollection<MyTable> set1 { get; set; }
[InverseProperty("MyID2")]
public virtual ICollection<MyTable> set2 { get; set; }

Answer 16

For me (using Visual Studio 2017 and the database-first model under Entity Framework 6.1.3), the problem went away after restarting Visual Studio and Rebuilding.

Answer 17

My problem had to do with custom triggers on the table

Answer 18

In case you do not have any One-to-Many relationships to your table, my issue ended being that I had a MS SQL trigger referencing an old/non-existent column.

Answer 19

I forgot to mark the navigation property virtual

public Guid GroupId { get; set; }
public Group Group { get; set; }

->

public Guid GroupId { get; set; }
public virtual Group Group { get; set; }

Answer 20

TLDR: Set virtual properties to be nullable.

I had a weird problem where because property was not set nullable it tried to look for another table that is and thus created '*_ID1'. I just set virtual properties to be nullable and it works.

[ForeignKey("Type")]
public virtual Type? Type{ get; set; }