Entity Framework Eager Loading Loads Everything

Question

We are using Entity Framework + Repository Pattern in a web based application to fetch database . Because of our complex business, our models are getting complex sometimes and this cause strange behaviour at Entity Framework eager loading system.

Please imagine our real model like this. We have tables, boxes which are on table, pencil cases which can be on table or in the box and pencils that can be on the table or in the box or in the pencil case. We had modelled this in our application like this.

public class Table
{
    public int TableID{ get; set; }
    public virtual ICollection<Box> Boxes{ get; set; }
    public virtual ICollection<PencilCases> PencilCases{ get; set; }
    public virtual ICollection<Pencils> Pencils{ get; set; }
}

public class Box
{
    public int BoxID{ get; set; }
    public int TableID{ get; set; }
    [ForeignKey("TableID")]
    public virtual Table Table{ get; set; }
    public virtual ICollection<PencilCases> PencilCases{ get; set; }
    public virtual ICollection<Pencils> Pencils{ get; set; }
}

public class PencilCases
{
    public int PencilCaseID{ get; set; }
    public int? BoxID{ get; set; }
    public int TableID{ get; set; }
    [ForeignKey("TableID")]
    public virtual Table Table{ get; set; }
    [ForeignKey("BoxID")]
    public virtual Box Box{ get; set; }
    public virtual ICollection<Pencils> Pencils{ get; set; }
}

public class Pencils
{
    public int PencilID{ get; set; }
    public int? PencilCaseID{ get; set; }
    public int? BoxID{ get; set; }
    public int TableID{ get; set; }
    [ForeignKey("TableID")]
    public virtual Table Table{ get; set; }
    [ForeignKey("BoxID")]
    public virtual Box Box{ get; set; }
    [ForeignKey("PencilCaseID")]
    public virtual PencilCase PancelCase{ get; set; }
}

Our repository pattern implementation similar with this tutorial, http://www.asp.net/mvc/tutorials/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application So we call get method like this.

var tables = unitOfWork.TableRepository.Get(includeProperties: "Boxes, PencilCases, Boxes.Pencils");

So the problem is the result is very different from my expectations;i expect only Boxes,PencilCases and Boxes.Pencils collections will be fetched, but all the Pencil entities fetched from database including Pencils, PencilCases.Pencils and Boxes.PencilCases.Pencils. This recursive fetch causes OutOfMemoryException because amount of data.

I couldn't understand why Entity Framework fetches all Pencils except Boxes.Pencils. I also tried to specify including list with Expression instead of Query Path but result didn't change.

Answer 1

first off - I'm fairly new to EF myself so please excuse if the following is not 100% accurate. However, I've dealt with this exact same problem just a couple of days ago, so hopefully this will help.

The problem is that when EF loads a specific entity, it will add that entity to every part of the Data Model that it appears in - not just the parts that were explicitly loaded.

This means that every Pencil in Boxes.Pencils that is also in the ICollection of Table.Pencils will be automatically resolved even though you did not specifically ask for it.

By itself that fact does not present a problem, and can even be helpful in a user-driven MVC application.

Where it all goes wrong is when you try to do anything that recurses trough the Data Entity, such as trying to map the self-recursing Data Entity to a Business Model or trying to turn the self-recursing data entity into JSON/XML.

Now, there are several solutions to this problem:

Implement a mapper / encoder that hashes / remembers each object and only adds it once:

The problem with this one is that it can lead to some hard-to-predict results, especially when you want / need the object in multiple places. Additionally, hashing and comparing every object could be costly.

Implement a mapper / encoder that can be configured to ignore some properties

Relatively simple - if you can specify that you don't want to map or encode Pencil at all, you won't have any issues. Downsides are of course that you could still encounter a stackoverflow if you are not vigilant about specifying the ignored properties.

Implement a mapper / encoder with specifyable recursion depth

This is a very simple and pretty decent solution - simply set a hard limit on recursion depth, either on a global or on a per-type basis, and you won't have any more stackoverflows. Downside is that you would still end up with elements that you don't want, and thus get a unnecessarily bloated return object.

Implement custom business entities

This is probably the best solution - simply create a new business entity with the offending navigational properties removed. The primary downside is that it would require you to create different business entities for different purposes.

Here is a example:

// Removed Pencils
public class BusinessTable
{
    public int TableID{ get; set; }
    public IEnumerable<Box> Boxes{ get; set; }
    public IEnumerable<PencilCases> PencilCases{ get; set; }
}

// Removed Table & PencilCases
public class BusinessBox
{
    public int BoxID{ get; set; }
    public int TableID{ get; set; }
    public IEnumerable<Pencils> Pencils{ get; set; }
}

// Removed Table & Box & Pencils
public class BusinessPencilCases
{
    public int PencilCaseID{ get; set; }
    public int? BoxID{ get; set; }
    public int TableID{ get; set; }
}

// Removed Table, Box, PencilCase
public class BusinessPencils
{
    public int PencilID{ get; set; }
    public int? PencilCaseID{ get; set; }
    public int? BoxID{ get; set; }
    public int TableID{ get; set; }
}

Now when you map your Data Entity to this set of Business Entities, you won't get any more errors.

For the mapping aspect of this, theres 2 solutions: Manually doing things / using a mapping factory Example of Model Factory, ValueInjecter and AutoMapper - the latter two being available NuGet packages.

For AutoMapper: I don't use AutoMapper, but you'd have to create a config file that looks something like this:

Mapper.CreateMap<Table, BusinessTable>();
Mapper.CreateMap<Box, BusinessBox>();
Mapper.CreateMap<PencilCases, BusinessPencilCases>();
Mapper.CreateMap<Pencils, BusinessPencils>();

And then in your query:

var tables = unitOfWork.TableRepository.Get(includeProperties: "Boxes, PencilCases, Boxes.Pencils");
var result = Mapper.Map<IEnumerable<Table>, IEnumerable<BusinessTable>>(tables);

Or

var tables = unitOfWork.TableRepository.Get(includeProperties: "Boxes, PencilCases, Boxes.Pencils").Project().To<IEnumerable<BusinessTable>;

For more info pertaining AutoMapper ( like how to set up a config file ): https://github.com/AutoMapper/AutoMapper/wiki/Getting-started

For ValueInjecter:

var tables = unitOfWork.TableRepository.Get(includeProperties: "Boxes, PencilCases, Boxes.Pencils");
var result = new List<BusinessTable>().InjectFrom(tables);

Or:

var tables = unitOfWork.TableRepository.Get(includeProperties: "Boxes, PencilCases, Boxes.Pencils");
var result = tables.Select(x => new BusinessTable.InjectFrom(x).Cast<BusinessTable>());

It might also be worthwhile to look at additional ValueInjecter Injections, like SmartConventionInjection, Deep Cloning, Useful Injections and a ORM with ValueInjecter guide.

I also made a few injections for my own project that may be of use to you, which you can find On my Github

With MaxDepthCloneInjector for example, you can supply a dictionary of (property names, max recursion depth) and it will only map values included in the dictionary, and only until the specified level.

Two more pieces of advice:

• If you want a bit more freedom with your queries, you should consider using the Query Expression Syntax for some of your more complex needs. Theres also some good information in this answer on SO: How to limit number of related data with Include
• If you are planning to run queries including navigational properties like the one in your example: STICK WITH EAGER LOADING. A query like that in Lazy Loading would lead to the N + 1 problem. As a rule of thumb:
• • Use Lazy Loading if you don't need the entire result set right away, for example if you are developing a application where data requirements naturally expand based on the User's interaction with the application.
• • Use Eager Loading if you need the entire result-set right away, for example in a Web Api, or a application that needs to work with the complete entity.

Best of luck, Felix