Include not working with join entities

2019-07-26 07:52发布

问题:

As example I have following entities (many-to-many, I also removed unnessecary props):

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

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

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

Join entity:

public class BuffetRecipe
{
    public int BuffetId {get; set;}
    public virtual Buffet Buffet {get; set;}
    public int RecipeId {get; set;}
    public virtual Recipe Recipe {get; set;}
}

I want to get all recipes that belong to a specific buffet and want include the recipe category.

public IList<Recipe> GetRecipes(int buffetId)
{
    return _dbContext.BuffetRecipes
    .Where(item => item.BuffetId == buffetId)
    .Include(item => item.Recipe)
    .ThenInclude(item => item.Category)
    .Select(item => item.Recipe)
    .ToList();
}

The list I get always returns Recipes with prop Category = null. I didn't find a solution to make the Include() work together with the Select()...

What am I doing wrong??

UPDATE:

I can make it work this way... but my feeling says this is not a good way because i have 2 ToList() calls... but now I have category included in my results:

public IList<Recipe> GetRecipes(int buffetId)
{
    return _dbContext.BuffetRecipes
    .Where(item => item.BuffetId == buffetId)
    .Include(item => item.Recipe)
    .ThenInclude(item => item.Category)
    .ToList()
    .Select(item => item.Recipe)
    .ToList();
}

回答1:

Include is only effective if it can be applied to the end result of the query.

You could change it into ...

return _dbContext.BuffetRecipes
    .Where(item => item.BuffetId == buffetId)
    .Select(item => item.Recipe)
    .Include(rcp => rcp.Category)
    .ToList()

... but the drawback of this is that you duplicate your Recipes (as many as they have BuffetRecipes). It's better to start the query with Recipe:

return _dbContext.Recipes
    .Where(rcp => rcp.BuffetRecipes.Any(br => br.BuffetId == buffetId))
    .Include(rcp => rcp.Category)
    .ToList();

You see that I took the liberty to add a navigation property Recipe.BuffetRecipes. This shouldn't be any problem with your model (on the contrary, I'd say).