0

I use with winform in C# and Entity Framework.

In Database I have a contact table

Between "word" and "user", Word table has a lot of data (4000+).

I have a window with datagridview where there is a checkbox in each line that the user marks the words he wants.

And by pressing the save button I want to update all the records that he has changed in the table.

listWord = Program.DB.WordUseUser.Where(lw => lw.IdUser == thisIdUser).ToList();

///Clicking on the checkbox I add or remove from ListWord accordingly...

foreach (var item in listWord)
        {
            Program.DB.WordUseUser.Remove(item);
        }
Program.DB.SaveChanges();
foreach (WordUseUser item in listWord)
        {
            Program.DB.WordUseUser.Add(item);
        }
Program.DB.SaveChanges();

It takes a lot of time (of course ...)

And I'm looking for a more effective solution.

I tried to use a solution here:Fastest Way of Inserting in Entity Framework

But it only talks about updating existing data

And not updating and adding and deleting together

I would love for help !!

hadas
  • 1
  • 1
  • how about [this post](https://stackoverflow.com/questions/21568479/how-can-i-delete-1-000-rows-with-ef6) ? is it helpfull? – Mong Zhu Sep 24 '19 at 09:47
  • I am sorry, I don't get why you remove all items and then add them directly afterwards? every item is tracked by the EF system. So if you change any property of any item in listWord the system will know, and it will update all items when you call `Program.DB.SaveChanges();` – Mong Zhu Sep 24 '19 at 09:50
  • If the user had a markup with a specific word and then he deleted his markup I don't want every time it happens to delete the record from database So I thought first of all to delete everything and then use List to add-update-delete while the checkbox was clicked. – hadas Sep 24 '19 at 10:23

1 Answers1

0

Fast reply - you have to do it inside explicit transaction. Not only this is secure, but also this would be much more faster.

So, begin transaction - do your updates/inserts and commit transaction.

Every query creates it's own implicit transaction. Unless there is already existing transaction. So think of it as: without creating a transaction database has to do 12000 operations (for every query: create transaction, execute query, commit transaction) and when you create an explicit transaction then it's just 4002 operations.

Adam Jachocki
  • 1,897
  • 1
  • 12
  • 28
  • I tried it and it works. But I have another question - is it right to do it this way- first of all to delete everything and then use List to add-update-delete while the checkbox was clicked – hadas Sep 24 '19 at 10:59
  • If you delete everything so what would you like to update/remove next? :) If you are able to "track" that for example this word has to be deleted and that updated, then try to do this without deleting everything. But there are some cases when deleting everything is much more simpler (and sometimes faster). So you can do it unless there are dependencies somewhere in the database on this table. – Adam Jachocki Sep 24 '19 at 11:13