C # Problems and Solutions for Recursive database query using Linq

The User table is usually used when we write the "XX management system" project. In some cases, the classification of personnel belongs to the tree structure, except for the highest and lowest layers, the middle layer has relative Parent and Child. When designing a database, we usually add a parent_id field. In this way, we can use the user_id of the current user to find out which direct subordinates are, or use parent_id to find out who his direct supervisor is. However, when we want to use user_id to query all of its subordinates, we cannot use a simple SQL statement. If the project is. net Framework3.5 or lower, that is, when there is no Linq, a function is usually written in the database, and then a filtered result set can be obtained by directly calling the function when writing SQL. What if it is Linq? I thought I was about to write a static method. I just encountered a problem like this. I just got in touch with Linq, so I tried to write it. However, whether it is writing a function in the database or writing a static method in the project, I think it should be implemented using recursion. My idea is to pass in the current user_id and then return the result set of all its subordinates. Finally, query the result set based on the condition. However, when writing this method, you still encounter several problems: 1. How to set the queried result set var type, converting to List <T> type I wrote in this way at the beginning/* very wrong */var list = from ....... where .... select ...;..... return list. toList <T> (); now, I am very creative and can write such a thing. First, let alone list. ToList <T> (); itself draws a red line. Why do I need to go to ToList () at the final return? The reason is that I know that var can use the "+ =" operator. In this way, the returned values of a deeper layer are directly added together for better use. Ah ~ This is really a big mistake. First of all, according to my ideas, the value returned by a deeper layer is already of the ToList type, so we cannot use the + = Operator. secondly, Well, I admit, I still don't know much about what var is. In fact, after the program runs, the list will have a clear type, which is automatically determined by the system. Var makes programming more convenient, a bit like program honey (forgot where it was heard). That is to say, the system should be able to recognize that list is a set of T-type objects, this is a bit superfluous to me. Positive Solution: var list = (from ....... where .... select ...). toList ();..... return list; in this way, the list will become the List <T> type I want, because the return value of the function is of this type, so it is exactly what I want. 2. Error: Collection was modified; enumeration operation may not execute. the cause of this error is that the Add/Remove operation is performed on the Collection (temp here) dataset during foreach traversal. In this way, the Collection may be modified before it is traversed to the end, and an error is reported. The solution is to replace foreach with for, but I still think foreach is better, so I created a copy of The result set of Collection, and then a copy of The result set for traversal and an Add/Remove operation, of course, the latter is returned. Note that a new object must be created when the copy is created, instead of assigning values after the declaration. Otherwise, it is the same as not written. List <T> tmpList = list; // Error List <T> tmpList = new List <T> (list); // The correct 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 = fi Ndallchildren (single. user_id); if (tmpChildren. Count! = 0) {list. addRange (tmpChildren) ;}} return list. in the cs file, you can directly use the return value of this method as the Basic Dataset in the condition query. For example, var result = from c in findallchildren (userid) where ..... select ....; there should be better methods. I hope you can teach me more. Welcome to build a building! ~