Filtering Related Entites with Entity Framework

Question

According to this StackOverflow answer:

Linq to Entities - how to filter on child entities

you should be able to filter down the list of related entities in Entity Framework by utilizing a projection, like I've done here:

Company company = _context.Company
.Where(g => g.CompanyId == id)
.Select(comp => new
{
    group = comp,
    operators = comp.Operator,
    formFamilies = comp.FormFamily.Where(ff => ff.IsActive ?? false)
}).AsEnumerable().Select(i => i.group).FirstOrDefault();

To give a quick overview of what I'm trying to obtain here, I'm trying to get a list of all of the active form families associated with this company object, however, whenever I restrict the results in any way, the result set is empty.

• If the line were formFamilies = comp.FormFamily then it returns two results, one active one inactive
• If the line is formFamilies = comp.FormFamily.Where(ff => true) then it returns nothing
• If the line is formFamilies = comp.FormFamily.OrderBy(ff => ff.FormFamilyId) then it returns nothing.

Any sort of modification that I do to comp.FormFamily means the result set returns nothing, I've dug through the deepest sections of SA to try to find a solution, and tried every solution I've found, but nothing seems to cause this list to return anything.

Answer 1

Assuming that Company and FormFamily entities has one to many relationship I would suggest to use a join statement.Something like this should give you what you are looking for.

  var company =  from c in _context.Company
                           join f in _context.FormFamily
                           on c.Id equals f.CompanyId   
                           where c.Id == id
                           select new Company()
                           {
                               Id = c.Id,
                               operators = c.Operator.ToList(),
                               formFamilies = c.FormFamily.Where(x=>x.IsActive == 
                                               false).ToList()
                           } .FirstOrDefault();

Hope this helps.