Geek Answers Handbook

Entity Framework and Self-Reference Table

I need to have a database that starts with a table called "User", which must refer to itself and have a very deep graph of related objects. It should look like the left side of the image below (ignoring the right side).

enter image description here

I also need to go through this graph up and down to calculate percentages, totals, etc. In other words, I will need to track the entire chart anyway.

Is this possible and / or how is this done? Can I scroll right in a LINQ statement? Examples?

EDIT: I am basically trying to create a network marketing scenario and have to calculate each person’s income.

Examples:

  • To be able to calculate the total sales for each user under a specific user (therefore, each user will have some kind of income).
  • Calculate the commission at a certain tree level (for example, if the top person had 3 people below them, each of whom sold the product for $ 1, and the commission was 50%, then it would be $ 1.50.)
  • If I requested the image above (left) for "B", I should get "B, H, I, J, N, O"

Hope this helps: S

+4
source share
2 answers

You cannot traverse the entire tree using only LINQ, so as to translate into a single SQL query (or constant counter). You can do this either with one request for each level, or with one request, which is limited by a specific number of levels (but such a request will be really large at many levels).

In T-SQL (I assume you are using MS SQL Server), you can do this with recursive generic table expressions . It should be possible to put this in a stored procedure that you can use from LINQ to get the information you need.

To summarize, your options are:

  • Do not use LINQ, just SQL with recursive CTE
  • Use recursive CTE in stored procedure from LINQ
  • Use LINQ, creating one query per level
  • Use ugly LINQ query limited to several levels
+3
source

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

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

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

0
source

More articles:


All Articles