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?

Westlund

This also happens if you have a navigation property with only a get accessor: public virtual Person Person { get; } – Symphony

Please mark an answer – Pitchfork

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.

Brathwaite

Just to be clear on this answer, because it was most accurate to my situation (but I didnt know it until after I figured my problem out). 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. – Sunsunbaked

Just an additional comment on Luke's answer. When you check for ICollections, ensure you check any partials too. My class was a partial, and I had ended up having the other partial with the same property, but not plural :( – Plier

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; }
}

Pitchfork

This worked for me. +1 for being the only place I found this answer. – Quits

@Jerry I have the forign key defined. But still it searches for the Category_Id. You have mentioned about the fixes for different versions of EF right? i'm using EF 6.0 What is the fix i canadopt? – Jezreel

@ajay-aradhya Actually, it was the person who originally responded, prisoner-zero, who made the comment about different versions of EF. – Quits

@JerryBenson-Montgomery nevermind ! i made it work. It was the 'one to one' mapping which was causing it to search for *_ID. Including the back reference worked fine. – Jezreel

I had a many to many relationship in a table, and using [ForeignKey("TableId")] seemed to fix EF's confusion about where to find the ID (as it was looking in the wrong place at first and invalidly thinking it didn't exist). – Fluster

You can also fix this by adding a metadata partial class so you will won't have to fix this when you regenerate. [MetadataType(typeof(MetaData))] public partial class Tour { public class MetaData { [ForeignKey(nameof(TourCategory))] public virtual TourCategory TourCategory { get; set; } } } – Sielen

very good answer, in my case I used normal join not Include – Impersonal

That was it! Thank you bro! – Multivocal

I had my foreign keys configured by Fluent API. Somehow EF6 refused to work that way. Adding [ForeignKey("...")] solved the problem. – Wilinski

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!

Salmonella

Thanks a lot.. I had the same issue. – Vaillancourt

This worked for me too, added similar lines of code in the Map file: builder.HasOne(item => item.LogicalShipment).WithMany(s => s.Items).HasForeignKey(item => item.LogicalShipmentId).IsRequired(); – Archibold

I had an issue very similar to this. In my case, TableA had a Foreign Key to TableB, and TableB had List<TableA> that I wanted populated procedurally. I had to do the following configuration: this.HasRequired( x => x.TableB ).WithMany( x => x.TableA ).HasForeignKey( x => x.TableBId ).WillCascadeOnDelete( true ); Thanks so much! – Porty

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

Intrinsic

Such a briliant answer! – Parallax

This answer indeed saved by day quickly. and thanks to LUKE, i did read his comment. Though @Brathwaite made it at the last of the answer chain, but it was superb and what was needed for most facing this situation. – Sondrasone

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);

Widget

Answer 6

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.

Cameliacamella

Answer 7

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 });

...

Gosney

Answer 8

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;
    }
}

Attend

Answer 9

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.

Sweepstake

Answer 10

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.

Bottom

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.

Sightless

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

Whereon

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.

Amadis

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 !

Phenology

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; }

Dicker

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.

Bolide

This does not look a definitive answer to the question since you don't explain the cause. It should be put as a comment. – Berwick

Answer 17

My problem had to do with custom triggers on the table

Salamis

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.

Lowtension

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; }

Elect

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; }

Pale

Answer 21

Just stepped on this problem in a legacy code base where I removed the old edmx model and replaced it with a fluent datamodel. Somewhere in the code base there where a namespace with extension classes.

The attribute [NotMapped] solved it

Colchicum

Answer 22

In my case, there was an internal view being used in another view. However, for some weird reason the another view response was cached in Azure SQL Server.

To fix the issue, I required updating the view cache. I found a statement that mentioned below.

If you need to clear the cache for a specific view without affecting other queries, you might consider creating a mechanism to force the recompilation of queries accessing that view. One approach is to modify the view definition slightly (even if it's just adding and then removing a comment), which will cause SQL Server to recompile the view and refresh the cached plan. However, this approach may not be feasible or practical in all situations.

So, I used the schema dbo along with views query in another view. Something like

SELECT * FROM dbo.VUE_Worker_JobsAvailable

Pylle