使用EntityFramework.Core从自引用表加载完整层次结构 [英] loading a full hierarchy from a self referencing table with EntityFramework.Core

问题描述

说明此问题为何不同于以下内容: EF -多个包含，渴望加载分层数据.不好的做法?

Explanation why this question is different to: EF - multiple includes to eager load hierarchical data. Bad practice?

1. 可能的重复项是基于意见的问题，无论这是否是一种不好的做法，而我的问题倾向于获得关于如何执行此操作的技术解决方案，而与它是否是一种良好的做法无关.我将这个决定权留给需要该功能的产品所有者，需求工程师，项目经理和客户.
2. 给出的答案要么解释为什么这是一种不好的做法，要么使用对我不起作用的方法(使用Include()和ThenInclude()会产生硬编码深度，而我需要一个灵活的深度).

---

在当前项目(.NET核心Web API)中，我尝试从自引用表加载层次结构.

---

In the current project (a .NET core web api) I try to load a hierarchy from a self referencing table.

经过大量搜索之后，我感到惊讶的是，这样的任务(我认为这是微不足道的)似乎并不微不足道.

After googling a lot I was surprised that such a task (which I thought would be trivial) seems not to be trivial.

好吧，我有这张桌子来构成我的层次结构:

Well, I have this table to form my hierarchy:

CREATE TABLE [dbo].[Hierarchy] (
    [Id]        INT           IDENTITY (1, 1) NOT NULL,
    [Parent_Id] INT           NULL,
    [Name]      NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Hierarchy_Hierarchy] FOREIGN KEY ([Parent_Id]) REFERENCES [dbo].[Hierarchy] ([Id])
);

在网络api中，我尝试返回完整的层次结构.我想加载整个表的事实也许是一件特别的事(可能会有所帮助).

In the web api I try to return the complete hierarchy. One maybe special thing (that could help) is the fact that I want to load the complete table.

我还知道我可以使用预先加载功能和导航属性(儿童使用父级和InverseParent)

I also know that I could use eager loading and the navigation property (Parent and InverseParent for children)

_dbContext.Hierarchy.Include(h => h.InverseParent).ThenInclude(h => h.InverseParent)...

这样做的问题是，这将加载硬编码的深度(例如，如果我使用1 Include()和5 ThenInclude()，则为六个级别)，但是我的层次结构具有灵活的深度.

The problem with that is that this would load a hard coded depth (e.g. six levels if I use 1 Include() and 5 ThenInclude()) but my hierarchy has a flexible depth.

任何人都可以通过给我一些代码来加载整个表的方法来帮助我(例如，在使用1个DB调用的最佳情况下将其加载到内存中)，然后使该方法返回完整的层次结构吗?

Can anyone help me out by giving me some code how to load the full table (e.g. into memory in an optimal scenario with 1 DB call) and then make the method return the full hierarchy?

推荐答案

实际上，由于所谓的EF(核心)关系修正，加载整个层次结构非常容易>.

In fact loading the whole hierarchy is quite easy thanks to the so called EF (Core) relationship fixup.

假设我们有以下模型:

public class Hierarchy
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Hierarchy Parent { get; set; }
    public ICollection<Hierarchy> Children { get; set; }
}

然后输入以下代码

var hierarchy = db.Hierarchy.Include(e => e.Children).ToList();

将使用正确填充的Parent和Children属性加载整个层次结构.

will load the whole hierarchy with correctly populated Parent and Children properties.

当您仅需要加载层次结构的一部分时，就会出现在引用的文章中描述的问题，由于缺少LINQ中的CTE之类的支持，因此这很困难.

The problem described in the referenced posts arise when you need to load just part of the hierarchy, which is hard due to the lack of CTE like support in LINQ.

这篇关于使用EntityFramework.Core从自引用表加载完整层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！