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
– T I, Commented May 30, 2013 at 9: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
– MrFox, Commented May 30, 2013 at 9:37

Jens Kloster · Accepted Answer · 2013-05-30 11:25:26Z

6

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 }

edited May 30, 2013 at 11:25

Jens Kloster

11.3k5 gold badges44 silver badges55 bronze badges

answered May 30, 2013 at 10:21

scdove

5865 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jimmy Over a year ago

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"

T I Over a year ago

@JimBarton if you change the first bit of where to be where node.LeftID >= parent.LeftID then you should get the root node

scdove Over a year ago

I've amended the answer to add ordering and the root node. You can swap companyGroup for db.CompanyGroups if you need to.

h.meknassi · Accepted Answer · 2013-05-30 09:45:45Z

0

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 } 
       } 
};

answered May 30, 2013 at 9:45

h.meknassi

1878 bronze badges

Collectives™ on Stack Overflow

Converting an SQL query in to LINQ C#

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related