Ytukyg

Is it possible to change values of ID column in a table and at the same time update all dependent records in other tables?

Example Data:

Table : Document                        =>  should be:

Id       Code           Title           Id             Title

------------------------------------    ------------------------------------

1        112512         "Some Title"    112512         "Some Title"

2        123234         "Some Title"    123234         "Some Title"  

3        234321         "Some Title"    234321         "Some Title"



Table : Person

Id       Name           DocumentID 

-----------------------------------  

290      "Roberto"      1 ---> should change to 112512      

291      "Roberta"      3 ---> should change to 234321

I'm using entity framework to access SQLServer databse and I think that managing forms and user inputs will be much easier with the above change. the Migration for structure is done. now I need a SQL Command for data migration. I Also tried migration data manually:

var Pers = unitOfWork.PersonRep.GetAll().ToList();

foreach (var person in persons)

{

    var doc = person.Document;

    int code = int.Parse(doc.Code);

    doc.ID = code;

    person.DocumnetId = code;

}

unitOfWork.Complete();

Migration failed with this error:

The property 'ID' is part of the object's key information and cannot
be modified.

It looks like id is a primary key, as part of EF limitation you can't change pk you can either add another id for example (id_pk) and set id_pk as pk and this is how you can change you id or just delete insert.

in general if your id keeps changing you would want to use id_pk

Your best bet (if you need to keep your data) would be to recreate the FKs in the DB with the ON UPDATE CASCADE option (see documentation for more details). Then updating the values through standard SQL UPDATE statement and then recreating the FKs without the option.

The recreation (drop & create) of the FKs makes sure, that when you run the UPDATE statement on the Document table, all FKs pointing to it will be updated automatically (if you have more FKs pointing to Document.ID you should recreate all of them using the option). The second recreation of the FKs is just there to set the things the same way as they were before - I'm not sure what role it does play for code-first and/or migrations. Also you would need to make sure, that you keep the names of FKs the same (again not sure if needed).

This of course assumes, you feel comfortable about using direct SQL as it's ignoring the migrations part altogether.