实体框架和自引用表

I need to have a database that starts with a table called "User" that needs to self reference itself and will have a very deep graph of related objects. It will need to be like the left side of the image below (disregard the right side).

我需要一个以一个名为“User”的表开头的数据库,该表需要自我引用,并且会有一个非常深的相关对象图。它需要像下面图像的左侧(忽略右侧)。

I will also need to traverse through this graph both up and downwards in order to calculate percentages, totals, etc. In other words I'll need to travese the entire graph in some cases.

我还需要向上和向下遍历此图表以计算百分比,总计等。换句话说,在某些情况下我需要遍历整个图表。

Is this possible and/or how is it done? Can traversing be done right in the LINQ statement? Examples?

这是可能的和/或它是如何完成的?可以在LINQ语句中直接进行遍历吗?例子?

EDIT: I'm basically trying to create a network marketing scenario and need to calculate each persons earnings.

编辑:我基本上是想创建一个网络营销场景,需要计算每个人的收入。

Examples:

1. To be able to calulate the total sales for each user under a specific user (so each user would have some sort of revenue coming in).

能够计算特定用户下每个用户的总销售额(因此每个用户都会有某种收入)。

2. Calculate the commission at a certain level of the tree (e.g. if the top person had 3 people below them each selling a product for $1 and the commission was 50% then there would be $1.50.)

计算树木某一级别的佣金(例如,如果*人员下面有3人,每人以1美元的价格出售产品,佣金为50%,则会有1.50美元。)

3. If I queried the image above (on the left) for "B" I should get "B,H,I,J,N,O"

如果我查询上面(左边)的图像为“B”,我应该得到“B,H,I,J,N,O”

Hopefully that helps :S

希望这有助于:S

2 个解决方案

#1

You can't traverse the whole tree using just LINQ in a way that would translate to single SQL query (or a constant count of them). You can do it either with one query for each level or with one query, that is limited to a specific count of levels (but such a query would get really big with many levels).

您不能仅使用LINQ遍历整个树,这种方式可以转换为单个SQL查询(或者它们的常量计数)。您可以使用每个级别的一个查询或使用一个查询来执行此操作,该查询仅限于特定的级别计数(但是这样的查询会在很多级别上变得非常大)。

In T-SQL (I assume you're using MS SQL Server), you can do this using recursive common table expressions. It should be possible to put that into a stored procedure that you can use from LINQ to get the information you actually want.

在T-SQL中(我假设您使用的是MS SQL Server),您可以使用递归公用表表达式来执行此操作。应该可以将它放入一个存储过程,您可以从LINQ使用它来获取您真正想要的信息。

To sum up, your options are:

总而言之,您的选择是:

1. Don't use LINQ, just SQL with recursive CTE

不要使用LINQ,只使用带递归CTE的SQL

2. Use recursive CTE in a stored procedure from LINQ

在LINQ的存储过程中使用递归CTE

3. Use LINQ, creating one query for each level

使用LINQ,为每个级别创建一个查询

4. Use ugly LINQ query limited to just a few levels

使用难看的LINQ查询仅限于几个级别

#2

I know this is late, but if you look at Directed Graph algorithms, you can bypass the recursive issues. check out these 2 articles:

我知道这已经很晚了,但如果你看一下Directed Graph算法,你可以绕过递归问题。看看这两篇文章:

http://www.sitepoint.com/hierarchical-data-database/

http://www.codeproject.com/Articles/22824/A-Model-to-Represent-Directed-Acyclic-Graphs-DAG-o