让所有父母共有的孩子

Question

我有以下使用 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; }

}

鉴于IQueryable<Parent>我需要，使用 Linq to Entities lambda 表达式：

获取所有 Parents 共有的 Children；

对于每个从withChildren获取其名称。ChildrenLocalizationLanguage="en"

所以我尝试了以下方法：

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();

此查询给出了预期的结果，但它似乎太复杂了。

我无法改进它，例如，我需要添加最后一个 GroupBy 才能使其工作。

如何使我的查询更简单？

Answer 1

由于您拥有多对多关系，因此最好将查询基于（开始）结果实体 ( Children)，从而避免需要GroupBy/Distinct如果您从另一端 ( Parent) 开始它。所以给出IQueryable<Parent> parents并假设您有权访问上下文，则可以按如下方式编写查询：var query = context.Set<Children>()&nbsp; &nbsp; .Where(c => parents.All(p => p.ParentChildrens.Select(pc => pc.ChildrenId).Contains(c.ChildrenId)))&nbsp; &nbsp; .Select(c => new&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; Id = c.ChildrenId,&nbsp; &nbsp; &nbsp; &nbsp; Name = c.ChildrenLocalizations.Where(cl => cl.Language == "en").Select(cl => cl.Name).FirstOrDefault()&nbsp; &nbsp; });这很好地转换为单个 SQL。您从unique Children开始。对于要求 (2)，您只需使用导航属性。要求 (1) 更复杂（all总是比any更难实现），但我认为标准parents.All(p => p.ParentChildrens.Select(pc => pc.ChildrenId).Contains(c.ChildrenId))相当直观地代表所有父母共有的孩子。

Answer 2

鉴于IQueryable<Parent> parentsparents.SelectMany(p => p.ParentChildrens).Select(pc => pc.Children).Where(c => c.ParentChildrens&nbsp; &nbsp; .Select(pc => pc.ParentId)&nbsp; &nbsp; .OrderBy(i => i)&nbsp; &nbsp; .SequenceEqual(parents.Select(p => p.ParentId).OrderBy(i => i))).Select(c => new{&nbsp; &nbsp; Id = c.ChildrenId,&nbsp; &nbsp; c.ChildrenLocalizations.FirstOrDefault(cl => cl.Language == "en").Name})

Answer 3

假设你有 3 个父母，ID 为 10、11、12 假设你有 3 个孩子，ID 为 20、21、22父子表：ChildId | ParentId&nbsp; 20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; 20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;11&nbsp; 20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; 21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; 21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;11&nbsp; 22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; 22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12所以孩子 20 有父母 10/11/12；孩子 21 有父母 10/11；孩子 22 有父母 10/12。“获得所有父母共有的孩子”；如果这意味着：获取在其父母集合中具有每个可用父母的孩子，那么很容易看出您只想要 20 个孩子，并且您只想要这个孩子一次因为所有的父子关系都是唯一的，我们知道如果有 X 个父母，我们想要的孩子正好有 X 个父母。你不想要这些孩子的所有属性，你只想“从 ChildrenLocalization 中获取它的名字 Language="en "，是否总是有零个或一个这样的名字？如果有更多我们应该取哪个？任何名字，或所有的名字？因为我们需要将自己限制为 ParentCount 等于父母数量的所有孩子，所以我们还需要计算每个孩子的父母数量var childrenWithParentCount = dbContext.Children.Select(child => new{&nbsp; &nbsp; // "get its name from ChildrenLocalization with Language="en"&nbsp; &nbsp; LocalizationName = child.ChildrenLocalizations&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Where(localization => localization.Language == "en")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Select(localization => localizaition.Name)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .FirstOrDefault();&nbsp; &nbsp; // or if you want all names:&nbsp; &nbsp; LocalizationNames = child.ChildrenLocalizations&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Where(localization => localization.Language == "en")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Select(localization => localizaition.Name)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .ToList;&nbsp; &nbsp; ParentCount = child.ParentChildren&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Select(parentChild => parentChild.ParentId)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Count();});现在我们不想要所有这些孩子，我们只想要那些 ParentCount 等于 Parents 数量的孩子var childrenWithAllParents = childrenWithParentCount&nbsp; &nbsp; .Where(child => !child.ParentCount == dbContext.Parents.Count());你注意到了吗，我只创建了 IQueryable 对象，我还没有执行任何查询。要执行查询：var result = childrenWithAllParents.ToList();有些人喜欢用一个大的 LINQ 语句来打动别人；好吧，它是：var result = dbContext.Children.Select(child => new{&nbsp; &nbsp; LocalizationName = child.ChildrenLocalizations&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Where(localization => localization.Language == "en")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Select(localization => localizaition.Name)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .FirstOrDefault();&nbsp; &nbsp; ParentCount = child.ParentChildren&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Select(parentChild => parentChild.ParentId)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Count();}).Where(child => !child.ParentCount == dbContext.Parents.Count()).ToList();幸运的是，您的数据库管理系统足够聪明，可以记住 Parents 的数量，而不是为每个 Child 重新计算一次。

Answer 4

如果我理解正确，这可能会起作用。这将是一个单一的查询。&nbsp; &nbsp; var result =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (from parent in context.Parents&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from pToC in parent.ParentChildrens&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where pToC.Children.ParentChildrens.Select(pc => pc.ParentId).Distinct().Count() == context.Parents.Count()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from childLocation in pToC.Children.ChildrenLocalizations&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where childLocation.Language == "en"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; select new { pToC.Children.ChildrenId, childLocation.Name }).Distinct();

Answer 5

您需要将呼叫从您的分组中分离出来。&nbsp; List<Parent> result = context.Parents&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Include(i => i.ParentChildrens)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .ThenInclude(i => i.Children)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .ThenInclude(i => i.ChildrenLocalizations)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .ToList();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var finalResult = result.SelectMany(c => c.ParentChildrens, (o, j) =>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return new&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Id = j.ChildrenId,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Parent = o.ParentId,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Name = j.Children.ChildrenLocalizations.First(c => c.Language == "en").Name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; };&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });