There is an operation to import records into the database, about 7000 lines are loaded at a time. Lines can be updated, in this case it is necessary to replace the old ones. However, I get a DuplicateKey error (the key is a composite of 3 fields).

using (var db = new Context()) { using (var dbTransaction = db.Database.BeginTransaction()) { try { MasterAccount MA = db.MasterAccounts.FirstOrDefault(i => i.AccountId == AccountID); if (MA == null) return false; if (MA.History == null) MA.History = MH; else MA.History.AddRange(MH); db.SaveChanges(); dbTransaction.Commit(); return true; } catch { dbTransaction.Rollback(); return false; } } } //MA.History = List<History> //MH тоже самое

Actually the question is how to solve such problems without checking each element for existence in the database and not manually updating it? *

  • merge adds, if not, updates, if there is - Senior Pomidor
  • @SeniorAutomator Merge is what, where to look? - SYL
  • GetSession().Merge(T t) - Senior Pomidor
  • @SeniorAutomator GetSession().Merge(T t) is still not clear where it came from. Can a complete example? - SYL
  • one
    Let's continue the discussion in the chat . - Senior Pomidor

1 answer 1

The answer is taken from the chat.

It is necessary to check whether there is already such an ID in the database. If not, add ( MA.History.AddRange(MH) ), otherwise update ( tmp.Update(item) )

author's result

  using(var db = new Kiwi3Context()) { using(var dbTransaction = db.Database.BeginTransaction()) { try { MasterAccount MA = db.MasterAccounts.FirstOrDefault(i = > i.AccountId == AccountID); if (MA == null) return false; if (MA.MasterHistory == null) MA.MasterHistory = MH; else { foreach(var item in MH) { var tmp = MA.MasterHistory.SingleOrDefault(i = > i.Ticket == item.Ticket && i.OpenTime == item.OpenTime && i.Symbol == item.Symbol && i.Type == item.Type && i.Size == item.Size); if (tmp == null) MA.MasterHistory.Add(item); else tmp.Update(item); } } db.SaveChanges(); dbTransaction.Commit(); return true; } catch (Exception e) { dbTransaction.Rollback(); throw new Exception(e.Message, e.InnerException); } }
  • That's right, but tmp.Update(item) is implemented in the class itself by copying each variable, otherwise if you write just tmp = item then we will lose the link to the object and changes to the database will not be written. And this option is not the most rational, perhaps it is easier to load the entire table into a local buffer and then make comparisons. It all depends on the number of records in the database and the number of records to be inserted / updated ... - SYL