0

I wanted to select all related records from a self referencing table using entity framework . I don't have any specific level . It's just for all related records all child and all parent . Below is some sample data . So If I pass ID 5 to my function it should give me records having id 1,4,5,7,8 .

5 is the parent id for Id 7 and 7 is the parent id for Id 8 . Similarly parent id for 5 is 1 and 1 is also the parent id for Id 4 .

So in this way I would like to select all beneath and above records of Id 5 .

Data Source

Id  Name    ParentId
1   Test 1  NULL
2   Test 2  NULL
3   Test 3  NULL
4   Test 4  1
5   Test 5  1
6   Test 6  4
7   Test 7  5
8   Test 7  7

Expected 

1   Test 1  NULL
4   Test 4  1
5   Test 5  1
7   Test 7  5
8   Test 7  7
Deb
  • 391
  • 2
  • 20
  • Check out this [question](http://stackoverflow.com/questions/11929535/writing-recursive-cte-using-entity-framework-fluent-syntax-or-inline-syntax). You need a recursive query and it looks like EF doesn't directly support that – juharr Dec 15 '15 at 17:34
  • I didn't write anything solid which can be used as base . I was trying to read records beneath and above using individual loops but here as I don't have any level so it needs some sort of recursion . So my code didn't work at all and . You can take it from scratch . – Deb Dec 15 '15 at 17:48
  • 1
    Start here http://sqlfiddle.com/#!3/497b0/6. Its not a complete answer – Pரதீப் Dec 15 '15 at 17:57
  • Thanks a lot . I was also trying some thing . So do I need to make two separate quires ? One for beneath and one for above records and push them in to a temp table and select from there deleting duplicate one . – Deb Dec 15 '15 at 18:17
  • I have added a new column called RootId to support multiple family of tree hierarchy . Fromthe given Id now I can get the root Id and load the complete tree using the root Id . http://sqlfiddle.com/#!3/9d931 – Deb Dec 16 '15 at 09:01

1 Answers1

0

I got the logic . We need to first identify the root node and then find the linked family tree from the root node . We can write a CTE to find the root node or we can add a new column which will keep the root node id . Sql Fiddle With Root Node

Deb
  • 391
  • 2
  • 20