What is the best way to write this linq query?

Question

SQL :

SELECT node.CategoryId, node.CategoryName, node.Description, node.Lft, node.Rgt, node.ShowOnMenu, (COUNT(parent.CategoryName) - 1) AS Level, (CASE WHEN node.Lft = node.Rgt - 1 THEN 'TRUE' ELSE 'FALSE' END) AS Leaf FROM Article_Category AS node, Article_Category AS parent WHERE node.Lft BETWEEN parent.Lft AND parent.Rgt GROUP BY node.CategoryId,node.CategoryName,node.Description,node.Lft,node.Rgt,node.ShowOnMenu ORDER BY node.Lft 

My linq expression :

  var list = (from node in DbContext.Categories from parent in DbContext.Categories where node.Lft >= parent.Lft && node.Lft <= parent.Rgt select new { node.CategoryId, node.CategoryName, node.Description, node.Lft, node.Rgt, node.ShowOnMenu, ParentName = parent.CategoryName, } into x group x by new { x.CategoryId, x.CategoryName, x.Description, x.Lft, x.Rgt, x.ShowOnMenu, } into g orderby g.Key.Lft select new { CategoryId = g.Key.CategoryId, CategoryName = g.Key.CategoryName, Description = g.Key.Description, Lft = g.Key.Lft, Rgt = g.Key.Rgt, ShowOnMenu = g.Key.ShowOnMenu, Level = g.Count() - 1, IsLeaf = g.Key.Lft == g.Key.Rgt - 1 }).ToList(); 

My question is:

• The linq expression is too long, there are two select new expressions, I wonder how to make it shorter?

• What is the appropriate extension method for linq query? How can I express "from ... from ... where ..." using the extension method?

Answer 1

First select new .. into x I don’t understand why you need to, try deleting it and write group node by new...

"from...from" written as a lambda expression as follows:

 Categories.SelectMany(n => Categories, (n, p) => new { Node = n, Parent = p });