]> Entity Framework Core invalid column name foreign key 🌐:aligrant.com

Entity Framework Core invalid column name foreign key

Alastair Grant | Thu 27 Apr 2017

I have finally gotten round to messing about with ASP.NET Core 1.1 - this is a version of ASP.NET that will run on the .NET Core runtime (read: it'll work on Linux). Initially I had shied away from it due to the lack of Entity Framework - which for me is the killer feature of ASP.

Luckily we now have Entity Framework Core - it is missing some features of EF6, but it also has some others. For basic data-access there is no difference.

I started by putting together some simple models and trying it out, it was generally going OK until one of my relationships refused to work. And it comes down to the problem of keywords and uniqueness.

public class Record {
  public int record_id { get; set; }
  public short provider { get; set; }
  public Provider Provider { get; set; }
public class Provider {
  public short provider_id { get; set; }
  public string name { get; set; }

A simple record class, with a navigation property off to a "Provider" class. Seems fairly straight-forward, but would produce a SQL exception for provider_id being an invalid column.

The underlying SQL table has a column called "provider" which is the foreign key to the Provider table (makes sense, no?). The problem here though is to do with too many things being called the same thing. In EF we also have a navigation property called Provider (pointing to an object with the same name). This is perfectly allowable C# as case is significant: provider and Provider could be chalk and cheese.

This significance starts to fall apart when we get to EF and SQL. Running SQL Profiler found that EF was trying to get "provider_id" from the Record table - without one being defined. It boils down to the ForeignKey attribute not really functioning when there is a similarly named navigation and field. Renaming the navigation to RecordProvider, which whilst makes me shudder a little, still makes sense and keeps the naming of objects unique - even with case being insignificant.

Breaking from the voyeuristic norms of the Internet, any comments can be made in private by contacting me.