0

i´m using EF6.

After i clear a tabel and i want to add a new entry to that table i get the following error:

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded

The code for deleting the databasetable:

 public void ResetStatistics() {
        _lisDatabase.Database.ExecuteSqlCommand("TRUNCATE TABLE Sortedtubes");
        _lisDatabase.sortedtubes.Local.Clear();
 }

After that i want to add a new entry to that table with the following code:

 Sortedtubes tube = new Sortedtubes();
 tube.Time = time;
 tube.Milliseconds = time.Millisecond.ToString();
 tube.Barcode = barcode;
 tube.Tubetype = tubeType;
 tube.Target = target;
 tube.Materialcode = materialCode;

 try {
        _lisDatabase.sortedtubes.Add(tube);
        _lisDatabase.SaveChanges(); // Error appears here
 } catch (DbUpdateConcurrencyException ex) {
        // maybe do something here?
 }

I tryed the sugestions on the EF documentation with no luck: https://msdn.microsoft.com/en-us/data/jj592904

EDIT

The problem seems to be the Clear() method (_lisDatabase.sortedtubes.Local.Clear();). After i execute this method the error appears after the next SaveChanges().

So maybe there is an other way to handle this? I have a GridView in my application witch is bind to the sortedtubes entity and i clear it so that the GridView is also cleared, when i truncat the table.

cybertronic
  • 117
  • 1
  • 10
  • I'm not sure I understand : you delete your table and then you try to add value inside the table you deleted ? – Florian Jan 09 '18 at 14:06
  • ok delete is the wrong word i clear the table with the sql command "truncate table " – cybertronic Jan 09 '18 at 14:11
  • 1
    `_lisDatabase.sortedtubes.Local.Clear()` marks objects for delete. It doesn't just clear the local collection. You better use a new `_lisDatabase` instance. – Gert Arnold Jan 09 '18 at 14:14
  • Your instance of _lisDatabase is altered when you use "ExecuteSqlCommand". When you try to save your changes, it looks like "someone else" changed the record and your instance is outdated. Aaron's answer should be fine. – Florian Jan 09 '18 at 14:22

3 Answers3

0

It seems to me that your problem lies in trying to truncate the table and manually clearing your local version of the DbSet. You should just change your EF entities and save them, then those changes are reflected in the database.

This should work:

_lisDatabase.SortedTubes.RemoveRange(_lisDatabase.SortedTubes);
_lisDatabase.SortedTubes.Add(new SortedTube());
_lisDatabase.SaveChanges();

Alternatively, try this:

https://stackoverflow.com/a/10450893/5392513

Aaron
  • 622
  • 7
  • 15
  • I get the same error as in title when i try to SaveChanges(). For your solution with RemoveRange and the solution you linked :( – cybertronic Jan 09 '18 at 14:51
  • @cybertronic Not likely. Only other thing I can suggest is to do a `context.SortedTubes.Load();` after your `truncate`. – Aaron Jan 09 '18 at 16:01
  • If i Load() after truncate that seems to work, but i use the _sortettubes_ entity as source for a Datagrid and i want to clear it also, so i execute the `_lisDatabase.sortedtubes.Local.Clear();` too but after that i get the error again. – cybertronic Jan 10 '18 at 07:23
  • Don't do the `Clear()`. The `Load()` will clear your `DbSet`. You need to rebind the grid. – Aaron Jan 10 '18 at 09:25
  • Ok i understand but how do i rebind an object? – cybertronic Jan 10 '18 at 09:37
  • If i debug the method the `Load()` command doesn´t load the current data from database. I see the database is empty but the Count property of the entity still includes all values of the entity befor the truncate. – cybertronic Jan 10 '18 at 09:46
  • I got it, however I think I've gone about as far as I can with this. – Aaron Jan 10 '18 at 10:15
  • Maybe there is an option to update the entity with the current state of the database table? – cybertronic Jan 10 '18 at 11:17
0

I suspected that you retrieve the entiries from database with same context by tracking before Truncate the table and when you apply the SaveChanges the EF is trying to delete already deleted records by Truncate. So, usual way to perform it creating a new instance from context and apply actions. It is not good idea to make long database processes with same context. So, in your case if you want to truncate table and use the same context to add new entries later you should detach all tracked entries after truncate action.

public void ResetStatistics()
{
    _lisDatabase.Database.ExecuteSqlCommand("TRUNCATE TABLE Sortedtubes");
    _lisDatabase.sortedtubes.Local.Clear();
    foreach (var entry in _lisDatabase.ChangeTracker.Entries<Sortedtubes>())
    {
        _lisDatabase.Entry(entry).State = EntityState.Detached;
    }
}

Also, if it is possible use .AsNoTracking in your queries before Truncate the table and no need to detach entries manually.

lucky
  • 12,734
  • 4
  • 24
  • 46
0

So.. finaly i think i got a solution.

My reset method looks like this now:

public void ResetStatistics() {
     _lisDatabase.sortedtubes.RemoveRange(_lisDatabase.sortedtubes);
     _lisDatabase.SaveChanges();
}

And my add stays the same:

_lisDatabase.sortedtubes.Add(tube);
_lisDatabase.SaveChanges();

but the "big" change was in my Sortedtubes entity. I changed the type of Time from DateTime to a normal String

from:

[Key]
[Column(Order = 1)]
public DateTime? Time { get; set; }

to:

[StringLength(45)]
public string Time { get; set; }

The problem seems to be the Type of the Time property of the Sortedtubes Entity. But i don`t know why. If anyone wants to explain me this case?

Thanks to all for your help.

cybertronic
  • 117
  • 1
  • 10