Entity Framework 6, eager loading nested relationship returns empty collection

Question

I have a domain model that looks like this (stripped of some unimportant properties)

public class User
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public virtual int UserId { get; set; }

    private ICollection<Recipe> _recipes;
    public virtual ICollection<Recipe> Recipes
    {
        get { return _recipes ?? new List<Recipe>(); } 
        set { _recipes = value; }
    } 
}

public class Recipe
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int RecipeId { get; set; }

    private ICollection<Ingredient> _ingredients;
    public virtual ICollection<Ingredient> Ingredients
    {
        get { return _ingredients ?? new List<Ingredient>(); } 
        set { _ingredients = value; }
    }
    public User User { get; set; }
}

public class Ingredient
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int IngredientId { get; set; }

    public Recipe Recipe { get; set; }
}

So in short, User has one-to-many relationship to Recipe, which in turn has one-to-many relationship to Ingredient. I'm using code first approach, with this mapping in protected override void OnModelCreating(DbModelBuilder modelBuilder) :

modelBuilder.Entity<User>()
    .HasMany(u => u.Recipes)
    .WithRequired(u => u.User)
    .WillCascadeOnDelete(true);

modelBuilder.Entity<Recipe>()
    .HasMany(u => u.Ingredients)
    .WithRequired(u => u.Recipe)
    .WillCascadeOnDelete(true);

and I'm using repository, which has this code to store data into MySQL database:

public void Add(User entity)
{
    using (var context = new UserContext())
    {
        context.Users.Add(entity);
        context.SaveChanges();
    }
}

and fetching:

public User GetById(int id)
{
    using (var context = new UserContext())
    {
        var user = context.Users
            .Include(u => u.Recipes)
            .Include(u => u.Recipes.Select(x => x.Ingredients))
            .FirstOrDefault(u => u.UserId == id);

        return user;
    }
}

I have integration tests, which create a User object, List<Recipe> with two recipes where each has List<Ingredient> with 2 items. They are different for each recipe, so 4 ingredients total. When I add it to the repository, I can see it in the database with correct PKs and FKs.

However when retrieving from database, returned User object has recipes, but neither of those recipes has the ingredients in them. Due the the way I set up my getters, when I try to access, them, I receive an empty collection.

e.g. doing this:

/* create recipes collection and seed it */

User user = new User {Recipes = recipes /* plus some omitted properites*/};
_repository.Add(user);

var returnedUser = _repository.GetById(user.UserId);

var userIngredients = user.Recipes.First(u => u.RecipeName == "Test").Ingredients.ToList();
var returnedIngredients = returnedUser.Recipes.First(u => u.RecipeName == "Test").Ingredients.ToList();

returnedIngredients is empty, while userIngredients has two elements. This then fails assertion and results in failed integration test.

Can someone tell me how to properly do eager loading on nested one-to-many?

Answer 1

Couple of ideas:

public class Ingredient
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int IngredientId { get; set; }

    public virtual Recipe Recipe { get; set; } // <-- make this virtual
}

...and...

public class Recipe
{
    public Recipe()
    {
        // set up a default collection during construction
        Ingredients = new List<Ingredient>();
        // could also be Ingredients = new Collection<Ingredient>();
    }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int RecipeId { get; set; }

    // private ICollection<Ingredient> _ingredients; <-- don't back
    public virtual ICollection<Ingredient> Ingredients
    {
        get; // <-- should never return null because of constructor
        protected set; // and because externals cannot set to null
    }

    public virtual User User { get; set; } // <-- make this virtual too
}

It may also be worth adding some foreign key properties to the dependent entities (for example RecipeId in Ingredient) and updating the modelBuilder definition. Something like this after the fk property is added:

modelBuilder.Entity<Recipe>()
    .HasMany(u => u.Ingredients)
    .WithRequired(u => u.Recipe)
    .HasForeignKey(u => u.RecipeId) // <-- change is here
    .WillCascadeOnDelete(true);

Answer 2

This:

get { return _recipes ?? new List<Recipe>(); } 

Should read:

get { return _recipes ?? (_recipes = new List<Recipe>()); } 

Same for ingredients.

Otherwise you are returning a new empty list every time (since it's not stored anywhere). This will only work if the whole list is set at some point (read from the database and overwritten), but not if you are creating the entity by code, since they will be added to a list which is not stored in the backing field.

You see that Recipes is full because you are setting it here: User user = new User {Recipes = recipes /* plus some omitted properites*/};, so it gets stored in the backing field.

But ingredients are not, when you add them:

var recipe = new Recipe();
recipe.Ingredients.Add(myIngredient); // <-- this will be stored in a 
                                      //     new List<Ingredient>, 
                                      //     but not on the 
                                      //     _ingredients backing field
                                      //     since the get accesor doesn't 
                                      //     set it
recipe.Ingredients.Add(myIngredient); // <-- this will be stored in a 
                                      //     new List<Ingredient>, not in 
                                      //     the expected created one

As for the eager loading, you don't need this:

 var user = context.Users
        .Include(u => u.Recipes)
        .Include(u => u.Recipes.Select(x => x.Ingredients))
        .FirstOrDefault(u => u.UserId == id);

It's ok with just:

 var user = context.Users
        .Include(u => u.Recipes.Select(x => x.Ingredients))
        .FirstOrDefault(u => u.UserId == id);

It'll load both levels