I am using Entity Framework 5, C# and Visual Studio 2012 to do a project. One of SQL Server tables of that project keeps data in hierarchical structure; its name is Employee. The Employee table is considered as a forest of employee trees (employee groups). Each tree starts with a row whose ParentId column value is null, and its descendant rows have non-null ParentId values.
When an employee joins or leaves a tree (group), all rows of that tree are set to inactive (0) and becomes inactive tree, and at the same time a new tree which has IsActive columns being set to 1 is copied from the old inactive tree . All the rows of the new tree are definitely have ParentId columns set to new values because EmployeeId primary identity keys for new rows are created by SQL Server. See details below.
I'd like to use Entity Framework and C# codes to make deep copy for such a new tree of rows. What is the best way to achieve that? Thank you in advance.
Details
Employee table before a new tree of rows is copied:
EmployeeId FirstName LastName ParentId IsActive
1 aaa bbb null 1
2 ccc ddd 1 1
5 kkk lll 1 1
3 eee fff 2 1
4 ggg hhh 2 1
6 mmm nnn 5 1
7 lim wer null 1
8 kie aol 7 1
9 hfj nvu 8 1
When employee named "ooo ppp" joins the tree whose root EmployeeId of 7, the table is updated as follows, pay attention to the last 4 rows to see how deep copied rows I want.
EmployeeId FirstName LastName ParentId IsActive
1 aaa bbb null 1
2 ccc ddd 1 1
5 kkk lll 1 1
3 eee fff 2 1
4 ggg hhh 2 1
6 mmm nnn 5 1
7 lim wer null 0
8 kie aol 7 0
9 hfj nvu 8 0
10 lim wer null 1
11 kie aol 10 1
12 hfj nvu 11 1
13 ooo ppp 11 1