Converting an SQL query in to LINQ C#

Question

I've an SQL Query:

SELECT 
      node.GroupName
    , depth = COUNT(parent.GroupName) - 1
FROM CompanyGroup node
JOIN CompanyGroup parent ON node.LeftID BETWEEN parent.LeftID AND parent.RightID
GROUP BY node.GroupName, node.LeftID
ORDER BY node.LeftID;

I've tried converting it to LINQ myself but I'm unfamiliar with the language, after some research I've tried using Linqer but it won't convert the functions 'BETWEEN' or 'COUNT'.

The closest I've gotten so far is:

        var groupModel =
            from node in db.CompanyGroups
            join parent in db.CompanyGroups.Where(node.LeftID > parent.LeftID && node.LeftID < parent.RightID)
            orderby node.LeftID
            select node.GroupName;

which doesn't work and wouldn't return the 'depth' even if it did, help please!

Edit:

The query is used to return the depth of nodes in a nested set in an order so that I can create a representation of a hierarchy; I'm following this tutorial: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ on the chapter 'Finding the Depth of the Nodes'

you should use `>=` and `<=` in the linq join because `BETWEEN` is inclusive — T I, May 30 '13 at 09:28
Why would you need to know the depth upfront? Can't you return a nested structure and let the code drawing the interface keep a depth counter? — MrFox, May 30 '13 at 09:37

score 6 · Accepted Answer · edited May 30 '13 at 11:25

This should get you close.

Given

        var companyGroups = new List<CompanyGroup>
        {
            new CompanyGroup {GroupName = "ELECTRONICS",            LeftID = 1 , RightID =20 },
            new CompanyGroup {GroupName = "TELEVISIONS",            LeftID = 2 , RightID =9  },
            new CompanyGroup {GroupName = "TUBE",                   LeftID = 3 , RightID =4  },
            new CompanyGroup {GroupName = "LCD",                    LeftID = 5 , RightID =6  },
            new CompanyGroup {GroupName = "PLASMA              ",   LeftID = 7 , RightID =8  },
            new CompanyGroup {GroupName = "PORTABLE ELECTRONICS",   LeftID =10 , RightID =19 },
            new CompanyGroup {GroupName = "MP3 PLAYERS         ",   LeftID =11 , RightID =14 },
            new CompanyGroup {GroupName = "FLASH               ",   LeftID =12 , RightID =13 },
            new CompanyGroup {GroupName = "CD PLAYERS          ",   LeftID =15 , RightID =16 },
            new CompanyGroup {GroupName = "2 WAY RADIOS        ",   LeftID =17 , RightID =18   },
        };

then this

        var results = from node in companyGroups
                      from parent in companyGroups
                      where node.LeftID >= parent.LeftID && node.RightID <= parent.RightID
                      group node by node.GroupName into g
                      orderby g.First().LeftID
                      select new { GroupName = g.Key, Depth = g.Count() - 1 };

yields

{ GroupName = ELECTRONICS, Depth = 0 }

{ GroupName = TELEVISIONS, Depth = 1 }

{ GroupName = TUBE, Depth = 2 }

{ GroupName = LCD, Depth = 2 }

{ GroupName = PLASMA              , Depth = 2 }

{ GroupName = PORTABLE ELECTRONICS, Depth = 1 }

{ GroupName = MP3 PLAYERS         , Depth = 2 }

{ GroupName = FLASH               , Depth = 3 }

{ GroupName = CD PLAYERS          , Depth = 2 }

{ GroupName = 2 WAY RADIOS        , Depth = 2 }

nice, thanks, returns everything except the root node with the correct depth but in alphabetical order rather than in order of 'LeftID'. Also it should be "in db.CompanyGroups" — Jimmy, May 30 '13 at 10:33
@JimBarton if you change the first bit of where to be `where node.LeftID >= parent.LeftID` then you should get the root node — T I, May 30 '13 at 11:04
I've amended the answer to add ordering and the root node. You can swap companyGroup for db.CompanyGroups if you need to. — scdove, May 30 '13 at 11:17

score 0 · Answer 2 · answered May 30 '13 at 09:45

you must add something like :

group CompanyGroup by node.GroupName into g
select new
{
    node= g.GroupName,
    left = select 
       new 
       { 

          left = 
          from CompanyGroup  in g 
          group CompanyGroup  by CompanyGroup. into mg 
          select new { left=mg.LeftID } 
       } 
};

Converting an SQL query in to LINQ C#

2 Answers2