Load navigation property explicitly

Question

public class MyDbContext: DbContext { } public class Product { public long Id {get; set;} public long CategoryId {get; set;} [ForeignKey("CategoryId")] public virtual Category Category {get; set;} } public class Category { public long Id {get; set;} public string Name {get; set;} } List<Product> GetProducts() { var context = new MyDbContext(); var products = context.Set<Product>().ToList(); var categories = context.Set<Category>().ToList(); foreach(var product = in products) { product.Category = categories.First(c => c.Id == product.CategoryId); } return products; }

Here I want to retrieve all products with associated categories with best performance. I first tried with lazy loading, but it results in many database queries. Then I use eager loading, but the query scripts generated are not so efficient especially for complex query. So I used the following way:

get all Products,
get all Categories and
set the navigation property "Category" of a product manually from the fetched categories

My questions are:

-Will EF still lazy load the navigation property "Category" even after I set it manually?

-Is there any better solution to eager loading for complex query?

you realy don need to make it explicitly. Change tracking automatically fixups dependencies for you. See my unswer to somewhat another question, but it also cosiders what you need: stackoverflow.com/questions/17766211/… — Pavel Voronin
– Pavel Voronin, Commented Dec 13, 2013 at 12:13

Gert Arnold · Accepted Answer · 2012-12-07 14:18:52Z

4

You get the best result if you use Include:

var products = context.Set<Product>().Include("Category");

This will load products and their categories in one query and the Category properties will not trigger lazy loading any more.

Even better is the extension method Include: context.Set<Product>().Include(p => p.Category);.

answered Dec 7, 2012 at 14:18

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

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

3 Comments

user1012036 Over a year ago

For simple query, it's no problem using eager loading. But if there are many nesting levels of entity objects and the relations are complex, the query is very slow because many tables are joined. This is the reason I try to load the data explictly in this way.

Gert Arnold Over a year ago

But then you should a more complex scenario if that's your main concern. For the example you show, Include is the way. A rule of the thumb is that there should not be more than tree Includes in one statement. It is hard to give further general recommendations for complex queries. It really depends on the situation.

Muflix Over a year ago

@GertArnold What if you created a new product, which is not in database yet and you need to load its categories?

Collectives™ on Stack Overflow

Load navigation property explicitly

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related