为一个表中的嵌套数据创建简化查询(.NET Core)

Question

我有这样的表：

Steps
- id
- name
- parendId

parentId指向同一表中的id。

主要问题是因为我有三个层次的嵌套。。。

我想要这样的输出：

step: {
id: 1,
name: 'step 1'
childs: [
{
id: 2
name: 'child lvl 1',
childs: [
{
id: 3,
name: 'child lvl 2'
childs: [...]
}, ...
]
}, ...
]
}

所以在我的应用程序中，我有这样的东西：

var parents =  _apiDbContext.StepResult
.ToList();
foreach (var parent in parents) {
var childs = _apiDbContext.StepResult
.Where(s => s.StepParent == parent.Id)
.ToList();
foreach (var child in childs) {
var childsLvl2 = _apiDbContext.StepResult
.Where(s => s.StepParent == child.Id)
.ToList();
// ... One more level
}
}

这是最小的代码，例如，如果我犯了一些错误，请告诉我。

因此，可以通过一个查询或.net核心中的一些"魔术"来完成这些事情。

这很好，但正在扼杀性能。。。因此，任何建议都会有所帮助。

谢谢！

Answer 1

尝试下面的递归代码，该代码将适用于任何级别的

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication139
{
class Program
{
static List<Step> steps = new List<Step>();
static void Main(string[] args)
{
Tree root = new Tree();
int rootId = 0;
GetRecursive(rootId, root);
}
static void GetRecursive(int parentId, Tree parent)
{
foreach(Step step in steps.Where(x => x.id == parentId))
{
parent.step = step;
if (parent.children == null) parent.children = new List<Tree>();
Tree child = new Tree();
parent.children.Add(child);
GetRecursive(step.id, child);
}
}
}
public class Step
{
public int id { get;set;}
public string name { get;set;}
public int parendId { get; set; }
}
public class Tree
{
public List<Tree> children { get; set; }
public Step step { get; set; }
}
}

Answer 2

我会一次将所有行选择到一个平面数据结构中，然后将其加载到所需的树结构中。这将给你一个如何做到这一点的好主意：

//declare a class to hold your data
public class step
{
public int id { get; set; }
public string name { get; set; }
public int? parentId { get; set; }
public List<step> childs { get; set; }
}
public void loadSteps()
{
var steps = new List<step>();
//instead of doing this load from your db
steps.Add(new step { id = 1, name = "t1", parentId = null });
steps.Add(new step { id = 2, name = "t2", parentId = 1 });
steps.Add(new step { id = 3, name = "t3", parentId = 2 });
steps.Add(new step { id = 4, name = "t4", parentId = 1 });
steps.Add(new step { id = 5, name = "t5", parentId = 1 });
steps.Add(new step { id = 6, name = "t6", parentId = null });
steps.Add(new step { id = 7, name = "t7", parentId = 6 });
//build the tree from the flat data
var tree = BuildTree(steps);
}
//build the tree using linq (this could also be done with a queue or recursion
static List<step> BuildTree(List<step> items)
{
items.ForEach(i => i.childs = items.Where(ch => ch.parentId == i.id).ToList());
return items.Where(i => i.parentId == null).ToList();
}

Answer 3

您可以使用单个数据库查询创建步骤树：

var dbList = _apiDbContext.StepResult
.ToList();
List<Steps> steps = dbList.Where(x => x.parentID == null).Select(x => new Steps { id = x.id, name = x.name }).ToList();
steps.ForEach(x => Sample(x, dbList));

public class Steps
{
public int id { get; set; }
public string name { get; set; }
public List<Steps> childs { get; set; }
}

public void Sample(Steps step, List<DbSteps> dbList)
{
var childs = dbList.Where(x => x.parentID == step.id);
if (!childs.Any())
return;
step.childs = childs.Select(x => new Steps { id = x.id, name = x.name }).ToList();
step.childs.ForEach(x => Sample(x, dbList));
}