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?

Try removing the .Include(u => u.Recipes), you don't need it. To include a collection and then a collection one level down use .Include(e => e.Level1Collection.Select(l1 => l1.Level2Collection)) (your second include). — ocuenca
– ocuenca, Commented Feb 10, 2015 at 18:41
@octavioccl removed it, the second level is still not being loaded. — isklenar
– isklenar, Commented Feb 10, 2015 at 18:46
If you're using SQL Server it might be worth using the SQL Profiling tool to see what SQL is actually being generated and sent to the database. It might give a clue as to what is wrong. — Craig W.
– Craig W., Commented Feb 10, 2015 at 18:52
Isn't this always going to return a new empty list? get { return _recipes ?? new List<Recipe>(); } — test
– test, Commented Feb 10, 2015 at 18:55
@xerwin, I would say download LinqPad: linqpad.net and try re-creating your Get in there. I've gotten a lot of value out of that little program. — DrewJordan
– DrewJordan, Commented Feb 10, 2015 at 19:15

Jcl · Accepted Answer · 2015-02-10 19:13:13Z

6

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

edited Feb 10, 2015 at 19:13

answered Feb 10, 2015 at 18:57

Jcl

28.4k5 gold badges66 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

DrewJordan Over a year ago

this is incorrect. When querying for nested collections, you MUST use the SELECT syntax, as far as I know

Jcl Over a year ago

@DrewJordan according to the documentation, not really. Check the second example in Eagerly loading multiple levels. I use this syntax all the time and it works just fine.

isklenar Over a year ago

@jcl get { return _recipes ?? _recipes = new List<Recipe>(); } is invalid syntax and since Recipes is collection, Users.Include(u => u.Recipes.Ingredients), this doesn't work either.

DrewJordan Over a year ago

@ Jcl, actually the documentation says this: var blogs1 = context.Blogs .Include(b => b.Posts.Select(p => p.Comments)) .ToList();

Jcl Over a year ago

@DrewJordan oh, yes, sorry, i kinda confused this for a navigation property and not a collection. Will edit

|

danludwig · Accepted Answer · 2015-02-10 19:16:15Z

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);

Collectives™ on Stack Overflow

Entity Framework 6, eager loading nested relationship returns empty collection

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related