Include not working in LINQ query but works in LINQ Fluent API

user_372296 0
Viewed 1431

The Problem

I have a LINQ query (against Entity Framework) that use Include to include some navigation properties. One of those properties uses ThenInclude to include its own property collection. When I run the query, the first level properties are included on the primary object but the sub-collection (the one using ThenInclude) is always empty.

However, if I change the query to use Fluent API form, the query works and the sub-collection is actually included. Why does this work for the Fluent form and not the normal LINQ query?

Example

//FAIL - This returns Benefits but Benefits.Dates.Count = 0 on all Benefits
var list1 = (from s in _context.Subscribers
               .Include(s => s.Dates)
               .Include(s => s.Benefits)
               .ThenInclude(b => b.Dates)
               where s.Id == 13643
               select new { benefits = s.Benefits }).ToList();

//SUCCESS - This returns Benefits and Benefits.Dates.Count is > 0 on the ones with Dates
var list2 = _context.Subscribers
               .Include(s => s.Dates)
               .Include(s => s.Benefits)
               .ThenInclude(b => b.Dates)
               .Where(s => s.Id == 13643)
               .Select(s => new { benefits = s.Benefits}).ToList();

Am I mistaken that these queries should give the same output?

Update

I just tried manually linking things using LINQ and I am able to get Benefit dates included -- granted, its not apples to apples as the resulting set is different, but the point is that Include(b => b.Dates) seems to work in this case. 

var list3 = (from s in _context.Subscribers.Include(s => s.Dates)
             join b in _context.Benefits.Include(b => b.Dates) on s.Id equals b.SubscriberId
             select new {benefits = b}).ToList();

I'm beginning to wonder if ThenInclude() may be a little more restrictive in where/when it can be used?

Update 2

I just noticed a warning in my Debug Output window that led me to this link about ignored includes. This seems to be on the right track as the Debug Output clearly indicates that these includes are being ignored.

What doesn't make sense, though, is that I'm actually selecting to anonymous in both examples and only one of them seems to ignore the includes. Why one and not the other?

ef-core-2.1 linq c#
1 Answers

This is just a guess: since You have Datas, in both Subscribers and Benefits, there is a chance that you are not querying the correct model/entity... you can try confirming the entity type like below (assuming Benefit is your Entity type).

var list1 = (from s in _context.Subscribers
               .Include(s => s.Dates)
               .Include(s => s.Benefits)
               .ThenInclude(b => (b as Benefit).Dates)
               where s.Id == 13643
               select new { benefits = s.Benefits }).ToList();

var list2 = _context.Subscribers
           .Include(s => s.Dates)
           .Include(s => s.Benefits)
           .ThenInclude(b => (b as Benefit).Dates) // <-- I suggest renaming s to b
           .Where(s => s.Id == 13643)
           .Select(s => new { benefits = s.Benefits}).ToList();
user_82115280
Related