LINQ Include on a one-to-many with .Where() not working?

I’m trying to query Users including each user’s Interests, but only where an Interest meets certain criteria:

  return db.Users.Include(u => u.Interests.Where(s => s.TenantId == tenantId))

But I get an error:

The Include path expression must refer to a navigation property
defined on the type. Use dotted paths for reference navigation
properties and the Select operator for collection navigation
properties.

I played with the idea of pushing the .Where outside, but haven’t been able to get it working.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

To include only some of the interests you won’t be able to use the Include method, as it doesn’t support this. You’ll need to manually join the Interests with the Users:

var query = from user in db.Users
    join interest in db.Interests.Where(s => s.TenantId == tenantId)
    on user.InterestId equals interest.Id //todo:  will need to be updated
    into interests;
    select new { user, interests};

Method 2

Try this:

return db.Users.Include("Interests").Where(u => u.Interests.Any(i => i.TenantId == tenantId));

This causes the users to be loaded, but only where the tenantId matches. The Interests related entities will be eager loaded for those users when the query executes.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0