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

Question

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.

The Action-Model has a propertie named "User" ? Do you have a DbContext ? — dknaack, Jul 20 '11 at 11:25

score 74 · Accepted Answer · answered Jul 21 '11 at 13:00

74

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.

answered Jul 21 '11 at 13:00

jaffa

26,770
50
178
289

9

The join will happen automatically, assuming your entity is mapped correctly in the context. You can achieve the same with a simpler query: `var qry = from a in Actions.Include("User") select a` If you are joining entities that are not mapped explicitly as Navigation properties (ie: Action does not have a User property, or that is for some reason not mapped as a Navigation property), then you would need the join, remove the Include, and you can then use `select new { Action = a, User = u }` to have EF return the joined data. (The latter is generally referred to as a Projection) – JoeBrockhaus Jan 18 '17 at 18:59

score 25 · Answer 2 · edited May 21 '19 at 09:49

25

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;

edited May 21 '19 at 09:49

Jimmy

864
13
24

answered Feb 13 '17 at 23:50

K0D4

2,373
1
27
26

2

Thanks for the `nameof()`. I keep seeing hardcoded strings of table names. – Zorgarath Feb 21 '17 at 16:38
1

Of all the features they added in C#6, nameof has been the one I've used more than any other! Thanks for the feedback! – K0D4 Feb 21 '17 at 20:52

Enrico Campidoglio · Answer 3 · 2011-07-20T12:04:41.983

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

Hi, your examples all show chained methods, my query is in Expression syntax as I showed above (using from/select). Can you show me how to do this? — jaffa, Jul 20 '11 at 12:36

score 2 · Answer 4 · answered Jul 21 '11 at 10:18

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

From my understanding, `Include("User")` is meant to trigger eager loading, so to turn off lazy loading with `LazyLoadingEnabled = false` is at best redundant but ultimately mislead. I think if you were to swap your 'foo' and 'bar' then the Include would work on the EF query and eager load the additional table. — Zorgarath, Feb 21 '17 at 16:37

score 0 · Answer 5 · answered Jul 20 '11 at 11:34

0

I use for this the LoadWith option

var dataOptions = new System.Data.Linq.DataLoadOptions();
dataOptions.LoadWith<Action>(ac => as.User);
ctx.LoadOptions = dataOptions;

Thats it. ctx is your DataContext. This works for me :-)

answered Jul 20 '11 at 11:34

Mariusz

1,409
12
25

This example uses LINQ to SQL while the question was about Entity Framework. – Enrico Campidoglio Jul 20 '11 at 11:52

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

5 Answers5

Linked