How to get hierarchical data using Linq-to-entity?

Question

I want to get the data and display it in sorted (child below parent).
Data items defined as follows: ID | Title | Parent id

What I am doing is taking all the elements first and then sorting.
Is there a better way to do this 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:

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

Answer 1

I am afraid that you will have to rewrite LINQ results in your code. Depending on the size and structure of the table, you may need to pull the entire table into memory (as you think, you do this), and there goes the hierarchy. If you have a very large table, you can make repeated trips to the database.

Read this great article for more information: Storing hierarchical data in a database

Your code already performs anti-aliasing in memory. I would suggest using Skip(pSize * pIndex).Take(pSize) on your list2 object (which contains flattened) data. Remember that this can lead to page breaks in the hierarchy.

Answer 2

I urge you to use lazy loading methods, in which case you do not need to load every thing into your object tree until you need it:

 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 this, and in this case you do not need to print all the lines into memory to process them.