实体框架-检查是否有孙记录 [英] Entity Framework - check whether has grandchild records

问题描述

我正在使用ASP Net Core 2.0和实体框架来访问MySQL数据库.我的模型结构看起来像

I am using asp net core 2.0 and entity framework to access a MySQL database. My model structure looks like

public class Parent 
{
   [Key]
   public int Id { get; set; }
   public virtual ICollection<Child> Child { get; set; }
   [NotMapped]
   public virtual bool HasGrandchild  { get; set; }
}

public class Child
{
   [Key]
   public int Id { get; set; }
   public int ParentId { get; set; }
   public virtual ICollection<Grandchild> Grandchild { get; set; }
}

public class Grandchild
{
   [Key]
   public int Id { get; set; }
   public int ChildId { get; set; }
}

我想检查在 Parent 类中是否有任何孙子记录.我知道我可以使用 Include 和 ThenInclude 来检索子代和孙代.但是，孙子确实很大.我不想退还他们.或者，我可以使用 for 循环来计数孙子代号.例如，

I want to check whether there are any records of grandchild in the class Parent. I know I can use Include and ThenInclude to retrieve the child and grandchild. However, the Grandchild is really big. I don't want to return them. Alternatively, I could use for loop to count the number Grandchild. For example,

List<Parent> p_list = _context.Parent.ToList();
foreach(Parent p in p_list) 
{
    List<Child> c_list = _context.Child
        .Where(c => c.ParentId == p.Id)
        .ToList();
    int NumberOfGrandchild = 0;
    foreach (Child c in c_list) 
    {
        List<Grandchild> gc_list = _context.Grandchild
           .Where(gc =>gc.ChildId == c.Id)
           .ToList();
        NumberOfGrandchild  += gc_list.Count();
    }
    p.HasGrandchild = false;
    if (NumberOfGrandchild > 0) {
        p.HasGrandchild = true;
    }
}

此方法的性能非常慢，尤其是对于大表.

The performance of this method is very slow, especially for the big table.

有没有更好的方法来检查是否有孙记录.

Is there any better method to check whether there are any grandchild records.

推荐答案

var parents = _context.Parents
                      .Where(p => p.Children.SelectMany(c => c.Grandchildren).Any());

该应该为您提供所需的内容.您希望尽可能避免使用 Include ，因为它将整个表的列添加到查询中.(至少它是对< = EF5所做的.)

This should give you what you're looking for. You want to avoid Include as much as possible, because it adds the entire table's columns to the query. (At least it did with <= EF5.)

这里发生的是您正在生成子查询，其中最低的子查询将为 parent 获取所有的 Children ，并查看是否有任何 Child 有任何孙子.

What's happening here is that you're generating subqueries, the lowest of which will get all Children for the parent, and see if any Child has any Grandchild.

如果您想确定 Parent 是否有任何 Grandhildren ，则可以将 Where 子句的该部分移开，或者只具有 true 在一个匿名对象中.

If you want to determine if a Parent has any Grandhildren then you can either move that portion of the Where clause, or just have true in an anonymous object.

var parents = _context.Parents
                      .Select(p => new { // Can be other DTO if you have one
                          Parent = p,
                          HasGrandchildren = p.Children
                                              .SelectMany(c => c.Grandchildren)
                                              .Any()
                      });

或

var parents = _context.Parents
                      .Where(p => p.Children.SelectMany(c => c.Grandchildren).Any())
                      .Select(p => new {
                          Parent = p,
                          HasGrandchildren = true
                      });

第二个可能性能更高，因为它能够在单个查询中执行该操作.第一个可能可能会使查询的速度稍慢一些，但也可以使您获得所有信息.

The second one probably be a little more performant because it would be able to perform that within a single query. The first might be slightly slower of a query, but it would also allow you to get everything.

如果要在有子孙的孩子和没有子孙的孩子之间进行划分，还可以通过 GroupBy 进行其他操作.

There are also other ways you could do that with a GroupBy if you want to split them between those that do have grandchildren, and those that don't.

这篇关于实体框架-检查是否有孙记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！