C # Problems and workarounds for using LINQ recursive query database

User table is usually we write "XX Management system" project must be used, in some cases the classification of people belong to the tree structure, that is, in addition to the highest level and the lowest layer, the middle layer has a relative parent and son, when designing the database, we usually add a parent_id Such a field. In this way, we can query the current user's user_id to find out what his direct subordinates are, or through parent_id to find out who his direct boss is.

But when we want to find out all of their subordinates through user_id , it is not possible to use a simple SQL. If the project is under. Net Framework3.5, which is not LINQ, it usually writes a function in the database and then calls the function directly at the time of writing SQL to get a filtered result set. What if it's LINQ? I think it is to write a static method, just happen to encounter a problem like this, is just contact with LINQ, so try to write a bit.

But whether it's writing a function in a database or writing a static method in a project, I think it's all going to be a recursive implementation.

My idea is to pass in the current user_id and return the result set of all its subordinates. Finally, the result set is queried based on the condition. However, in the process of writing this method still encountered a few problems:

1, how to query out the result set Var type, converted to list<t> type

That's how I wrote it at first.

/ * BIG mistake * * where..... select ...; ..... return list. Tolist<t> ();

Now look at me, I am quite creative, ha, can actually write such a thing.

Don't say list first. Tolist<t> (); The red line itself, why should I go to ToList at the last return? The reason is that I know that Var can be used with the "+ =" operator. In this way, the return value of the deep layer is directly combined with one another, which makes it easier to use.

Ah ~ really wrong, first of all, according to my thinking, the value returned by the deep layer is already the tolist type, so it is no longer possible to use the + = operator. Second, well, I admit, I still don't quite understand what Var is. In fact, after the program runs, the list will have a clear type, is the system to automatically determine the. var just makes it easier for us to program, a bit like the app honey (forget where it came from), which means that the system should be able to recognize that the list is a collection of objects of type T, and I'm writing a little superfluous.

Positive solution:

where....). ToList (); return list;

In this way, the list becomes the type of list<t> I want because the return value of the function is the type, so that's exactly what I want.

2, prompt error:Collection was modified, enumeration operation may not execute.

The reason for this error is that the data set of collection (temp) is add/remove in the case of a foreach traversal. In this way, it is possible to change this collection when it is not traversed to the last moment, and the error is followed. The solution has to do with a for instead of foreach, but I still think that foreach is better, so I created a copy of the result set for collection, then one for the traversal, one for the add/remove operation, and, of course, for the latter.

Note When you create a copy, you must be new to the object, not directly after it is declared, or it is not written.

//Error New List<t> (list); //Correct

The final complete code is:

 Contextdata CTDT = new  contextdata (); public  static  List<db_userinfo> Findallchildren (int  parentid) {var list = (from C 
   
    in  ctdt.db_userinfo 
    where  c.parent_id = = ParentID Select C).            ToList ();            list<db_userinfo> tmplist = 
    new  list<db_userinfo> (List); 
    foreach             (Db_userinfo single 
    in  temp)                {list<db_userinfo> Tmpchildren = Findallchildren (single.user_id); 
    if                 (Tmpchildren.count! = 0) {list.                AddRange (Tmpchildren);        }} 
    return  list; }
   

In this way, the return value of this method can be used as the underlying data set in the conditional query directly in the background code. cs file of the page. Such as

 in Findallchildren (userid)  where..... select ...;

There should be a better way, I hope the more understanding of the friends can teach more, welcome to cover the building! ~