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

问题描述

这是我的第一个问题，抱歉我的语言很弱.

this is my first question and sorry about my weak language.

我有一张这样的桌子；

 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
 }

这样的菜单有很多行；

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.

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

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屋！