How to convert this linq join to Entity framework Include and ThenInclude

Question

This is my working linq join which i want to rewrite using to entity framework include/theninclude

requirementlist = await(from r in _context.Requirement
                        join tr in _context.TemplateRequirement on r.RequirementId equals tr.RequirementId
                        join t in _context.Template on tr.TemplateId equals t.TemplateId
                        where t.Name == certificationType && t.ItemTypeId == itemTypeId
                        select new Requirement()
                        {
                            Number = r.Number,
                            RequirementId = r.RequirementId,
                            Oqedescription = r.Oqedescription,
                            HelpText = r.HelpText,
                            IsGroupable = r.IsGroupable,
                            Name = r.Name,
                            Description = r.Description,
                            AcceptanceCriteria = _context.AcceptanceCriteria.Where(x => x.RequirementId == r.RequirementId).ToList()
                        }).ToListAsync();

I tried writing like this

var responses = await _context.Requirement.Include(r => r.TemplateRequirement)
                    .ThenInclude(tr => tr.Template)
                    .ThenInclude(t => t.ItemTypeId == itemTypeId && t.Name == certificationType).Select(r => new Requirement()
                    {
                        Number = r.Number,
                        RequirementId = r.RequirementId,
                        Oqedescription = r.Oqedescription,
                        HelpText = r.HelpText,
                        IsGroupable = r.IsGroupable,
                        Name = r.Name,
                        Description = r.Description,
                        AcceptanceCriteria = _context.AcceptanceCriteria.Where(x => x.RequirementId == r.RequirementId).ToList()
                    }).ToListAsync();

this is not working as am using expression inside theninclude. How can I write this properly in EntityFramework.

My question is, are you getting an error saying the expression should represent a property access? As far as I know, that is the only way it works, unless it has changed recently. — insane_developer, Aug 05 '20 at 15:28
Take a moment to read through the [editing help](//stackoverflow.com/editing-help) in the help center. Formatting on Stack Overflow is different than on other sites. The better your post looks, the easier it is for others to read and understand it. — gunr2171, Aug 05 '20 at 15:33
What EF are you using: EF 6.x / EF Core 2.0 / EF Core 2.1 / EF Core 3.x? — NetMage, Aug 05 '20 at 18:00
Does this answer your question? [Filtering on Include in EF Core](https://stackoverflow.com/questions/43618096/filtering-on-include-in-ef-core) — NetMage, Aug 05 '20 at 18:09
@insane_developer this is the error am receiving "The expression '((t.ItemTypeId == __itemTypeId_0) AndAlso (t.Name == __certificationType_1))' is invalid inside Include operation. The expression should represent a property access: 't => t.MyProperty'. To target navigations declared on derived types use cast, e.g. 't => ((Derived)t).MyProperty' or 'as' operator, e.g. 't => (t as Derived).MyProperty'. Collection navigation access can be filtered by composing Where, OrderBy(Descending), ThenBy(Descending), Skip or Take operations." — sravan kumar, Aug 07 '20 at 13:53
@sravankumar that is your answer. What you want to do is not supported by EF, not even Core. There might be a third party package that you can use, but I don't know of any. — insane_developer, Aug 07 '20 at 15:12

How to convert this linq join to Entity framework Include and ThenInclude

0 Answers0