EF4 Unknown Column In Field List

Question

So, I am kind of stumped. I have been using a generic repository, and it works perfect. It sits on top of Entity Framework 4.1. I have used the same line of code to get a set of data numerous times and had no issues before. However, this one table in my database seems to be throwing an exception and I cannot for the life of me figure out how to fix it.

This is the table design in the MySQL database

Completed
=========
CompletedId
OldStepId
NewStepId
Name

Step
====
StepId
Name
Description

This is the model.cs definition

public class Completed
{
    [Key]
    public int CompletedId { get; set; }

    public int OldStepId { get; set; }
    public int NewStepId { get; set; }
    public string Name { get; set; }

    public virtual Step OldStep { get; set; }
    public virtual Step NewStep { get; set; }
}

public class Step
{
    [Key]
    public int StepId { get; set; }

    public string Name { get; set; }
    public string Description { get; set; }
}

The context definition

public DbSet<Completed> Completeds { get; set; }
public DbSet<Step> Steps { get; set; }

The controller calling code in question

var completeds = new List<Completed>();
using (var gm = new GenericRepo<Completed>())
{
  completeds = gm.Get().ToList();
}

The get method of the repository (simplified)

public IEnumerable<T> Get()
{
 var context = new exampleContext();
 DbSet<T> dbSet = context.Set<T>();
 IQueryable<T> query = dbSet;
 return query.ToList();
}

This is the error I get from the browser when navigating to the action

Unknown column 'Extent1.OldStep_StepId' in 'field list'

This is the inner exception obtained from the debugger

InnerException: MySql.Data.MySqlClient.MySqlException
   Message=Unknown column 'Extent1.OldStep_StepId' in 'field list'
   Source=MySql.Data
   ErrorCode=-2147467259
   Number=1054
   StackTrace:
        at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
        at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)
        at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId)
        at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
        at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
        at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
        at MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
        at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
        at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

This is the query which seems to have the issue from the debugger right before the error crashes the application

{SELECT
`Extent1`.`CompletedId`, 
`Extent1`.`OldStepId`, 
`Extent1`.`NewStepId`, 
`Extent1`.`Name`, 
`Extent1`.`OldStep_StepId`, 
`Extent1`.`NewStep_StepId`, 
`Extent1`.`Step_StepId`, 
`Extent1`.`Step_StepId1`
FROM `Completed` AS `Extent1`}

Using reflection I was able to determine, or at least assume, that perhaps the reason StepId is not showing up as a field in Step is because Step is for whatever reason defined as Completed. This may not be the case. I am unsure why this error is occurring.

Has anyone encountered something like this with the Entity Framework before? Is this an issue with my code somewhere? Is this an issue of the way the tables are connected? I have similar definitions to this setup which work flawlessly so I do not understand the discrepancy here. The only difference between this table and all the others is that there are two references to the same object (Note: The Completed class holds two virtual Step objects).

Also note: this is not EF Code First.

Solution

I think this issue is because you havent configured your navigation property to use the FK field you have defined.

You should use:

modelBuilder.Entity<Completed>().HasRequired(e => e.OldStep ).WithMany().HasForeignKey(e => e.OldStepId )

or try:

public class Completed
{
    [Key]
    public int CompletedId { get; set; }

    public int OldStepId { get; set; }
    public int NewStepId { get; set; }
    public string Name { get; set; }
        
    [ForeignKey("OldStepId")]
    public virtual Step OldStep { get; set; }

    [ForeignKey("NewStepId")]
    public virtual Step NewStep { get; set; }
}

public class Step
{
    [Key]
    public int StepId { get; set; }

    public string Name { get; set; }
    public string Description { get; set; }
}

refer to http://xhalent.wordpress.com/2011/01/21/configuring-entity-framework-4-codefirst/

Comments

[Travis J]

And why would that be the case here but not in the dozens of other tables setup 90% the same as this example table?

[undefined]

your error says that its trying to find the default named ID col for the navigation property (OldStep_StepId) but in reality your FK here is named OldStepId, my bet is that this is the first place you have a Navigation property named something different to the name of the entity type

[Travis J]

Not a bad assertion. I tried your suggestion, and now get this error instead: Unknown column 'Extent1.Step_StepId' in 'field list'

[undefined]

Have you got a nav property called step in your completed model?

[Travis J]

No, which is very confusing. The example is pretty spot on. It may be because there is no actual Step foreign nav property, maybe I will try including it. Edit: Did not help to add public virtual Step Step { get; set; }

[undefined]

I think its probably best to try and isolate the issue, start off with no nav proprerties and start adding them 1 at a time until you get the error that way you will be able to tell exactly which one is causing the issue.

[Travis J]

the primary issue seems to be that a Nav property must be named the same as the object it is navigating to. I have tried removing only one, but the issue remains: with a different name comes the error.

[undefined]

You could try the attribute decoration way of doing nav properties, ive revised my answer with this