将SQL查询转换为LINQ C#

我有一个SQL查询：SELECT node.GroupName, depth = COUNT(parent.GroupName) - 1FROM CompanyGroup nodeJOIN CompanyGroup parent ON node.LeftID BETWEEN parent.LeftID AND parent.RightIDGROUP BY node.GroupN...

我有一个SQL查询：

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;

我已经尝试过将它转换为LINQ,但是我对语言不熟悉,经过一些研究后我尝试使用Linqer,但它不会转换函数’BETWEEN’或’COUNT’.

我到目前为止最接近的是：

        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;

哪个不起作用,即使它确实也不会返回’深度’,请帮忙！

编辑：

该查询用于按顺序返回嵌套集中的节点深度,以便我可以创建层次结构的表示;我正在按照本教程：“查找节点的深度”一章中的http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

解决方法:

这应该让你接近.

特定

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

那么这个

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

产量

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