实体框架自引用实体查询结果是平的AND新兴的 [英] Entity Framework self-referencing entity query results are flat AND hiearchical

问题描述

我有一个自引用的实体：

I have a self-referencing entity:

当我查询这个实体..

When I query this entity..

var query = this._context.DispositionPossibilities
    .Where(x => x.AreaID == areaID)
    .Where(x => x.IsActive == true);

..生成的集合从根中的查询返回每个项，然后这些项父系ID在子集合中是重复的（由于导航属性）。

.. the resulting collection has EVERY item returned from the query at the root, and then the those items with ParentIDs are 'duplicated' inside the child collections (because of Navigation Properties).

我可以通过这样做来删除它们：

I can remove them by doing this:

    // have to ToArray() first because the child entities will be excluded if I don't..
    rValue = query.ToArray();
    // trim off the entities at the root that shouldn't be there..
    rValue = rValue.Where(x => !x.ParentCode.HasValue).ToArray();

..但是有更好的方法吗？

.. But is there a better way to do this?

推荐答案

我原来的 Include 解决方案不会遍历整个层次结构。这对我来说很明显，但它只会返回层次结构的第一级。

My original Include solution below does not traverse the entire hierarchy. This should've been obvious to me, but it will only return the FIRST level of the hierarchy.

由于我每天最多会每个树一次这样做一次这个电话，所以我会打好表现。



那就是说，我确实想出了一个更好的方法来从层次结构剥离非根元素：

Since I will be making this call per-tree at most once per day, I'll take the performance hit.

That said, I did come up with a better way to strip the non-root elements off the hierarchy:

var subset = new List<DispositionPossibility>();
foreach (var disp in query.OrderBy(x => x.ParentCode).ToArray())
{
    if (!disp.ParentCode.HasValue)
        subset.Add(disp);
    else
        break;
}

这个比<第一种方法我正在使用。 （所以几乎和Include方法一样好，但实际上给了我所有的级别。）
添加ASC排序，并从我的27k项目树上剃掉.1 - .25秒的循环。

---

---

[原始（BAD）答案]

在没有看到这一天并回到它之后，花了5秒钟的时间想想如何做到这一点。

After not looking at this for a day and coming back to it, it took all of 5 seconds to think of how to do this.

var query = context.DispositionPossibilities
    .Include("ChildDispositions")
    .Where(x => x.AreaID == areaID)
    .Where(x => !x.ParentCode.HasValue)
    .Where(x => x.IsActive == true);

我运行代码分析，这种方法在几乎每种情况下表现都更好：

- 最值得注意的是我最大的树有4个水平，约27,400个项目大致为.73到.83秒。
- 对于小树（<5,000），差异不太明显（小于.01 - .05秒）

I ran code profiling, and this method performs better in almost every case:
-- Most notably for my largest trees with 4 levels and ~27,400 items by roughly .73 to .83 seconds.
-- For small trees (<5,000), the difference was far less noticeable (less than .01 - .05 seconds)

这篇关于实体框架自引用实体查询结果是平的AND新兴的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！