Invalid column name. The name was never implemented in the code

Question

I have a simple program which I'm setting up to be used with unit testing. For some reason, Entity Framework is yelling at me with the following error: Invalid column name 'Author_Id'.

The problem is I do not have a column named Author_Id, not in the project, not in the Models and not in the tables on the database. For some reason, it's adding the _Id part to the end of what I search for.

Example: If I set it to search for Author I get Author_Id. AuthorId becomes AuthorId_Id and Author_Id turns into Author_Id_Id.

I have no idea what I'm doing wrong here. I did a search through the entire solution and nothing was found.

It tells me that my error is here:

public ViewResult Index()
{
    var books = db.Books.Include(b=>b.AuthorId);
    ViewBag.AlbumCount = books.Count();
    return View(books.OrderBy(b => b.Author.Id).ToList());
    //^^^^ Return is supposed to be doing the error. Fetching Name or 
}

My Author Model is this:

public class Author
{
    [Key]
    public int Id { get; set; }

    [Display(Name = "Author")]
    public string Name { get; set; }

    [Display(Name = "Date of birth")]
    public DateTime? DateOfBirth { get; set; }

    [Display(Name = "Date of death")]
    public DateTime? DateOfDeath { get; set; }

    public ICollection<Book> Books { get; set; }
}

My Book model is this:

public class Book
{
    [Key]
    public int Id { get; set; }

    [Display(Name = "Select author")]
    public Author AuthorId { get; set; }

    [Required]
    public string Title { get; set; }

    [Display(Name = "Publication date")]
    public DateTime? PublicationDate { get; set; }

    public float? Edition { get; set; }

    public Author Author { get; set; }
}

Answer 1

Sorry guys. I was kind of trying to use code first and database first at the same time. I had to remove the old tables and recreate them with migrations. Then I also had 2 different dbContexts. One for Identity and one for me. Also, relations were all messed up. Anyway if someone has a similar problem check your relations. And use database first or code first. Don't mix them like I did.

Answer 2

You've got two Navigation Properties, instead of one Navigation Property and one Foreign Key Property.

Shouldn't this:

public class Book
{
    [Key]
    public int Id { get; set; }

    [Display(Name = "Select author")]
    public Author AuthorId { get; set; }

    [Required]
    public string Title { get; set; }

    [Display(Name = "Publication date")]
    public DateTime? PublicationDate { get; set; }

    public float? Edition { get; set; }

    public Author Author { get; set; }
}

be

public class Book
{
    [Key]
    public int Id { get; set; }

    [Display(Name = "Select author")]
    public int AuthorId { get; set; }

    [Required]
    public string Title { get; set; }

    [Display(Name = "Publication date")]
    public DateTime? PublicationDate { get; set; }

    public float? Edition { get; set; }

    [ForeignKey("AuthorId")]
    public Author Author { get; set; }
}

?

Answer 3

The Include method is for loading related entities, not single columns (like AuthorId). Include the navigation property instead.

Replace

var books = db.Books.Include(b => b.AuthorId);

by

var books = db.Books.Include(b => b.Author);

Note: authorId is loaded anyway, as it is simply a column in the books table.