是否可以在LINQ to Entities中进行递归查询 [英] Is recursive query possible in LINQ to Entities

问题描述

这是我的第一个问题，对我的语言较弱感到抱歉.

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

我需要一个linq查询来获得这样的结果；

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?

更新:

我想将主菜单存储在表格中.并且此表将对用户使用访问权限控制.某些行将显示在菜单内，有些行将仅用于获取访问权限.

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.

在这种情况下，我需要很多次才能获得表树.该表树将被创建为过滤后的用户权限.当获取树时，存储在会话中.但是许多会话意味着大量的RAM.如果有什么需要时从sql获取菜单树的快速方法，那么我将不存储在会话中.

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.

推荐答案

LINQ to Entities不支持递归查询.

LINQ to Entities does not support recursive queries.

但是，加载存储在数据库表中的整个树既简单又高效.早期版本的Entity Framework似乎有一些神话，所以让我们揭开它们的神秘面纱.

However, loading the whole tree stored in a database table is easy and efficient. There seem to be some myths from earlier version of Entity Framework, so let's demystify them.

您需要做的是创建适当的模型和FK关系:

All you need is to create a proper model and FK relationship:

型号:

public class Menu
{
    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; }

    public ICollection<Menu> Children { get; set; }
}

流利的配置:

modelBuilder.Entity<Menu>()
    .HasMany(e => e.Children)
    .WithOptional() // EF6
    .WithOne() // EF Core
    .HasForeignKey(e => e.ParentID);

重要的更改是，为了建立这种关系，ParentID必须为可为空，并且根项目应使用null而不是0.

The important change is that in order to setup such relationship, ParentID must be nullable, and root items should use null instead of 0.

现在，有了模型，加载整个树很简单:

Now, having the model, loading the whole tree is simple as that:

var tree = db.Menu.AsEnumerable().Where(e => e.ParentID == null).ToList();

使用AsEnumerable()我们确保执行查询时，将使用简单的非递归SELECT SQL在内存中检索整个表.然后，我们只需过滤掉根项.

With AsEnumerable() we ensure that when the query is executed, the whole table will be retrieved in memory with a simple non recursive SELECT SQL. Then we simply filter out the root items.

仅此而已.最后，我们有一个列表，其中包含根节点及其子代，大子代等.

And that's all. At the end we have a list with root nodes with their children, grand children etc. populated!

它如何工作?不需要/不使用任何懒惰，急切或显式的加载.整个魔力由DbContext跟踪和导航属性修复系统提供.

How it works? No lazy, eager or explicit loading is needed/used. The whole magic is provided by the DbContext tracking and navigation property fix up system.

这篇关于是否可以在LINQ to Entities中进行递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！