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

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

     GetCategory开发者_运维知识库List(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 ...

---

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