Entity Framework 5 - Invalid column name - Reverse Engineer Code First

Question

Using Entity Framework 5, Visual Studio 2010 with the Entity Framework Power Tools (Beta 2) extension.

Here is my database table structure:

Database Table Structure

I used the Reverse Engineer Code First function of the aforementioned extension, which generated the POCO classes and some 'mapping' files (not sure if that's the formal parlance) and a single DbContext-derived class. Other than the change I describe next, all of these generated classes are as-generated by the power tool.

In the Category.cs file, I added the following code to help flatten the object graph a bit:

private ICollection<Product> m_Products = null;
public ICollection<Product> Products
{
    get
    {
        if (m_Products == null)
        {
            m_Products = new List<Product>();
            foreach (var categoryProduct in CategoryProducts)
            {
                m_Products.Add(categoryProduct.Product);
            }
        }
        return m_Products;
    }
    set { m_Products = value; }
}

I get the following exception, which I know must have something to do with the mappings, but I just can't quite figure this out.

Unhandled Exception: System.Data.EntityCommandExecutionException: An error occurred while 
executing the command definition. See the inner exception for details.
 ---> System.Data.SqlClient.SqlException: 
      Invalid column name 'Category_CategoryId'.

If I need to post more information, such as the specifics of the mappings, just let me know and I'll do it. I wanted to keep this as short as possible, but I realize I've omitted some things that, for those unfamiliar with the code generated by the tool, may leave one wanting for more details.

score 6 · Accepted Answer · answered Jan 03 '13 at 15:55

6

You've added a navigation property to your model and so EF is trying to map that to your database. "Code First" means your code model defines your database schema.

Try adding the [NotMapped] attribute to your helper properties to tell EF to ignore them.

answered Jan 03 '13 at 15:55

Nick Butler

24,045
4
49
70

Where, there you go. Thanks, I appreciate it. This gives me a good starting point from which to research more about why this was causing me problems. Thank you! – Jason Bunting Jan 03 '13 at 16:05

Miroslav Holec · Answer 2 · 2013-01-03T16:14:52.947

In case you've created DB scheme automatically and you are not using strategies like (DropDatabaseAlways/DropDatabaseIfModelChanges) - other words: you are really in Reverse Engineering, it seems that you have to manually add column "CategoryId" on "Category" table.

In case, you don't want to work with the property (I mean in DB), you can use Data Annotation [NotMapped] or Fluent API modelBuilder.Entity<Category>().Ignore(x=> x.CategoryId)

Finally it is possible that problem can be in mapping. I don't know whether you are using data annotations or Fluent API but EF may automatically looks for some db column (logical behavior derived from the model) and can not find it. In this case I recommend you make a revision of the mapping.

Thanks for responding, I appreciate it! – Jason Bunting Jan 03 '13 at 20:26 — Jason Bunting, Jan 03 '13 at 20:26

score 0 · Answer 3 · answered Apr 05 '13 at 01:14

The OP already solved their problem, but I've had a similar error with a different solution. So here it is in case others need it:

I was missing a navigation property on one side of a 0..1 relationship between entities. Once I added an appropriate navigation property to the entity that was missing it, the problem was solved.

A bit more details: I had two entities with a 0..1 relationship using a FK. Entity A (parent) had a FK to Entity B (child). The child entity B had a navigation property to entity A, but A did not have a navigation property to B. After adding this, the problem was solved.

Correct me if I'm wrong, but navigation property is not required on both sides in order to define a relation... navigation property has to be at least on one side. So, I think, there must be solution not requiring to add a navigation property on both sides (or making EF ignore/not map this navigation) — Prokurors, Mar 04 '14 at 09:06

Entity Framework 5 - Invalid column name - Reverse Engineer Code First

3 Answers3

Linked