Linq Query for a particular scenario

somnath roy 0
Viewed 26
 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.

linq-to-sql performance linq c#
1 Answers

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.

Cetin Basoz0
Related