Entity Framework - Invalid column name '* _ID "

Question

I narrowed this down to some problem between the first code and the first EF database, but I'm not sure how to fix it. I will try to be as clear as possible, but to be honest, I myself am missing out on some of the understanding. This is Entity Framework 4.4

I inherited a project that used the Entity Framework, but many of the actual files were deleted without a real way to go back. I again added EF (database first) and replicated the T4 installation in which the project was built. He generated code versions for all database models and the DBContext code file.

If my connection string looks like a “normal” .NET connection string, I get an error with an invalid column. The name "ProcessState_ID" does not exist. ProcessState_ID is generally missing from the code base; it is not in the EDMX file or anything else. This is apparently some kind of automatic EF conversion in the request.

When the connection string matches the Entity Framework model, it works fine.

Now, trying to map the previous code to the Entity Framework, I would like to keep the “normal” .NET connection string.

So, I have two questions: 1. What is a good way to go from a regular connection string to an EF connection string in code? 2. Is there another fix here that I don't see to stop the wrong column name error?

Answer 1

Check if you have ICollections.

What I understood is when you have an ICollection that refers to a table and there is no column that it can define, it creates one for you to try to establish a connection between the tables. This especially happens with ICollection and made me “butti” try to figure it out.

Answer 2

This is a late entry for those (like me) who did not immediately understand the other 2 answers.

So...

EF is trying to match the EXPECT name from the PARENT REFERENCE KEY ... and since ... the FOREIGN KEY name has been "changed or abbreviated" in the CHILD TABLE databases ... you will get the message above.

(this fix may vary between versions of EF)

FOR WE CORRECTION 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

Holy Cow - after many hours of trying, I finally figured it out.

First I make an EF6 database, and I was interested in learning about the error "unknown column length" - for some reason, it called the column name of the column name of the column name of the underscore table and tried 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 generated some weird column name, like Owners_AnimalID1 and Owners_AnimalID2 , and then kept breaking.

The trick is that these confusing foreign keys must be registered with EF using the Fluent API!

In the context of the main database, override the OnModelCreating method and modify the configuration of the object. Preferably, you will have a separate file that extends the EntityConfiguration class, but you can do it inline.

In any case, you should 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 (maybe) will start to work as you expect. Boom.

In addition, you will get the same error if you use the .HasOptional() columns above - just use .HasOptional() instead of .HasRequired() .

---

Here is the link that placed 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 APIs help, especially the foreign key examples:

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

You can also place 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 are some new issues that I am facing right now, but it was a huge conceptual gap that was missing. Hope this helps!

Answer 4

Assumptions:

• Table
• OtherTable
• OtherTable_ID

Now choose one of the following methods:

---

A)

Delete ICollection<Table>

If you have some kind of error related to OtherTable_ID while getting Table , go to your OtherTable model and make sure you don't have ICollection<Table> . If the relationship is not defined, the framework automatically assumes that you must have FK for OtherTable, and creates these additional properties in the generated SQL.

All credits for this answer are owned by @LUKE. The answer above is his comment under @drewid's answer. I think his comment is so clean that I rewrote it as an answer.

---

B)

• Add OtherTableId to Table

as well as

• Define OtherTableId in Table in the database

Answer 5

In my case, I incorrectly defined the primary key, which consists of two foreign keys, such as:

 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 a composite primary key corrects the problem correctly:

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

Answer 6

I also had this problem, and there seem to be several different reasons. For me, it had an id property erroneously defined as int instead of long in the parent class containing the navigation object. The id field in the database was defined as bigint, which corresponds to the length in C #. This did not cause a compile-time error, but it caused the same runtime error as the OP:

 // 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 7

The problem for me is that I mapped the table twice in my application - once through Code First, once through Database First.

Removing one of them solves the problem in my case.

Answer 8

For me, the reason for this behavior was due to a specific mapping issue with the Fluent API. I had 2 related types, where type A had an optional object of type B, and type B had many objects of A.

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

I defined a runaway API mapping as follows:

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

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

I connected Visual Studio Debug to EF DatabaseContext.Database.Log to output the generated SQL in VS Output-> Debug Window

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

And the generated SQL had 2 relationships from table B -> one with the correct identifier, and the other with A_Id

The problem was that I did not add this B.List<A> navigation B.List<A> to the display.

Here's how the correct display should have been in my case:

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

Answer 9

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

Something like that -

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

Answer 10

For me (using Visual Studio 2017 and the first database model in Entity Framework 6.1.3) the problem disappeared after restarting Visual Studio and recovering.

Answer 11

In my case, my initial method data still called the table column that was deleted in the previous migration. Double check your mappings if you are using Automapper.

Answer 12

In my case, the cause of this problem was the lack of a FOREIGN KEY restriction on the migrated database. Therefore, the existing virtual ICollection was not loaded successfully.