For updating records, instead of querying the context and updating each record individually, we currently use code that does DeleteAllOnSubmit on existing set of rows and InsertAllOnSubmit on new set of rows. This worked fine for majority of our scenarios, as in if the same row (content) is being inserted/deleted, it gets removed even though we have an insert and a delete in the ChangeSet. Also, if the primary key is the same, and the records have different content, it converts it to a single update. The problem we have is the primary key’s match in a case insensitive manner, like say ‘abc’ and ‘Abc’, Linq thinks they are different keys and then tries to run the Insert first followed by the delete next which fails due to primary key violation, since for our database settings, both the primary keys are considered equal. Is there a way where we could make Linq use a case insensitive comparison, when it determines an update from the inserts and deletes in ChangeSet?
I am aware that the other way would be to query the database, and if the record is present, do a update instead of a insert and a delete. But we do have this logic for multiple objects and we would like to see if there are other options that work.
Thanks for the responses.
Let me try to explain the issue we have with a example.
Say we have two tables a Bank and a Branch where a Bank can have multiple Branches.
We are given a set of branches that need to set in the table. So the logic would be to delete all branches for that bank and set it to the set of branches we have.
The current code we have does something
DataContext dc = new DataContext();
var destBranches = dc.Branches.Where(b => b.BankID.Equals("123"));
dc.Users.DeleteAllOnSubmit(destBranches); dc.Branches.InsertAllOnSubmit(branches);
If we went with the update route, for each branch, we have to see if it exists in dest, then modify its properties, if not insert it, and finally if any dest branch is not in the set of branches, delete it. We have lots of tables that this change needs to be made.