Entity Framework - check whether has grandchild records

Question

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; }
}

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.

krillgar · Answer 1 · 2019-08-06T10:06:01.047

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

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.)

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.

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

or

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.

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.

Thanks for your suggestion. Now I can select the entry with Grandchildren. But how could I assign it to the variable `HasGrandchild`? I want to return all entities but with an extra field `HasGrandchild` — Bangyou, Aug 06 '19 at 00:08
@Bangyou Could you loop the above `parents` using foreach and use`item.HasGrandchild=true`?Or you could use `Select()` to manually return Parents. — Ryan, Aug 06 '19 at 02:19

Entity Framework - check whether has grandchild records

1 Answers1

Linked