This is my model:

public class CaseType {
    public int CaseTypeID { get; set; }
    public int? ParentID { get; set; }
    public string CaseTypeText { get; set; }
    public int? OrderBy { get; set; }

I'm trying to create a hierarchical tree structure from this model with the following values:

CaseTypeID  ParentID   CaseTypeText   OrderBy
    1          Null       CT A         1
    2          Null       CT B         2
    3          Null       CT C         3
    4             1       CT A 1       1
    5             1       CT A 2       2
    6             3       CT C 1       1
    7             3       CT C 2       2

Which should output as:

   CT A 1
   CT A 2
   CT C 1
   CT C 2

This link helped me create T-SQL which nearly did the job. It just wouldn't let me use the OrderBy column. It used the order of the records in the table. I decided I'm probably best doing it with EF and LINQ anyway but hopefully that should let you see what I'm trying to achieve.

This is the Index method from my controller:

public async Task<IActionResult> Index() {
    var TopLevelCaseTypes = await _context.CaseTypes.Where(ps => ps.ParentID.HasValue == false).OrderBy(x => x.OrderBy).ToListAsync();
    List<CaseType> outputView =  new List<CaseType>();
    foreach (CaseType IndividualTopLevelCaseType in TopLevelCaseTypes) {
        var AllSubCaseTypes = await _context.CaseTypes.Where(ps => ps.ParentID == IndividualTopLevelCaseType.CaseTypeID).OrderBy(x => x.OrderBy).ToListAsync();
        foreach (CaseType IndividualSubCaseTypes in AllSubCaseTypes) {
    return View(outputView);

This achieves what I need it to but is inefficient as it gets the top level CaseTypes (where ParentID is null) then for each one, see if there are any subCaseTypes, adding each CaseType and subCaseType as it arrives to an outputView which is pushed to the View. Is there a better way of writing this in my controller making one DB call but letting me use the OrderBy field to pick the order?

  • \$\begingroup\$ Since you load the whole hierarchy: stackoverflow.com/a/41837737/861716. Then build the ordered tree in memory. \$\endgroup\$ – Gert Arnold Nov 3 '17 at 8:18
  • \$\begingroup\$ @GertArnold That method retrieves all the rows from the table, basically SELECT * FROM table, in row order. Do you mean take all that then iterate through the resultset? \$\endgroup\$ – Glyn Nov 3 '17 at 13:08
  • \$\begingroup\$ Yes, that's the idea. EF has established all relationships in the object graph by relationship fixup. \$\endgroup\$ – Gert Arnold Nov 3 '17 at 13:10
  • \$\begingroup\$ @GertArnold I imagined an EF/Linq equivalent to the T-SQL involving the EF version of UNIONs, JOINs, etc. Is that not how EF is supposed to work? Is it supposed to be used like "get everything" as one operation, "do something with what you get" as a second step? \$\endgroup\$ – Glyn Nov 3 '17 at 16:12
  • \$\begingroup\$ I can use the code that was autogenerated with the controller return View(await _context.CaseTypes.ToListAsync()); to retrieve the same results as using the method used in the example var hierarchy = _context.CaseTypes.Include(e => e.Children).ToList(); return View(hierarchy); and don't have to have public CaseType Parent { get; set; } public ICollection<CaseType> Children { get; set; } in the model. \$\endgroup\$ – Glyn Nov 3 '17 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.