Article
01/31/2011

The information in this post is out of date.

Visit msdn.com/data/ef for the latest information on current and past releases of EF.

For Loading Related Entities see https://msdn.com/data/jj574232

Introduction

Version 4.1 of the Entity Framework contains both the Code First approach and the new DbContext API. This API provides a more productive surface for working with the Entity Framework and can be used with the Code First, Database First, and Model First approaches. This is the sixth post of a twelve part series containing collections of patterns and code fragments showing how features of the new API can be used.

The posts in this series do not contain complete walkthroughs. If you haven’t used EF 4.1 before then you should read Part 1 of this series and also Code First Walkthrough or Model and Database First with DbContext before tackling this post.

Eager loading is the process whereby a query for one type of entity also loads related entities as part of the query. Eager loading is achieved by use of the Include method. For example, the queries below will load princesses and all the unicorns related to each princess.

 using (var context = new UnicornsContext())
{
    // Load all princesses and related unicorns
    var princesses1 = context.Princesses
                          .Include(p => p.Unicorns)
                          .ToList();

    // Load one princess and her related unicorns
    var princess1 = context.Princesses
                        .Where(p => p.Name == "Cinderella")
                        .Include(p => p.Unicorns)
                        .FirstOrDefault();

    // Load all princesses and related unicorns using a string
    // to specify the relationship
    var princesses2 = context.Princesses
                          .Include("Unicorns")
                          .ToList();

    // Load one princess and her related unicorns using a string
    // to specify the relationship
    var princess2 = context.Princesses
                        .Where(p => p.Name == "Cinderella")
                        .Include("Unicorns")
                        .FirstOrDefault();
}

Note that Include is an extension method in the System.Data.Entity namespace so make sure you are using that namespace.

It is also possible to eagerly load multiple levels of related entities. The queries below show examples of how to do this for both collection and reference navigation properties.

 using (var context = new UnicornsContext())
{
    // Load all castles, all related ladies-in-waiting, and all related
    // princesses
    var castles1 = context.Castles
                       .Include(c => c.LadiesInWaiting.Select(b => b.Princess))
                       .ToList();

    // Load all unicorns, all related princesses, and all related ladies
    var unicorns1 = context.Unicorns
                        .Include(u => u.Princess.LadiesInWaiting)
                        .ToList();

    // Load all castles, all related ladies, and all related princesses
    // using a string to specify the relationships
    var castles2 = context.Castles
                       .Include("LadiesInWaiting.Princess")
                       .ToList();

    // Load all unicorns, all related princesses, and all related ladies
    // using a string to specify the relationships
    var unicorns2 = context.Unicorns
                        .Include("Princess.LadiesInWaiting")
                        .ToList();
}

Note that it is not currently possible to filter which related entities are loaded. Include will always being in all related entities.

Lazy loading is the process whereby an entity or collection of entities is automatically loaded from the database the first time that a property referring to the entity/entities is accessed. When using POCO entity types, lazy loading is achieved by creating instances of derived proxy types and then overriding virtual properties to add the loading hook. (See Part 8 for more information on working with proxies.) For example, when using the Princess entity class defined below, the related unicorns will be loaded the first time the Unicorns navigation property is accessed:

 public class Princess 
{ 
    public int Id { get; set; } 
    public string Name { get; set; } 
    public virtual ICollection<Unicorn> Unicorns { get; set; } 
}

Lazy loading of the Unicorns collection can be turned off by making the Unicorns property non-virtual:

 public class Princess 
{ 
    public int Id { get; set; } 
    public string Name { get; set; } 
    public ICollection<Unicorn> Unicorns { get; set; } 
}

Loading of the Unicorns collection can still be achieved using eager loading (see Eagerly loading related entities above) or the Load method (see Explicitly loading related entities below).

Turn off lazy loading for all entities

Lazy loading can be turned off for all entities in the context by setting a flag on the Configuration property. For example:

 public class UnicornsContext : DbContext
{
    public UnicornsContext()
    {
        this.Configuration.LazyLoadingEnabled = false;
    }
}

Loading of related entities can still be achieved using eager loading (see Eagerly loading related entities above) or the Load method (see Explicitly loading related entities below).

Even with lazy loading disabled it is still possible to lazily load related entities, but it must be done with an explicit call. To do so you use the Load method on the related entity’s entry. For example:

 using (var context = new UnicornsContext())
{
    var unicorn = context.Unicorns.Find(1);
    var princess = context.Princesses.Find(2);

    // Load the princess related to a given unicorn 
    context.Entry(unicorn).Reference(u => u.Princess).Load();

    // Load the princess related to a given unicorn using a string 
    context.Entry(unicorn).Reference("Princess").Load();

    // Load the unicorns related to a given princess 
    context.Entry(princess).Collection(p => p.Unicorns).Load();

    // Load the unicorns related to a given princess using a string to 
    // specify the relationship 
    context.Entry(princess).Collection("Unicorns").Load();
}

Note that the Reference method should be used when an entity has a navigation property to another single entity. On the other hand, the Collection method should be used when an entity has a navigation property to a collection of other entities.

The Query method provides access to the underlying query that the Entity Framework will use when loading related entities. You can then use LINQ to apply filters to the query before executing it with a call to a LINQ extension method such as ToList, Load, etc. The Query method can be used with both reference and collection navigation properties but is most useful for collections where it can be used to load only part of the collection. For example:

 using (var context = new UnicornsContext())
{
    var princess = context.Princesses.Find(1);

    // Load the unicorns starting with B related to a given princess 
    context.Entry(princess)
        .Collection(p => p.Unicorns)
        .Query()
        .Where(u => u.Name.StartsWith("B"))
        .Load();

    // Load the unicorns starting with B related to a given princess 
    // using a string to specify the relationship 
    context.Entry(princess)
        .Collection("Unicorns")
        .Query().Cast<Unicorn>()
        .Where(u => u.Name.StartsWith("B"))
        .Load();
}

When using the Query method it is usually best to turn off lazy loading for the navigation property. This is because otherwise the entire collection may get loaded automatically by the lazy loading mechanism either before or after the filtered query has been executed.

Note that while the relationship can be specified as a string instead of a lambda expression, the returned IQueryable is not generic when a string is used and so the Cast method is usually needed before anything useful can be done with it.

Sometimes it is useful to know how many entities are related to another entity in the database without actually incurring the cost of loading all those entities. The Query method with the LINQ Count method can be used to do this. For example:

 using (var context = new UnicornsContext())
{
    var princess = context.Princesses.Find(1);

    // Count how many unicorns the princess owns 
    var unicornHaul = context.Entry(princess)
                          .Collection(p => p.Unicorns)
                          .Query()
                          .Count();
}

Summary

In this part of the series we looked at how to load entities eagerly as part of a query, and lazily either automatically or with an explicit call to the Load method. We also looked at how to use the Query method to make adjustments to the loading query.

As always we would love to hear any feedback you have by commenting on this blog post.

For support please use the Entity Framework Forum.

Arthur Vickers

Developer

ADO.NET Entity Framework

Comments

Anonymous
January 31, 2011
Very cool stuff! I love the fact, that all methods like Include now have a generic overload. That makes refactoring much easier. The eagerly loading process of multiple levels of related entites doesn't feel that natural although. Would be nice to see a dump of the generated SQL queries. Keep up the good work!
Anonymous
February 02, 2011
This feature is what I need: "Using Query to count related entities without loading them" But I can't do this by a simple call to princess.Unicorns.Count() ? I don't want to call context and another EF methods at my UI Anyway, to this works well I need to disable Lazy Loading too( like you said that is necessary to filters)?
Anonymous
February 04, 2011
Where is the expression version of Include? Can't find it!
Anonymous
February 04, 2011
Found it in System.Data.Entity namespace.
Anonymous
February 05, 2011
I struggle to find a way how to eager load ENTIRE database with EF. The database itself is small (1MB with SQL Server CE) but is in shared folder so also using Include() still makes querying all entries very slow!
Anonymous
February 07, 2011
@Fujiy: You could do this by building your own collection type and injecting it with a context reference when the entities are materialized. EF doesn't do this automatically--I would encourage you to vote for the feature on data.uservoice.com/.../72025-ado-net-entity-framework-ef-feature-suggestions if you haven't already. You should not need to disable lazy loading to do extra lazy count. @Mikhail: EFisn't really designed to do load the entire database into memory and I don't know a good way to do this or even if it would really work effectively if you did.
Anonymous
February 07, 2011
@Arthur, I have one data.uservoice.com/.../1050591-extra-lazy-loading But this is still confuse: "When using the Query method it is usually best to turn off lazy loading for the navigation property. This is because otherwise the entire collection may get loaded automatically by the lazy loading mechanism either before or after the filtered query has been executed." May get loaded??? Why "may"?
Anonymous
February 17, 2011
@Fujiy Lazy loading kicks in when the navigation propery is accessed for the first time unless all the related entities have already been loaded, as indicated by the IsLoaded flag. When you use Query to filter what is loaded the IsLoaded flag is not set. This means if you then access the navigation property to get the filtered collection lazy loading will kick in and you will not get the filtered collection but instead the entire collection that has just been lazy-loaded. We intend to make it possible to set the IsLoaded flag, but this requires changes to the core EF assemblies and so won't be available in the first release. Therefore, for now, it's best to switch off lazy loading or never try to access the filtered collection using the navigation property. When using Query for Count things are a little different because you are not getting filtered results back, just a scalar value. This means that there is no filtered collection to access and therefore no chance you can try to access the filtered collection but instead get the full collection. Of course, if you have lazy loading on then accessing the collection will load it, but this is somewhat orthoganal to count.
Anonymous
February 20, 2011
Arthur, thanks for answers! You have any plans to release a new Core for EF before .NET 5? I have some problems when I need a List+Count, because I need to do a Projection and EF dont does RelationshipFixUp to Many-To-Many and Include method dont work with Projection: stackoverflow.com/.../how-to-use-include-and-anonymous-type-in-same-query-in-entity-framework
Anonymous
March 08, 2011
Great stuff! Are there any plans to give developers more control of inner vs outer joins when loading related entites?
Anonymous
April 18, 2011
Great stuff! is it possible to remove an entity from the collection without load (eager or lazy) all the collection data? princes.Unicorns.remove(unicorn); Thanks.
Anonymous
May 17, 2011
Arthur, using eager loading as default just doesn't work. i have the following code: public class EMarketContext : DbContext { public EMarketContext() { this.Configuration.LazyLoadingEnabled = false; } } But i still have to use the Include extension method at each query. any ideas?
Anonymous
June 19, 2011
There is a problem of Eagerly Loading-- this is the situation, if the Unicorns is null, when access p.Unicorns at application, an error happen saying:"The ObjectContext instance has been disposed and can no longer be used for operations that require a connection", I think this is because of the Unicorns is null, and the Princess try to retrieve the Unicorns again, but at that time,the Context has been disposed. How to resolve this? using (var context = new UnicornsContext()) { // Load all princesses and related unicorns var princesses1 = context.Princesses .Include(p => p.Unicorns)// suppose the Unicorns is null .ToList(); }
Anonymous
July 23, 2011
How do I load an object with all associated navigational objects? I understand that I can chain the .Include() to load them, but this way of coding is not scalable. There has to be a better way to load a particular object fully.
Anonymous
August 19, 2011
I might add that loading a single entity is much faster. Its only when a related entity is included in LINQ that it gets painfully slow. Thanks Regards
Anonymous
August 23, 2011
Is there any way I can sort the related entities while eager loading them?
Anonymous
October 27, 2011
For some reason, I dont have the updated version of the Include() method.... even though I have EF 4.1 installed. Its only letting me pass a string instead of an lambda expression......... Arrrghhhh....
Anonymous
December 18, 2011
mosC, have you added a using/imports for System.Data.Entity at the top of your code file? Without that, you'll only be able to use strings. Had me going for a while!
Anonymous
January 24, 2012
@Fujiy: I showed an example of how to do extra-lazy loading on my blog: see blog.oneunicorn.com/.../extra-lazy-collection-count-with-ef-4-1-part-1 and the following posts. @Martin: We don’t have any plans for this at the moment but please vote for it on the uservoice site if you think it should be a priority: data.uservoice.com/.../72025-ado-net-entity-framework-ef-feature-suggestions @Snahider: You could do this by turning off lazy loading while doing the Remove or possible using a variation of the extra-lazy loading pattern I referenced above. @Sean: I’m not sure what you are referring to. Eager loading is achieved through the Include method. There is no way to get everything (or, more usefully just aggregates) eager loaded automatically without using Include. Please vote for this on uservoice if you believe it should be a priority. @Problem of Eagerly Loading: All loading must be done before the context is disposed, otherwise it’s disposed and can’t load. If you need to use related entities outside the scope of the context then you should use Include or call Load for those entities before leaving the using scope. You could also look at using a Repository pattern to better encapsulate this. @Nikhil: This isn’t currently supported. It is something we would like to do in the future. Please vote for this on uservoice if you believe it should be a priority. @Balaje Sankar: Collections of related entities referenced by navigation properties (e.g. Category.Products) are not sorted. Allowing them to be sorted is something we would like to do in the future. Please vote for this on uservoice if you believe it should be a priority. Thanks, Arthur
Anonymous
March 09, 2012
It seems that intellisense does not work when doing the include. I had a long thread on the forums regarding this and I included an example there. .Include(c => c.LadiesInWaiting.Select(b => b.Princess)) social.msdn.microsoft.com/.../06d658ea-57be-4bc7-857c-1802717974df
Anonymous
March 09, 2012
@Peter Kellner: Any chances that you have installed the EF June 2011 CTP? Just asking because a coleague suggested there could be issue with intellisense in that release.
Anonymous
March 14, 2012
The comment has been removed
Anonymous
May 02, 2012
The comment has been removed
Anonymous
June 19, 2012
How can i load all princesses that have at least one unicorn? I need to exclude every princess that doesn't have any
Anonymous
June 20, 2012
@Pacho You can do that with a simple query. Something like: var happyPrincesses = context.Princesses.Where(p => p.Unicorns.Count >= 1);
Anonymous
July 29, 2012
I like Include, but I also like how you slyly left out the major weakness of it. Namely that you can only use one projection in an Include or else the returned data is jumbled. .Include(c => c.LadiesInWaiting.Select(b => b.Princess.Select( d => d.ThisProjectionFails ))
Anonymous
July 31, 2012
@Travis J Can you explain what you mean by "jumbled". There should be no problem including multiple navigation properties at multiple levels.
Anonymous
October 11, 2012
Good job. I have an answer, how can I load an entity providing an array or list of navigation properties, instead of a single. For example, I have Invoice (Customer and Lines), Customer (Invoices), InvoiceLine (Invoice, Product) and Product (InvoiceLines, Supplier) entities (navigation properties between brackets). So, I need to load Invoice entity, using the Customer and Lines.Product navigation properties. Thanks.
Anonymous
October 12, 2012
@Sebastian - The "Eagerly loading multiple levels of related entities" section of this post has the details you are after. For your example you would want something like context.Invoices.Include(i => i.Customer).Include(i => i.InvoideLines.Select(l => l.Product))...
Anonymous
October 29, 2012
Very Nice
Anonymous
June 11, 2013
thank you, you made the subject less threatening and easy to understand compared to many other articles Ive tried to read
Anonymous
June 17, 2013
Very Nice article, simple and informative :)
Anonymous
October 13, 2015
The comment has been removed
Anonymous
November 10, 2015
very helpful post. thanks :D

Share via

Introduction

Turning off lazy loading for specific navigation properties

Turn off lazy loading for all entities

Summary

Comments

Additional resources

Share via

Using DbContext in EF 4.1 Part 6: Loading Related Entities

Introduction

Eagerly loading related entities

Eagerly loading multiple levels of related entities

Turning off lazy loading for specific navigation properties

Turn off lazy loading for all entities

Explicitly loading related entities

Applying filters when explicitly loading related entities

Using Query to count related entities without loading them

Summary

Comments

Additional resources