Get Children common to All Parents

Question

I have the following Entities using Entity Framework Core:

public class Parent {
  public Int32 ParentId { get; set; }
  public virtual Collection<ParentChildren> ParentChildrens { get; set; }
}

public class ParentChildren {
  public Int32 ParentId { get; set; }
  public Int32 ChildrenId { get; set; }
  public virtual Parent Parent { get; set; }
  public virtual Children Children { get; set; }
}

public class Children {
  public Int32 ChildrenId { get; set; }
  public virtual Collection<ParentChildren> ParentChildrens { get; set; }
  public virtual Collection<ChildrenLocalization> ChildrenLocalizations { get; set; }
}

public class ChildrenLocalization {
  public Int32 ChildrenId { get; set; }
  public String Language { get; set; }
  public String Name { get; set; }
  public virtual Children Children { get; set; }
}

Given a IQueryable<Parent> I need to, using Linq to Entities lambda expressions:

1. Get the Children common to all Parents;
2. For each Children get its name from ChildrenLocalization with Language="en".

So I tried the following:

var result = context.Parents
  .SelectMany(y => y.ParentChildrens)
  .GroupBy(y => y.ParentId)
  .Where(y => 
     context.Parents
       .SelectMany(y => y.ParentChildrens)
       .Select(z => z.ChildrenId)
       .Distinct()
       .All(z => y.Any(w => w.ChildrenId == z)))
  .SelectMany(y => y)
  .Select(y => new {
    Id = y.ChildrenId,
    Name = y.Children.ChildrenLocalizations.Where(z => z.Language == "en").Select(z => z.Name).FirstOrDefault()
  })
  .GroupBy(x => x.Id)
  .Select(x => x.FirstOrDefault())
  .ToList();

This query is giving the expected result but it seems too complex.

I wasn't able to improve it and, for example, I needed the add the last GroupBy to make it work.

How can I make my query simpler?

Answer 1

Since you have many-to-many relationship, it's better to base (start) the query on the resulting entity (Children), thus avoiding the need of GroupBy /Distinct if you start it from the other end (Parent).

So given

IQueryable<Parent> parents

and assuming you have access to the context, the query can be written as follows:

var query = context.Set<Children>()
    .Where(c => parents.All(p => p.ParentChildrens.Select(pc => pc.ChildrenId).Contains(c.ChildrenId)))
    .Select(c => new
    {
        Id = c.ChildrenId,
        Name = c.ChildrenLocalizations.Where(cl => cl.Language == "en").Select(cl => cl.Name).FirstOrDefault()
    });

which nicely translates to a single SQL.

You start with unique Children. For requirement (2) you simply use the navigation property. The requirement (1) is more complicated (all is always harder to achieve than any), but I think the criteria

parents.All(p => p.ParentChildrens.Select(pc => pc.ChildrenId).Contains(c.ChildrenId))

quite intuitively represents child common to all parents.