Entity Framework Include () not working in complex query

Question

Consider the 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 works as expected, but item.ItemProp1.NavProp1 is NULL. As explained here , this is because the request does change after using Include() . but the question is, what is the solution to this situation?

Edit:

When I change the request this way everything works fine:

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

Regarding this article, I think the problem is ... but the solution provided by the author does not work in my situation (due to the use of the anonymous type in the final selection, and not the type of entity ).

Answer 1

As you already mentioned, Include is only valid when the end result of a query consists of objects that must include Include -d navigation properties.

So, in this case, Include has the effect:

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

The SQL query will contain a JOIN , and each SampleEntity will load NavProp1 .

In this case, it does not affect:

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

The SQL query does not even contain a JOIN ; EF completely ignores Include .

If in the last query you want SampleEntity contain their NavProp1 , you can do:

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

Now the Entity Framework retrieves SampleEntity and NavProp1 entities from the database separately, but glues them together with the relationship correction process. As you can see, Include not required to happen.

However, if NavProp1 is a collection, you will notice that ...

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

... will still execute the query to retrieve NavProp1 by lazy loading. Why is this?

While fixing the relationship fills the properties of NavProp1 , it does not put them as downloaded. This only happens when Include loads properties. So now we have SampleEntity all having their own NavProp1 s, but you cannot access them without starting 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 not making NavProp1 virtual.)

Now you will get NavProp1 without a new request.

For reference navigation properties, this does not apply, lazy loading does not start when it is turned on.

In the Entity Framework, the situation has changed a lot in this area. At first (at the time of writing), lazy loading is not enabled, not to mention its inadvertent launch. But Include behavior is also different. Now a query like _db.SampleEntity.Include(s => s.NavProp1).Select(s => new { s }) will include NavProp1 in the final result. EF-core is smarter in finding "Includable" objects in the end result.

Answer 2

I know that this will probably be a little laugh, but don't forget the obvious how I did it. The row in the database did not actually have a foreign key reference! First I had to check the dam data before thinking that EF Include is not working! Hmm 30 minutes of my life I will not return.

Answer 3

If your model is defined correctly, it should work without 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); 

Answer 4

How did you find that item.ItemProp1.NavProp1 is null. EF uses a proxy to download all the necessary properties when trying 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

 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 have no problem mapping EF navigation properties.