How to retrieve hierarchical data using Linq-to-entities?

Question

I want to retrieve the data and and display it in sorted (child below it's parent).
The data items defined like this: ID | Title | Parent-ID

What I do is first retrieving all items and then sorting.
Is there a better way to do that with linq?

protected void Page_Load(object sender, EventArgs e)
{          
    List<Category> list2 = new List<Category>();
    ContModel modeltx = new ContModel();

    var ret = modeltx.Categories.ToList();

     GetCategoryList(0, 0, ret, list2);
     string str="";

     foreach (Category cat in list2)
     {
          str=str+cat.Title+"\n";
         TextBox1.Text = str;
     }       
}


   private void GetCategoryList(int iCurID, int iDepth, List<Category> li, List<Category> newList)
    {
        Category tmp;
        string strOffset = "";

        foreach (Category cat in li)
        {
            if ((cat.ParentId) == iCurID)
            {
                for (int i = 1; i <= iDepth; i++)
                    strOffset = strOffset + "-";  

                strOffset = strOffset + cat.Title;

                tmp = cat;
                tmp.Title = strOffset;
                newList.Add(tmp);

                strOffset = "";
                GetCategoryList(cat.CategoryID, iDepth + 1, li, newList);
            }
        }
    }

Update:

How to be if the size of data is huge and i want to use paging?
I can't Page (.Skip(PageSize * PageIndex).Take(PageSize)) before sorting ...

Possible duplicate of http://stackoverflow.com/questions/202912/hierarchical-data-in-linq-options-and-performance — too, Jan 06 '11 at 20:18

score 1 · Answer 1 · answered Jan 06 '11 at 15:47

I encourage you to use lazy loading techniques and this case you don't have to load every thing in your tree of object until you need them :

class item 
{
 int id;
 string itemName;
 item partent;
 List<item> _childs;
 public List<item> Childs
{
  get 
{
  if( _child == null)
     _child = getitembyparentid(this.id);
  return _child;
}
}
}

something like that and in this case you don't have to bring all rows into memory to handle them .

I asked to get all items with all children – jullin Jan 06 '11 at 15:59 — jullin, Jan 06 '11 at 15:59

C. Ross · Accepted Answer · 2011-01-06T17:42:59.067

0

I'm afraid you'll have to recurse over the LINQ results in your code. Depending on table size and structure you may want to pull down the whole table into memory (as it seems you are doing) and do the hierarchy walk there. If you have a very large table, you may want to make repeated trips to the DB.

For more information read this great article: Storing Hierarchical Data in a Database

Your code is already doing the flattening in memory. I would suggest using Skip(pSize * pIndex).Take(pSize) on your list2 object (which contains the flattened) data. Be aware that this may lead to page breaks deep in the hierarchy.

edited Jan 06 '11 at 17:42

answered Jan 06 '11 at 15:35

C. Ross

31,137
42
147
238

Let's say my database is very big and I deride to pull X items, and I do '.OrderBy(p => p.Title).Take(100).ToList();', before taking 100 items, doesn't it pulls down the whole table? – jullin Jan 06 '11 at 16:26
1

@jullin No, that takes only the first 100 titles. To pull the whole table you would do `model.OrderBy(p => p.Title).ToList();`. – C. Ross Jan 06 '11 at 16:36
@C. Ross, please refer my update: how can I deal with paging? (I must first sort and only then page) using linq. – jullin Jan 06 '11 at 16:50
@jullin, You'll need to do paging on the `list2` object (the flattened list). You can page over the database because you have to pull it all out to build your hierarchy, and then your flattened list. See my update. – C. Ross Jan 06 '11 at 17:43
can you please explain what is "page breaks deep in the hierarchy"? – jullin Jan 06 '11 at 20:17
@jullin You may be three levels deep at the end of page1, and you would start out page2 also three levels deep. There is no guarantee of getting a top level item at the top of each page. – C. Ross Jan 06 '11 at 21:50

How to retrieve hierarchical data using Linq-to-entities?

2 Answers2