Entity Framework - Invalid Column Name 'User_ID"

Question

I have a project written with C# on the top of ASP.NET MVC 5 framework. I am using Entity Framework 6.2 ORM to interact with my database.

I have the following 2 entity classes

public class User
{
    [Key]
    public int Id { get; set;}
    public string FirstName { get; set; }

    public int? MainClassRoomId { get; set; }
    // ....

    [ForeignKey("MainClassRoomId")]
    public virtual ClassRoom MainClassRoom { get; set; }

    public virtual ICollection<ClassRoom> AvailableClassRooms { get; set; }
}

public class ClassRoom
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Title { get; set; }
    public int UserId { get; set; }
    // ....

    [ForeignKey("UserId")]
    public virtual User Instructor { get; set; }
}

I find a single user with no problem like this

User user = DataContext.Users.FirstOrDefault(x => x.Id == 10);

However, if I want to access the MainClassRoom navigation property I get the following error

Invalid column name 'User_Id'.

I get the above error after I execute the following

if(user.MainClassRoom != null)
{
   // Some something with user.MainClassRoom
}

What could be causing this error? How can I fix it?

Answer 1

Try adding the foreign key attribute to the ICollection navigation property:

[ForeignKey("UserId")]
public virtual ICollection<ClassRoom> AvailableClassRooms { get; set; } 

Since you've changed the naming convention you must set it on both sides of the relationship.

I usually prefer to do this using EF's fluent API as it's more flexible and you don't end up using magic strings.