Populating a TreeView from a database model in Razor Pages

Question

I have a Razor pages project that is trying to populate a Kendo TreeView (or any other TreeView) from a Database created with a Data Model.

These are the relevant fields of the model:

namespace FliveRetry.Models
{
    public class Org
    {
        public int ID { get; set; }
        public string OrgName { get; set; }
        public int? ParentID { get; set; }
        public bool? HasChildren { get; set; }
    }
}

This is the view call:

@(Html.Kendo().DropDownTree()
    .Name("selectedOrgs")
    .DataTextField("Org")
    .DataValueField("OrgID")
    .Checkboxes(true)
    .DataSource(d => d
        .Read(read =>
            read.Url("/Apps/Edit?handler=Read")
        )
    )
)

Hardcoding the values into a list like this below in OnGetRead works fine:

public IActionResult OnGetRead(int? id)
    {       
        var data = new List<Org>()
        {
            new Org() { ID = 1, ParentID = null, HasChildren = true, OrgName = "Org1" },
            new Org() { ID = 2, ParentID = null, HasChildren = false, OrgName = "Org2" },
            new Org() { ID = 3, ParentID = 1, HasChildren = true, OrgName = "Org3" },
            new Org() { ID = 4, ParentID = 3, HasChildren = false, OrgName = "Org4" }
        };

        var result = data.Where(x => id.HasValue ? x.ParentID == id : x.ParentID == null)
            .Select(item => new {
                id = item.ID,
                Org = item.OrgName,
                hasChildren = item.HasChildren
            });
            return new JsonResult(result);
        }
    }

Hard Coded Values Image

I am trying to Loop through the Org collection to populate the treeview with the Orgs from the Database.

Putting the for loop inside the List declaration creates errors everywhere, OnGetRead itself says not all code paths return a value:

public IActionResult OnGetRead(int? id)
        {
            var blnChildren = false;
            var data = new List<Org>()
            {       
                foreach (var item in _context.Org)
                {
                    blnChildren = false;
                    if (item.ParentID.Equals(null))
                        {
                        blnChildren = true;
                        }
                    new Org() { ID = item.ID, ParentID = item.ParentID, HasChildren = blnChildren, OrgName = item.OrgName };
                }       
            };

            var result = data.Where(x => id.HasValue ? x.ParentID == id : x.ParentID == null)
                .Select(item => new {
                    id = item.ID,

                    Org = item.OrgName,
                    hasChildren = item.HasChildren
                });

            return new JsonResult(result);
        }

If I put the for loop outside the List Declaration, the loop populates correctly but the result is empty, I'm guessing because the item declaration is outside the list?

public IActionResult OnGetRead(int? id)
        {
            var blnChildren = false;
            var data = new List<Org>()
            {       

            };
            foreach (var item in _context.Org)
            {
                blnChildren = false;
                if (item.ParentID.Equals(null))
                    {
                    blnChildren = true;
                    }
                new Org() { ID = item.ID, ParentID = item.ParentID, HasChildren = blnChildren, OrgName = item.OrgName };
            }

            var result = data.Where(x => id.HasValue ? x.ParentID == id : x.ParentID == null)
                .Select(item => new {
                    id = item.ID,
                    Org = item.OrgName,
                    hasChildren = item.HasChildren
                });

            return new JsonResult(result);
        }

Not sure if my problem is C# related or model related but any help would be appreciated.

Any other working example of a self referencing treeview working from a database model in a razor project rather than an MVC project would also help if I'm on the wrong track.

I have another example of the same issue using Kendo.Mvc.UI.TreeViewItemModel data that I can't get to work outside of hard coding either, I just need any version that works really.

Thanks heaps

Answer 1

A lot of stuff that you need to correct.

1. In forach loop you go trough each Org table item in DB creating query to Database each time. Thats a waste of time and resources. You should first query all the items you need from DB and then process them. If you need all the table entries you could just materialize them.

    var orgs = _context.Org.ToArray();
    foreach(var item in orgs)
    {
       //code here

2. You didn't add items into your data list inside foreach loop. You can do it like so

    var tmpOrg = new Org() { code code code} ;
    data.Add(tmpOrg);

3. This whole thing can be simplified into one expression. You don't need to materialize the whole table and you don't need that foreach loop. You can change the result expression to use _context.Org intead of data. In the Select clause on hasChildren you can just change this line

    hasChildren = item.ParentID == null ? true : false;

EDIT

What you should be left with is

public IActionResult OnGetRead(int? id)
{       
    var result = _context.Org.Where(x => id.HasValue ? x.ParentID == id : x.ParentID == null)
                             .Select(item => new 
                              {
                                  OrgID = item.ID,
                                  Org = item.OrgName,
                                  HasChildren = item.ParentID == null ? true : false
                              });

    return new JsonResult(result);       
}