Linq Query for a particular scenario-CodePudding

 Class Demo
 {
  public int Id{get;set;};
  public string Name{get;set;}
  public int Parent{get;set;};
  public IList<Demo> children{get;set;}
 }

Now my code returns List demos. The data is three level nested. I mean List can contain again Children1 based on each upper level Id matching nested level Parent. It goes another level down where the upper Children1 each ID matches nested Parent and another List of Demos. How can I write an optimized query from it. *

List Demos will have huge data then each Demo Id matches with Parent if matches List of Children (suppose children1) is fetched and then based on the each child in Children 1 which matches Id with Parent another List of Children is filled.

CodePudding user response：

You can achieve your desired result using recursion. Basically having a function that executes the query on 1 level of IList. It will then call itself (the same function as is currently executing) until there are no children to anymore.

Since there is no context beyond the question on how to query on a potential infinite amount of children I will only dig down on the recursion question and not the optimized part. How to optimize an infinite recursive query is an altogether different question.

It will most likely look something like this:

IList<Demo> QueryDemo(Demo current, int parentId)
{
    if (current.Id != parentId)
        return new List<Demo>();

    List<Demo> acceptedChildren = new();

    foreach (var child in current.children)
    {
        acceptedChildren.AddRange(QueryDemo(child, current.Id));
    }

    return acceptedChildren;
}

CodePudding user response：

Since you are saying Linq To SQL, I assume this has a backing table with a self join which is already an optimized way of defining such structures. If you generate your Linq To SQL model from such a table then your model would already have navigational properties for 'parent' and 'children'. SQL Server sample database's Employees table is a good example for this. Based on its model you would have something like:

var e = Employees.Select(em => new {
    em.EmployeeID,
    em.FirstName,
    em.LastName,
    em.ReportsToChildren
});

and that would generate this SQL:

SELECT [t0].[EmployeeID], [t0].[FirstName], [t0].[LastName], [t1].[EmployeeID] AS [EmployeeID2], [t1].[LastName] AS [LastName2], [t1].[FirstName] AS [FirstName2], [t1].[Title], [t1].[TitleOfCourtesy], [t1].[BirthDate], [t1].[HireDate], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[HomePhone], [t1].[Extension], [t1].[Photo], [t1].[Notes], [t1].[ReportsTo], [t1].[PhotoPath], (
    SELECT COUNT(*)
    FROM [Employees] AS [t2]
    WHERE [t2].[ReportsTo] = [t0].[EmployeeID]
    ) AS [value]
FROM [Employees] AS [t0]
LEFT OUTER JOIN [Employees] AS [t1] ON [t1].[ReportsTo] = [t0].[EmployeeID]
ORDER BY [t0].[EmployeeID], [t1].[EmployeeID]

Using ToList() on this is a slight detail for enumeration.

Note: Using a utility like LinqPad, you can test this quickly and easily.