C # Problems Related to database query recursively using Linq and Solutions

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 addParent_idSuch fields. In this way, we can useUser_idFind out which direct subordinates he/she has, orParent_idFind out who his direct supervisor is.

But when we want to passUser_idWhen you query all of its subordinates, you 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 currentUser_idThen return the result set of all its subordinates. Finally, query the result set based on the condition. However, there are several problems encountered during writing this method:

1. How to convert the queried result set var type to List <T> type

I wrote it like this at first.

/* Very wrong */var list = from ....... where .... select ...;..... return list. toList <T> ();

Now let's see how creative I 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 type I want, because the return value of the function is of this type, so it is exactly what I want.

2. An error is prompted: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); // correct

The complete code is as follows:

        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, in the background code. cs file of the page, 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 !~