Entity Framework Code First: Invalid column name

Last week I was creating a sample project to show some of the Entity Framework Code First features. I only had an old Northwind database available so I mapped my model against this database.

But after writing and running my first test I was welcomed by a nice error message:

Test Name:        CanLoadSuppliersByProductCategoryName

Test FullName: Test.Data.Tests.SupplierRepositoryTests.CanLoadSuppliersByProductCategoryName

Test Source:      c:\Projects\Sample\Tests\SupplierRepositoryTests.cs : line 22

Test Outcome: Failed

Test Duration:   0:00:00,784

Result Message:             

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'.

Invalid column name 'Supplier_SupplierID'.

Result StackTrace:         

at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)

at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)

at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()

at System.Data.Entity.Internal.Linq.InternalQuery`1.GetEnumerator()

at System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator()

at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)

at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

at Test.Data.SupplierRepository.LoadSuppliersBy(String productCategoryName) in c:\Projects\Sample\Repositories\SupplierRepository.cs:line 19

at Test.Data.Tests.SupplierRepositoryTests.CanLoadSuppliersByProductCategoryNameLinq() in c:\Projects\Sample\Tests\SupplierRepositoryTests.cs:line 26

--SqlException

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

Let’s have a look at my code first model:

	public class Category
	{
	public virtual int CategoryID { get; set; }
	public virtual string CategoryName { get; set; }
	public virtual string Description { get; set; }
	public virtual byte[] Picture { get; set; }
	public virtual IList<Product> Products { get; set; }
	}

view raw Category.cs hosted with ❤ by GitHub

	public class Product
	{
	public virtual int ProductID { get; set; }
	public virtual bool Discontinued { get; set; }
	public virtual string ProductName { get; set; }
	public virtual string QuantityPerUnit { get; set; }
	public virtual Int16? ReorderLevel { get; set; }
	public virtual Decimal? UnitPrice { get; set; }
	public virtual Int16? UnitsInStock { get; set; }
	public virtual Int16? UnitsOnOrder { get; set; }
	public virtual Category Category { get; set; }
	public virtual ICollection<OrderDetail> OrderDetails { get; set; }
	public virtual Supplier Supplier { get; set; }
	}

view raw Product.cs hosted with ❤ by GitHub

	public class Supplier
	{
	public virtual int SupplierID { get; set; }
	public virtual string Address { get; set; }
	public virtual string City { get; set; }
	public virtual string CompanyName { get; set; }
	public virtual string ContactName { get; set; }
	public virtual string ContactTitle { get; set; }
	public virtual string Country { get; set; }
	public virtual string Fax { get; set; }
	public virtual string HomePage { get; set; }
	public virtual string Phone { get; set; }
	public virtual string PostalCode { get; set; }
	public virtual string Region { get; set; }
	public virtual ICollection<Product> Products { get; set; }
	}

view raw Supplier.cs hosted with ❤ by GitHub

At first sight, I couldn’t find anything wrong with this model. But after some investigation I discovered that Code First is following some very specific conventions. In my current model I only specified the navigation property but not the related id property. On the Product class I had a ‘Category’ property but not a ‘CategoryId’ one, same thing for the ‘Supplier’ property. So I fixed it by adding these 2 extra properties on the Product class:

	public class Product
	{
	public virtual int ProductID { get; set; }
	public virtual bool Discontinued { get; set; }
	public virtual string ProductName { get; set; }
	public virtual string QuantityPerUnit { get; set; }
	public virtual Int16? ReorderLevel { get; set; }
	public virtual Decimal? UnitPrice { get; set; }
	public virtual Int16? UnitsInStock { get; set; }
	public virtual Int16? UnitsOnOrder { get; set; }
	public virtual Category Category { get; set; }
	public virtual int CategoryID { get; set; }
	public virtual IList OrderDetails { get; set; }
	public virtual int SupplierID { get; set; }
	public virtual Supplier Supplier { get; set; }
	}

view raw Product.cs hosted with ❤ by GitHub