写这个linq查询有什么更好的方法？(What's the better way to write this linq query?)

写这个linq查询有什么更好的方法？(What's the better way to write this linq query?)

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

我的linq表达式 ：

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();

我的问题：

linq表达式太长，有两个'select new'表达式，我想知道如何缩短它？

linq查询的相应扩展方法是什么？ 如何使用扩展方法表达“从......哪里......”？

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:

The linq expression is too long, there is two 'select new' expressions, i wonder how to make it shorter?

What's the corresponding Extension Method to the linq query? How can i express the "from... from...where..." with Extension method?

最满意答案

第一个select new .. into x我不明白你为什么需要，尝试删除它，并写group node by new...

"from...from"写成这样的lambda表达式：

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

The first select new .. into x I don't see why you need, try removing it and write group node by new...

"from...from" is written as a lambda expression like this:

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