在LINQ中可以进行递归查询 [英] Is recursive query possible in LINQ
问题描述
我有一张这样的表格，
public class Menu
{
[Key]
public int ID {get; set;}
public int ParentID { get; set;}
public string MenuName {get; set;}
public int OrderNo {get; set;}
public bool isDisplayInMenu {get; set;} // Menu或访问权限
}
并且有很多关于这样菜单的行;
ID ParentID MenuName Order
--- --------- ---------- --- ------
1 0 Main.1 1>>如果ParentID == 0是根
2 1 Sub.1.1 1
3 2 Sub.1.2 2
4 0 Main.2 2
5 4 Sub.2.1 1
6 4 Sub.2.2 2
我有一个第二个类准备菜单树;
public class MyMenu：Menu
{
public List< MyMenu> Childs {get; set;}
}
我需要一个linq查询才能得到结果像这样;
var result =（... linq ..）ToList< MyMenu>（）;
我正在使用递归函数来获取小孩，但是花费太多时间来获取结果。 / p>
如何在一个查询中写一个句子来获取所有菜单树？
更新：
我想将主菜单存储在表中。而这个表将使用用户的访问权限控制。某些行会显示在菜单中，有些行会显示在菜单中，有些行将仅用于获取访问权限。
在这种情况下，我需要多次获取表格树。表格树将被创建为过滤的用户权限。获取树，存储在会话中。但是许多会话意味着很多RAM。如果有什么快速的方式从sql中获取菜单树，那么我将不会存储在会话中。
如果你需要走整个树，你应该使用一个存储过程。实体框架特别不适合递归关系。您将需要为每个级别发出N + 1个查询，或者热切地加载一组定义的级别。例如， .Include（Childs.Childs.Childs），将加载三个级别。但是，这将会产生一个怪异的查询，您仍然需要为您在开始时不包括的任何其他级别发出N + 1个查询。
在SQL中，您可以使用 WITH 递归地移动表，并且它将比任何实体框架能够更快地执行。但是，您的结果将被平坦化，而不是从实体框架中获取的对象图。例如：
DECLARE @Pad INT =（
SELECT MAX（[Length]）
FROM
SELECT LEN（[Order]）AS [Length] FROM [dbo]。[菜单]
）x
）;
WITH Tree（[Id]，[ParentId]，[Name]，[Hierarchy]）AS
（
SELECT
[ID]，
[ParentID]，
[MenuName]，
REPLICATE（'0'，@Pad - LEN（[Order]））+ CAST（[订单] AS NVARCHAR（MAX））
FROM [ dbo]。[菜单]
WHERE [ParentID] = 0 - root
UNION ALL
SELECT
儿童[ID]，
儿童[ParentID]
孩子[MenuName]，
父母[层次结构] +'。'+ REPLICATE（'0'，@Pad - LEN（儿童[订单]））+ CAST（儿童。 AS NVARCHAR（MAX））AS [层次]
FROM [dbo]。[菜单]儿童
INNER JOIN树AS父
ON父母[ID] =儿童[ParentID]
SELECT
[ID]，
[ParentID]，
[MenuName]
FROM Tree
ORDER BY [层次结构]
看起来要复杂得多。为了确保菜单中的项目通过父母的父母和正确地排序，我们需要创建一个按顺序排序的分层表示。我在这里通过以 1.1.1 的形式创建一个字符串，其中基本上每个项的顺序都附加到父层次结构字符串的末尾。我还使用 REPLICATE 来左键单击每个级别的顺序，因此您没有与数字字符串排序相关的问题，例如 10 来自 2 ，因为它以 1 开头。 @Pad 声明只是根据表中最高的订单号码获得了我需要的最大长度。例如，如果最大订单类似于 123 ，则 @Pad 的值将为3，因此小于 123 的订单仍将是三个字符（即 001 ）。
一旦你超越了所有这一切，SQL的其余部分是非常简单的。您只需选择所有根项目，然后通过走树将所有孩子与所有孩子联合起来。这个并加入每一个新的层面。最后，您从这个树中选择您需要的信息，按照我们创建的层次排序字符串排序。
至少对于我的树，这个查询是可以接受的，但是可以如果复杂度有所增加，或者有很多菜单项要处理，可能会比您想要的慢。即使使用此查询，也可以进行树的某种缓存。就个人来说，对于像网站导航的东西，我建议使用一个小孩操作与 OutputCache 相结合。您在布局中调用子动作，导航应该出现，并且它将运行操作以获取菜单或从缓存中检索已创建的HTML（如果存在）。如果菜单是针对个人用户的，那么只需确保您的自定义变化，并考虑用户的id或自定义字符串中的某些内容。您也可以只是内存缓存查询本身的结果，但是您也可以减少生成HTML的费用。但是，应该避免将其存储在会话中。
this is my first question and sorry about my weak language.
I've got a table like this model;
public class Menu
{
[Key]
public int ID {get;set;}
public int ParentID {get;set;}
public string MenuName {get;set;}
public int OrderNo {get;set;}
public bool isDisplayInMenu {get;set;} // Menu or just for Access Authority
}
and there are many rows about menu like this;
ID ParentID MenuName Order
--- --------- ------------- ------
1 0 Main.1 1 >> if ParentID==0 is Root
2 1 Sub.1.1 1
3 2 Sub.1.2 2
4 0 Main.2 2
5 4 Sub.2.1 1
6 4 Sub.2.2 2
I have got a second class to prepare menu tree;
public class MyMenu:Menu
{
public List<MyMenu> Childs { get;set;}
}
I need a linq query to get the result like this;
var result = (...linq..).ToList<MyMenu>();
I am using a recursive function to get childs but this take too much time for to get results.
How can I write a sentence to get all menu tree in one query?
UPDATE:
I want to store main menu in a table. And this table will use on access authority control for users. Some rows will display inside the menu, some ones will use only to get access authority.
In this situation, I need many times to get the table tree. The table tree will be created as the filtered user authorities. When get the tree, stored in session. but many sessions means much RAM. If is there any fast way to get menu tree from the sql when I need then I will not store in the session.
If you need to walk the entire tree, you should use a stored procedure. Entity Framework is particularly ill-suited for recursive relationships. You'll either need to issue N+1 queries for each level, or eagerly load a defined set of levels. For example, .Include("Childs.Childs.Childs"), would load three levels. However, this is going to create a monstrous query, and you'll still need to issue N+1 queries for any additional level you don't include at the start.
In SQL, you can use WITH to recursively walk the table, and it will be much quicker than anything Entity Framework can do. However, your result will be flattened, rather than the object graph you would get back from Entity Framework. For example:
DECLARE @Pad INT = (
SELECT MAX([Length])
FROM (
SELECT LEN([Order]) AS [Length] FROM [dbo].[Menus]
) x
);
WITH Tree ([Id], [ParentId], [Name], [Hierarchy]) AS
(
SELECT
[ID],
[ParentID],
[MenuName],
REPLICATE('0', @Pad - LEN([Order])) + CAST([Order] AS NVARCHAR(MAX))
FROM [dbo].[Menus]
WHERE [ParentID] = 0 -- root
UNION ALL
SELECT
Children.[ID],
Children.[ParentID],
Children.[MenuName],
Parent.[Hierarchy] + '.' + REPLICATE('0', @Pad - LEN(Children.[Order])) + CAST(Children.[Order] AS NVARCHAR(MAX)) AS [Hierarchy]
FROM [dbo].[Menus] Children
INNER JOIN Tree AS Parent
ON Parent.[ID] = Children.[ParentID]
)
SELECT
[ID],
[ParentID],
[MenuName]
FROM Tree
ORDER BY [Hierarchy]
That looks much more complicated than it is. In order to ensure that the items in the menu are ordered properly by parent and their position within that parent's tree, we need to create a hierarchical representation of the order to order by. I'm doing that here by creating a string in the form of 1.1.1, where essentially each item's order is appended to the end of the parent's hierarchy string. I'm also using REPLICATE to left-pad the order for each level, so you don't have issues common with string ordering of numbers, where something like 10 comes before 2, because it starts with 1. The @Pad declaration just gets the max length I need to pad based on the highest order number in the table. For example, if the max order was something like 123, then the value of @Pad would be 3, so that orders less than 123 would still be three characters (i.e. 001).
Once you get past all that, the rest of the SQL is pretty straight-forward. You simply select all the root items and then union all with all their child by walking the tree. This and joining in each new level. Finally, you select from this tree the information you need, ordered by the hierarchy ordering string we created.
At least for my trees, this query is acceptably quick, but could be somewhat slower than you might like if the complexity scales or there's a ton of menu items to deal with. It's not a bad idea to do some sort of caching of the tree, even with using this query. Personally, for something like a site nav, I'd recommend using a child action combined with OutputCache. You call the child action in your layout where the nav should appear, and it will either run the action to get the menu or retrieve the already created HTML from cache if it exists. If the menu is specific to individual users, then just make sure you vary by custom, and factor in the user's id or something in your custom string. You could also just memory cache the result of the query itself, but you might as well reduce the cost of generating the HTML, too, while your at it. However, storing it in the session should be avoided.
这篇关于在LINQ中可以进行递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
