0

I have a simple issue. I have instantiated an in memory List<item> from a DbSet<item> (which represents a database table). Then my application makes changes to the list -- adds some items and removes others.

Now I want to synchronize the changes back in-bulk into the database (i.e updating the table from the changed list) all in one go --- NOT one item at a time.

I want the backing table to become an exact copy of my list.

Currently I'm forced to to do it in two ugly steps --

  1. removing items from the table that are not in the list
  2. adding items to the table that are in the list (if they aren't already in the table)

In the following code db.Items is my DbSet<item> (which represent the database table) and List.Items is my in-memory List<item>

using(var db = new MyDatabase())   
{      
      db.RemoveRange(db.Items.Where(item => !List.Items.Contains(item)));  

      db.AddRange(List.Items.Where(item => !db.Items.Contains(item)));  

      db.SaveChanges();   
}

Isn't there a simpler, one line call that can synchronize the changes back?

I must mention a few things:

  1. The db.UpdateRange(List.Items) or db.Items.UpdateRange(List.Items) calls do NOT do what I am talking about here. They do NOT delete from the database table the items that I have deleted from my list.
  2. My provider is Microsoft's Sqlite provider.
  3. And this question => Synchronize C# Object Array with Database Table --- has nothing to do with my question.

Thank you very much in anticipation of a good solid answer :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
explorer
  • 11,710
  • 5
  • 32
  • 39
  • If you are OK to use sql stored procedure you can do it. Pass your in memory list as table variable and use sql merge to synchronize https://www.sqlservertutorial.net/sql-server-basics/sql-server-merge/ – Arun Kumar May 24 '20 at 05:49
  • I had a similar question, it might be outdated, but parts of the old answers might still help to get insights. https://stackoverflow.com/q/45862050/3090544 – MarkusEgle May 24 '20 at 06:31

0 Answers0