Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How does LINQ expression syntax work with Include() for eager loading

I have a query below, but I want to perform an Include() to eager load properties. Actions has a navigation property, User (Action.User)

1) My basic query:

from a in Actions
join u in Users on a.UserId equals u.UserId
select a

2) First attempt:

from a in Actions.Include("User")
join u in Users on a.UserId equals u.UserId
select a

But Action.User is not populated.

3) Try to eager load 'User' into the navigation property in action outside of query:

(from a in Actions
join u in Users on a.UserId equals u.UserId    
select a).Include("User")

In LINQPad trying Include's I get an error:

'System.Linq.IQueryable' does not contain a definition for 'Include' and no extension method 'Include' accepting a first argument of type 'System.Linq.IQueryable' could be found (press F4 to add a using directive or assembly reference)

I think this is because LINQ doesn't support Include().

So I tried in VS; query 2 runs, but returns unpopulated User property. Query 3 the extension method does not seem to exist, although it does exist on Action itself without the query.

like image 815
jaffa Avatar asked Jul 20 '11 11:07

jaffa


People also ask

What is the use of include in LINQ?

Introduction to LINQ Include. LINQ include helps out to include the related entities which loaded from the database. It allows retrieving the similar entities to be read from database in a same query. LINQ Include() which point towards similar entities must read from the database to get in a single query.

How do you use eager loading?

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 blogs and all the posts related to each blog. Include is an extension method in the System.

What is lazy loading and eager loading in LINQ?

Lazy loading in Entity Framework is the default phenomenon that happens for loading and accessing the related entities. However, eager loading is referred to the practice of force-loading all these relations.

What is include in Entityframework?

Entity Framework Classic Include The Include method lets you add related entities to the query result. In EF Classic, the Include method no longer returns an IQueryable but instead an IncludeDbQuery that allows you to chain multiple related objects to the query result by using the AlsoInclude and ThenInclude methods.


4 Answers

I figured it out, thanks for the suggestions anyway. The solution is to do this (2nd attempt in my question):

var qry = (from a in Actions join u in Users on a.UserId equals u.UserId     select a).Include("User") 

The reason intellisense didn't show Include after the query was because I needed the following using:

using System.Data.Entity; 

Everything worked fine doing this.

like image 69
jaffa Avatar answered Oct 11 '22 13:10

jaffa


Better, refactor friendly code (EF6)

using System.Data.Entity; [...] var x = (from cart in context.ShoppingCarts          where table.id == 123          select cart).Include(t => t.CartItems); 

or

var x = from cart in context.ShoppingCarts.Include(nameof(ShoppingCart.CartItems))         where table.id == 123         select cart; 

Update 3/31/2017

You can also use include in lambda syntax for either method:

var x = from cart in context.ShoppingCarts.Include(p => p.ShoppingCart.CartItems))         where table.id == 123         select cart; 
like image 26
K0D4 Avatar answered Oct 11 '22 12:10

K0D4


If what you want is a query that will return all Action entities whose associated User entity actually exists via the Action.UserId foreign key property, this will do it:

var results = context.Actions
    .Include("User")
    .Where(action =>
        context.Users.Any(user =>
            user.UserId == action.UserId));

However you don't have to use foreign key properties in order to do filtering, since you also have navigation properties. So your query can be simplified by filtering on the Action.User navigation property instead, like in this example:

var results = context.Actions
    .Include("User")
    .Where(action => action.User != null);

If your model states that the Action.User property can never be null (i.e. the Action.UserId foreign key is not nullable in the database) and what you want is actually all Action entities with their associated Users, then the query becomes even simpler

var results = context.Actions.Include("User");
like image 33
Enrico Campidoglio Avatar answered Oct 11 '22 13:10

Enrico Campidoglio


Doing the basic query mentioned in your posted question you won't be able to see the User properties unless you return an anonymous type as following:

from a in Actions
join u in Users on a.UserId equals u.UserId
select new
{
   actionUserId = a.UserId
   .
   .
   .
   userProperty1 = u.UserId
};

However to use the Include method on the ObjectContext you could use the following:

Make sure you have LazyLoading off by using the following line:

entities.ContextOptions.LazyLoadingEnabled = false;

Then proceed by

var bar = entities.Actions.Include("User");
var foo = (from a in bar
           select a);
like image 33
Ryan Avatar answered Oct 11 '22 12:10

Ryan