Many-to-Many Relationships in EF7 - Select query for a list including related data

asp.net-core asp.net-core-mvc c# entity-framework entity-framework-core

Question

I am building a blog with the following Many-to-Many relationship on the content -> category tables. I have used the following code to generate the tables in EF7:

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Categorization> Categorization { get; set; }
}

public class Categorization
{
    public int ContentId { get; set; }
    public Content Content { get; set; }

    public int CategoryId { get; set; }
    public Category Category { get; set; }
}

public class Content
{
    [Key]
    public int Id { get; set; }
    public string Title { get; set; }
    public string Excerpt { get; set; }
    public string Body { get; set; }

    ...

    public virtual ICollection<Categorization> Categorization { get; set; }
}

However, when I attempt to load a list of categories, including the content associated with an EF7 query as follows, I get an error.

categories = await _db.Categories
                      .Include(c => c.Categorization)
                      .ThenInclude(c => c.Content)
                      .OrderByDescending(c => c.Categorization.Count)
                      .ToListAsync();

This is the error page that I get when this is called.

A database operation failed while processing the request.

AggregateException: One or more errors occurred. AggregateException: One or more errors occurred. AggregateException: One or more errors occurred. AggregateException: One or more errors occurred. SqlException: Invalid column name 'Id'. There are pending model changes for ApplicationDbContext Scaffold a new migration for these changes and apply them to the database from the command line:

dnx ef migrations add [migration name] 
dnx ef database update

Interesting note: When i remove the line

 .Include(c => c.Categorization).ThenInclude(c => c.Content)

It will sometimes work correctly, as in load the categorization, however this does not ALWAYS happen, sometimes the categorization doesn't load and I only have null references for Categorization in each Category class in the generated enumerable.

Popular Answer

The error message says it all. SqlException: Invalid column name 'Id'

Your database is different than the models on your project. If you have database migration enabled then run the commands

dnx ef migrations add [migration name] 
dnx ef database update

If you don't want to use database migration, you have to update the models on your database to match your models.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why