update a field value according to another in ef

Question

how can i change the value of a field according to another field value in entity framework mirations?

i have a Table named Tbl_Accounts with bellow columns:

1. ID (int)
2. Code (nvarchar(50))
3. Name (nvarchar(50))
4. ParentID ( int : nullable ) -----> this is the ID of parent that is of type Account( a tree architecture)

In the old version of my program,the 'Code' column has a 3 length digit value,
for example i list some data stored in Tbl_Account in bellow:

• 1,'100','Parent1',null
• 2,'200','Parent2',null
• 3,'300','Parent3',null
• 4,'001','First Child OF Parent1',1
• 5,'002','Seccond Child OF Parent1',1
• 6,'001','First Child OF First Child Of Parent1',4
• 7,'001','First Child OF Seccond Child Of Parent1',5

Now:I want to change the value of codes like bellow according to the it's parent with migrations:

• 1, '100' ,'Parent1',null
• 2, '200' ,'Parent2',null
• 3, '300' ,'Parent3',null
• 4, '100001' ,'First Child OF Parent1',1
• 5,'100002' ,'Seccond Child OF Parent1',1
• 6,'100001001' ,'First Child OF First Child Of Parent1',4
• 7,'100002001' ,'First Child OF Seccond Child Of Parent1',5

Note: code value of Record with ID=7:

• 100002 :the value of code column in record with ID=5
• 001 :the actual value of code column in record with ID=7

code value of Record with ID=5:

• 100 :the value of code column in record with ID=1
• 002 :the actual value of code column in record with ID=5

Answer 1

If you wanna do this in a migration, you can use raw sql.

The Sql (assuming you're working with Sql Server) would look like this (you need a recursive CTE, as an updated value can depend on a "previous" updated value)

WITH cte 
AS
(
    SELECT id, code
    FROM Tbl_Accounts
    WHERE parent_id IS NULL
    UNION ALL
    SELECT u.id, d.code + u.code
    FROM Tbl_Accounts AS u
    INNER JOIN cte AS d
        ON d.id = u.parent_id
)
update t
set t.code = c.code
from cte c
join Tbl_Accounts t on t.id = c.id

If you use Code first migrations, you can put this as a string variable, and just do (in th up method)

Sql(<yourStringVariableContainingTheSql);

You may also execute this query directly in your base(s), by the way.

See sqlFiddle to be sure of the result