如何使用实体框架进行递归加载? [英] How to do recursive load with Entity framework?

问题描述

我在带有 TreeNodes 表的数据库中有一个树结构.该表具有 nodeId、parentId 和 parameterId.在 EF 中，结构类似于 TreeNode.Children，其中每个孩子都是一个 TreeNode...我还有一个包含 id、name 和 rootNodeId 的 Tree 表.

I have a tree structure in the DB with TreeNodes table. the table has nodeId, parentId and parameterId. in the EF, The structure is like TreeNode.Children where each child is a TreeNode... I also have a Tree table with contain id,name and rootNodeId.

在一天结束时，我想将树加载到 TreeView 中，但我不知道如何一次加载它.我试过了:

At the end of the day I would like to load the tree into a TreeView but I can't figure how to load it all at once. I tried:

var trees = from t in context.TreeSet.Include("Root").Include("Root.Children").Include("Root.Children.Parameter")
        .Include("Root.Children.Children")
                        where t.ID == id
                        select t;

这将使我获得前 2 代，但不会更多.如何加载包含所有代和附加数据的整个树?

This will get me the the first 2 generations but not more. How do I load the entire tree with all generations and the additional data?

推荐答案

我最近遇到了这个问题，并在我想出一个简单的方法来实现结果后偶然发现了这个问题.我对 Craig 的答案进行了编辑，提供了第四种方法，但决定的权力应该是另一个答案.我没问题:)

I had this problem recently and stumbled across this question after I figured a simple way to achieve results. I provided an edit to Craig's answer providing a 4th method, but the powers-that-be decided it should be another answer. That's fine with me :)

可以在此处找到我的原始问题/答案.

只要您在表中的项目都知道它们属于哪棵树(在您的情况下看起来像它们:t.ID)，此方法就可以工作.也就是说，不清楚你真正在玩什么实体，但即使你有多个实体，如果实体 Children 不是一个 TreeSet

This works so long as your items in the table all know which tree they belong to (which in your case it looks like they do: t.ID). That said, it's not clear what entities you really have in play, but even if you've got more than one, you must have a FK in the entity Children if that's not a TreeSet

基本上，只是不要使用Include():

Basically, just don't use Include():

var query = from t in context.TreeSet
            where t.ID == id
            select t;

// if TreeSet.Children is a different entity:
var query = from c in context.TreeSetChildren
            // guessing the FK property TreeSetID
            where c.TreeSetID == id
            select c;

这将带回树的所有项目并将它们全部放在集合的根中.此时，您的结果集将如下所示:

This will bring back ALL the items for the tree and put them all in the root of the collection. At this point, your result set will look like this:

-- Item1
   -- Item2
      -- Item3
-- Item4
   -- Item5
-- Item2
-- Item3
-- Item5

因为您可能希望实体仅按层次结构从 EF 中出来，所以这不是您想要的，对吧?

Since you probably want your entities coming out of EF only hierarchically, this isn't what you want, right?

.. 然后，排除存在于根级别的后代:

幸运的是，因为您的模型中有导航属性，子实体集合仍将被填充，如您在上面的结果集插图中所见.通过使用 foreach() 循环手动迭代结果集，并将这些根项添加到 new List()，您现在将拥有一个列表根元素和所有后代正确嵌套.

Fortunately, because you have navigation properties in your model, the child entity collections will still be populated as you can see by the illustration of the result set above. By manually iterating over the result set with a foreach() loop, and adding those root items to a new List<TreeSet>(), you will now have a list with root elements and all descendants properly nested.

如果您的树变大并且性能受到关注，您可以按 ParentID(它是 Nullable，对吧?)是第一.像以前一样迭代和添加，但是一旦到达一个不为空的循环就中断循环.

If your trees get large and performance is a concern, you can sort your return set ASCENDING by ParentID (it's Nullable, right?) so that all the root items are first. Iterate and add as before, but break from the loop once you get to one that is not null.

var subset = query
     // execute the query against the DB
     .ToList()
     // filter out non-root-items
     .Where(x => !x.ParentId.HasValue);

现在 subset 看起来像这样:

And now subset will look like this:

-- Item1
   -- Item2
      -- Item3
-- Item4
   -- Item5

关于 Craig 的解决方案:

1. 您真的不想为此使用延迟加载！！围绕 n+1 查询的必要性而构建的设计将是一个主要的性能问题.
*********(公平地说，如果您要允许用户有选择地向下钻取树，然后它可能是合适的.只是不要使用延迟加载来预先获取它们！)


2. 我已经从来没有尝试过嵌套集的东西，我也不建议破解 EF 配置来完成这项工作，因为有一个更简单的解决方案.


3. 另一个合理的建议正在创建一个提供自链接的数据库视图，然后将该视图映射到中间连接/链接/m2m 表.就我个人而言，我发现这个解决方案比必要的复杂，但它可能有它的用途.

1. You really don't want to use lazy loading for this!! A design built around the necessity for n+1 querying will be a major performance sucker.
********* (Well, to be fair, if you're going to allow a user to selectively drill down the tree, then it could be appropriate. Just don't use lazy loading for getting them all up-front!!)


2. I've never tried the nested set stuff, and I wouldn't suggest hacking EF configuration to make this work either, given there is a far easier solution.


3. Another reasonable suggestion is creating a database view that provides the self-linking, then map that view to an intermediary join/link/m2m table. Personally, I found this solution to be more complicated than necessary, but it probably has its uses.

这篇关于如何使用实体框架进行递归加载?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！