EF. How to include only some sub results in a model?

Question

I'm trying to select list of Users and for each User JobTitle in correct language depended of strLang selected by user.

Something like that:

IList<User> myData;
myData = Context.Users.Where(u => u.Location == strLocation)
                .Include(u => u.JobTitles.Where(e => e.Language == strLang))
                    .ToList();

But it seems Include doesn't like Where clause

Error message: "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. Parameter name: path" — Whistler, Jan 23 '16 at 12:25
Would this help: http://stackoverflow.com/a/16801205/3279876 — Sami, Jan 23 '16 at 12:42
No as I don't want to create new model/object, I just want to reduce number of results — Whistler, Jan 23 '16 at 12:52
Try Include(u => u.JobTitles).Where(e => e.Language == strLang).ToList(); — Sami, Jan 23 '16 at 12:56
No, unfortunately it doesn't work: "'User' does not contain definition for Language. — Whistler, Jan 23 '16 at 13:00
Is JobTitles a navigation property of Users? Can we see the Users and JobTitle classes? — Kirsten, Jan 23 '16 at 16:08

Alexander Derck · Accepted Answer · 2016-01-23T16:27:09.437

You can't conditionally include only a few entities of a related collection, so you should use projection to get the stuff you need:

IList<User> myData;
var temp = Context.Users.Where(u => u.Location == strLocation)
      .Select(u => new
      {
        User = u;
        Locations = u.JobTitles.Where(e => e.Language == strLang));
      });

foreach(var t in temp)
{
   User user = t.User;
   user.Locations = t.Locations;
   myData.Add(user);
}

Jonathan Magnan · Answer 2 · 2018-02-19T13:55:02.817

You cannot do it by using the "Include" method since it only take naviation properties.

Disclaimer: I'm the owner of the project EF+ on github.

EF+ Query IncludeFilter allow you to easily filter related entities:

// using Z.EntityFramework.Plus; // Don't forget to include this.    
IList<User> myData;
    myData = Context.Users.Where(u => u.Location == strLocation)
                    .IncludeFilter(u => u.JobTitles.Where(e => e.Language == strLang))
                        .ToList();

You can find the project here

You can find the documentation here

Behind the code, IncludeFilter do exactly like Alexander answer by using a projection.

EF. How to include only some sub results in a model?

2 Answers2

Linked