Include / ThenInclude with where in EF Core

Question

I need to use where in ThenInclude

        var templatesFields = await _context.Sections
        .Include(x => x.Subtitles)
        .ThenInclude(r => r.Fields.Where(t=>t.TemplatesFields.TemplateID==TemplateID))
        .ThenInclude(r => r.OptionSources)
        .ThenInclude(r => r.OptionsSourcesDetails)
        .ToListAsync();

Derviş Kayımbaşıoğlu · Accepted Answer · 2020-09-21T07:35:24.903

29

you cannot use where condition inside Include or ThenInclude. What you can do is:

var templatesFields = await _context.Sections
    .Include(x => x.Subtitles)
    .ThenInclude(r => r.Fields)
    .ThenInclude(r => r.OptionSources)
    .ThenInclude(r => r.OptionsSourcesDetails)
    .Where(t=>t.Subtitles.Fields.Any(x => x.TemplatesFields.TemplateID==TemplateID))
    .ToListAsync();

EDIT: This will be possible with .Net Core 5.0 (which is in preview right now)

var blogs = context.Blogs
    .Include(e => e.Posts.Where(p => p.Title.Contains("Cheese")))
    .ToList();

Filtered Include

edited Sep 21 '20 at 07:35

answered Feb 10 '19 at 19:28

Derviş Kayımbaşıoğlu

28,492
4
50
72

I've formulated my code with this structure though i haven't tried it yet for lacking data, still i 1 up it. Thanks! – sshanzel Feb 13 '19 at 08:52
2

I'm still learning this, so test it before assuming I'm right, but I'm pretty sure this doesn't work as intended. The where clause of Fields is to reduce the field records attached to the subtitles, but in the solution provided, it is just removing any templates that have no Subtiutle.Fields records. In effect the question asks for "Give me All templates and include field information that matched the condition" where as the answer provided says "Give me all the templates and All the field information where the template has Any field that matches the condition." – Chaos Crafter Apr 06 '20 at 02:11
IT is not problem, IT is a feature. Check NEt COre 5 https://www.learnentityframeworkcore5.com/whats-new-in-ef-core-5/filtered-included – Derviş Kayımbaşıoğlu Sep 21 '20 at 07:33
probably this solution won't work for OptionSources and OptionsSourcesDetails if you want to add where clause for its entities. correct me if i'm wrong. What will be the solution in this situation. In EF Core 3.1 – Indigo_heart Feb 02 '23 at 20:40

score -2 · Answer 2 · edited Jul 09 '21 at 01:56

From this:

Filtering in Include or ThenInclude is not supported. Create projection by using Select:

questionnaire = _context.Questionnaires
    .Select(n => new Questionnaire
    {
        Id = n.Id,
        Name = n.Name,
        Questions = n.Questions.Select(q => new Question
        {
           Id = q.Id,
           Text = q.Text,
           Answers = q.Where(a => a.UserId == userId).ToList()
        }).ToList()
    })
    .FirstOrDefault(qn => qn.Id == questionnaireId);

Include / ThenInclude with where in EF Core

2 Answers2