Invalid Column Name *Id

Question

I know the problem: I am missing a mapping to help EF understand the relationship between my two tables but I am not seeing where my mistake is.

I have a Web Api using EF CF 7. I can query fine and get the parent table data but when I try to include the related child data I am getting the invalid column error.

My two tables look like this:

public class Categories { private ICollection<SubCategories> _subcat; public int Id { get; set; } public string Category { get; set; } public virtual ICollection<SubCategories> SubCategories { get { return _subcat ?? (_subcat = new List<SubCategories>()); } set { _subcat = value; } } } public class SubCategories { public int Id { get; set; } public string SubCategory { get; set; } }

The above classes match my db schema EXCEPT the SubCategories table has a column CategoryId and it is a FK to the parent table Id column.

My repository query looks like this:

return _context.Categories.Include(i => i.SubCategories).ToList();

Now the error is throwing me...and I vaguely remember it has to do with EF concatenating the name. In that experience there was a base class that had the Id defined therefore when I defined it in my class it conflicted but that isn't the case here?

Invalid column name 'CategoriesId'.

So I have google and read several articles but either I didn't understand completely or they weren't my problem - probably the former.

You should add a private constructor and add this logic inside SubCategories = new List<SubCategories>()), instead of doing it inside the property block. — Fals
– Fals, Commented Apr 12, 2016 at 20:48
Doesn't your SubCategory class need a Category property? Like; public virtual Category Category { get; set; } to complete the relationship? — RJ Lohan
– RJ Lohan, Commented Apr 12, 2016 at 20:49
@Fals Good point to move the logic outside of the properties block. — GPGVM
– GPGVM, Commented Apr 12, 2016 at 22:09

AaronLS · Accepted Answer · 2016-04-12 21:02:46Z

The above classes match my db schema EXCEPT the SubCategories table has a column CategoryId and it is a FK to the parent table Id column.

If your table is named Categories, but your FK is named CategoryId then you'll have a problem. Inconsistent pluralization.

Either rename the FK to CategoriesId, or better yet follow the more common convention of singular class names(only instances of collections are plural) and change the table and class name to Category.

That's it. Inconsistent Pluralization is a new topic to read up on. I also had some column names in the db not mentioned in the OP contributing. I done been edjumakated. Thank You!
@GPGVM Most of this is covered as "EF conventions", which are how it infers the names of things based on the names of other things, and similar concepts: msdn.microsoft.com/en-us/data/jj679962.aspx

Collectives™ on Stack Overflow

Invalid Column Name *Id

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related