EF Core 1.0: How to query a whole tree?

c# entity-framework-core linq tree

Question

I'm using EF 7.0.0.0-rc1-final.

I have a tree structure with one-to-many relationships from GrandGrandParent to GrandParent to Parent to Child:

public class GrandGrandParent
{
    public int ID { get; set; }
    public string Name { get; set; }

    public virtual List<GrandParent> GrandParents { get; set; }

    public GrandGrandParent()
    {
        this.GrandParents = new List<GrandParent>();
    }
}

public class GrandParent
{
    public int ID { get; set; }
    public string Name { get; set; }

    public virtual GrandGrandParent GrandGrandParent { get; set; }
    public virtual List<Parent> Parents { get; set; }

    public GrandParent()
    {
        this.Parents = new List<Parent>();
    }
}

public class Parent
{
    public int ID { get; set; }
    public string Name { get; set; }

    public virtual GrandParent GrandParent { get; set; }
    public virtual List<Child> Children { get; set; }

    public Parent()
    {
        this.Children = new List<Child>();
    }
}

public class Child
{
    public int ID { get; set; }
    public string Name { get; set; }

    public virtual Parent Parent { get; set; }
}

Using EF Core 1.0 (EF 7), how can I create a LINQ query (or with sub-queries) that give me the whole tree, given a certain grand-grand-parent ID?

I can .Include() one level up or down, maybe I'm blind for the obvious? This gives me the GrandGrandParent and the list of GrandParents:

var ggparent1 = from ggp in myDbContext.GrandGrandParent
                .Include(ggp => ggp.GrandParents)
                where ggp.ID == 2
                select ggp;

I want to get the whole tree, down to the list of children. Must I resort to code a foreach() loop and manually build the tree?

1
0
3/3/2016 10:02:18 AM

Accepted Answer

I would just go with the Linq form:

private static void Test0(ApplicationDbContext myDbContext)
{
    var ggparent = myDbContext.GrandGrandParents
        .Include(ggp => ggp.GrandParents)
        .ThenInclude(gp => gp.Parents)
        .ThenInclude(p => p.Children)
        .FirstOrDefault(ggp => ggp.ID == 3);

    if (ggparent == null)
    {
        DebugPrint("GrandGrandParent not found");
        return;
    }

    DebugPrint("GrandGrandParent:");
    DebugPrint(ggparent);


    if (ggparent.GrandParents == null)
    {
        DebugPrint("GrandParents null");
        return;
    }

    foreach (var gparent in ggparent.GrandParents)
    {
        DebugPrint(gparent);
        if (gparent.Parents == null) continue;
        foreach (var parent in gparent.Parents)
        {
            DebugPrint(parent);
            if (parent.Children == null) continue;
            foreach (var child in parent.Children)
            {
                DebugPrint(child);
            }
        }
    }

    int changeCount = myDbContext.SaveChanges();
    DebugPrint(string.Format("ChangeCount={0}", changeCount));
}

Then you do not have to do the sub queries by your own. But you can add logging to see the SQL queries EF actually creates.

If you need the complete tree you do not need select. You can use select to extract a part of the result tree or to build a new (flat) object out of the hierarchy.

An example where I flatten a hierarchy with only two levels (with many-to-many) to get the Items for a grouped SelectList:

var result =
    (from c in dbContext.EventTypes
    join j in dbContext.EventType2EventTypes on c.Id equals j.ChildEventTypeId
    join p in dbContext.EventTypes on j.ParentEventTypeId equals p.Id
    where p.EventTypeLevel == EventTypeLevel.First && c.EventTypeLevel == EventTypeLevel.Second
    orderby p.SortOrder, p.Id, c.SortOrder, c.Id
    select new SelectListItem
    {
        Text = c.NameDe,
        Value = c.Id.ToString(),
        Group = GetParentEventTypeSelectListGroup(p.NameDe)
    }).AsNoTracking();
2
4/7/2016 11:09:05 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow