Entity Framework Include() is not working within complex query

Question

Consider following LINQ query:

var item = (from obj in _db.SampleEntity.Include(s => s.NavProp1)
           select new
           {
                ItemProp1 = obj,
                ItemProp2 = obj.NavProp2.Any(n => n.Active)
           }).SingleOrDefault();

This runs as expected, but item.ItemProp1.NavProp1 is NULL. As it explains here this is because of the query actually changes after using Include(). but the question is what is the solution with this situation?

Edit:

When I change the query like this, every things works fine:

var item = (from obj in _db.SampleEntity.Include(s => s.NavProp1)
           select obj).SingleOrDefault();

Regarding to this article I guess what the problem is... but the solution provided by author not working in my situation (because of using anonymous type in final select rather than entity type).

What is the type of `item.ItemProp1` from first query and type of `item` from second query? In my opinion second query should return `SampleEntity` proxy. I agree that the whole problem is because anonymous type that you use in your query. The last thing I would check is `(from obj in _db.SampleEntity.Include(s => s.NavProp1) select obj).Select(obj=> new {ItemProp1 = obj, ItemProp2 = obj.NavProp2.Any(n => n.Active)}).SingleOrDefault()` — rraszewski, Jan 21 '15 at 11:37

Gert Arnold · Accepted Answer · 2018-07-14T09:03:01.860

As you mentioned, Include is only effective when the final result of the query consists of the entities that should include the Include-d navigation properties.

So in this case Include has effect:

var list = _db.SampleEntity.Include(s => s.NavProp1).ToList();

The SQL query will contain a JOIN and each SampleEntity will have its NavProp1 loaded.

In this case it has no effect:

var list = _db.SampleEntity.Include(s => s.NavProp1)
            .Select(s => new { s })
            .ToList();

The SQL query won't even contain a JOIN, EF completely ignores the Include.

If in the latter query you want the SampleEntitys to contain their NavProp1s you can do:

var list = _db.SampleEntity
            .Select(s => new { s, s.NavProp1 })
            .ToList();

Now Entity Framework has fetched SampleEntitys and NavProp1 entities from the database separately, but it glues them together by a process called relationship fixup. As you see, the Include is not necessary to make this happen.

However, if Navprop1 is a collection, you'll notice that...

var navprop1 = list.First().s.Navprop1;

...will still execute a query to fetch Navprop1 by lazy loading. Why is that?

While relationship fixup does fill Navprop1 properties, it doesn't mark them as loaded. This only happens when Include loaded the properties. So now we have SampleEntity all having their Navprop1s, but you can't access them without triggering lazy loading. The only thing you can do to prevent this is

_db.Configuration.LazyLoadingEnabled = false;
var navprop1 = list.First().s.Navprop1;

(or by preventing lazy loading by disabling proxy creation or by not making Navprop1 virtual.)

Now you'll get Navprop1 without a new query.

For reference navigation properties this doesn't apply, lazy loading isn't triggered when it's enabled.

In Entity Framework core, things have changed drastically in this area. A query like _db.SampleEntity.Include(s => s.NavProp1).Select(s => new { s }) will now include NavProp1 in the end result. EF-core is smarter in looking for "Includable" entities in the end result. Therefore, we won't feel inclined to shape a query like Select(s => new { s, s.NavProp1 }) in order to populate the navigation property. Be aware though, that if we use such a query without Include, lazy loading will still be triggered when s.NavProp1 is accessed.

I'm sad I can't upvote this twice. One of the best answers I've read on SO! — CleverPatrick, Aug 23 '16 at 19:12
I realise it's been more than 2 years since you've posted this answer, but in my tests on EF 6.1.3, lazy loading is not triggered if you happened to include `NavProp1` into the anonymous type; relationship fixup does its job there. — Roman Starkov, May 21 '17 at 04:13
(admittedly what I said only applies when `NavProp1` is a single related entity rather than a collection of related entities - in the latter case, a lazy load still occurs) — Roman Starkov, May 21 '17 at 06:09
This is actually correct, and has always been at least since EF 4.4.1. In ef-core things are different. I modified the answer to cover these insights. — Gert Arnold, May 21 '17 at 07:10
Why even bother accessing `list.First().s.Navprop1` at that point instead of just `list.First().Navprop1`? You've already been forced to change the shape of your resulting anonymous object to have a separate `Navprop1` property. — xr280xr, Feb 10 '21 at 05:35
@xr280xr But that doesn't demonstrate the lazy-loading issue. Normally, when applying this pattern (for example to mimic filtered `Include`) one would do `.Select(s => new { s, s.NavProp1 }).AsEnumerable().Select(x => x.s)` so you get the list of entities. In that case `list.First().Navprop1` would trigger lazy loading, if enabled. — Gert Arnold, Feb 10 '21 at 10:38

score 2 · Answer 2 · answered Dec 11 '15 at 10:05

2

I know this will probably get a few laughs, but don't forget the obvious like i just did. The row in the database didn't actually have a foreign key reference! I should have checked the dam data first before thinking EF Include wasn't working! Grrr. 30 minutes of my life I won't get back.

answered Dec 11 '15 at 10:05

sarin

5,227
3
34
63

i my case, EF had auto-created two columns and the FK was on a column i wasn't using. – mugume david Jan 23 '16 at 12:57

score 1 · Answer 3 · answered Jan 20 '15 at 21:28

If your model is defined properly it should work without any problems.

using System.Data.Entity;

var item = _db.SampleEntity
                   .Include(p => p.NavigationProperty)
                   .Select(p => new YourModel{
                         PropertyOne = p.Something,
                         PropertyTwo = p.NavigationProperty.Any(x => x.Active)
                    })
                   .SingleOrDefault(p => p.Something == true);

rraszewski · Answer 4 · 2015-01-20T21:58:49.207

0

How did you find that item.ItemProp1.NavProp1 is null. EF uses proxies to load all required properties when you try to access it.

What about

var item = (from obj in _db.SampleEntity.Include(s => s.NavProp1)
   select obj).SingleOrDefault();

Assert.IsNotNull(obj.NavProp1);
Assert.IsNotNull(obj.NavProp2);

You can also try with

var item = (from obj in _db.SampleEntity.Include(s => s.NavProp1)
   select new
   {
        ItemProp1 = obj,
        NavProp1 = obj.NavProp1,
        ItemProp2 = obj.NavProp2.Any(n => n.Active)
   }).SingleOrDefault();

Assert.IsNotNull(item.NavProp1)

Of course I assume that you don't have any problems with EF navigation property mappings.

edited Jan 20 '15 at 21:58

answered Jan 20 '15 at 21:51

rraszewski

1,135
7
21

Thanks for your reply, I don't think there is a problem with how EF load values. after using for example methods like `SingleOrDefault()` all entities and related properties is populated with data... how ever, i update the question hope it helps... thanks – Mehdi JM Jan 21 '15 at 10:30

score 0 · Answer 5 · answered Apr 29 '23 at 00:05

Since this is the first result coming up in web searches, then I want to make sure this answer is also included here. Note that the accepted answer here also worked for me, but this is much easier and seems more correct--at least for simple queries using EF Core:

https://stackoverflow.com/a/71362717/1467396

Make sure you are using "Microsoft.EntityFrameworkCore", NOT "System.Data.Entity"

Entity Framework Include() is not working within complex query

Edit:

5 Answers5

Linked

Related