1

I have a simple method that inserts a row into one of our tables using entity framework. However, that table has a trigger than manipulates the row as soon as it's inserted. This seems to be making entity framework by producing the error "Store update, insert, or delete statement affected an unexpected number of rows (0)". How can I correct this?

using (var supportContext = new SupportContext(CustomerId))
{
    var mobileUser = new AcctMobileUser
    {
        amu_user_id = userId
    };
    supportContext.AcctMobileUsers.Add(mobileUser);
    await supportContext.SaveChangesAsync();
}
JD Davis
  • 3,517
  • 4
  • 28
  • 61
  • `has a trigger than manipulates the row as soon as it's inserted` what does it do to the row? – Matt Burland Oct 29 '15 at 20:33
  • It actually does a TON of background stuff. It manipulates one of the fields (that's a FK) to determine if it's a billed user or not. But essentially it's only updating one of the columns when it's all said and done. – JD Davis Oct 29 '15 at 20:37
  • 1
    see [here](http://stackoverflow.com/questions/1836173/entity-framework-store-update-insert-or-delete-statement-affected-an-unexpec) – devlin carnate Oct 29 '15 at 20:43
  • Unfortunately none of these seem to really help. – JD Davis Oct 29 '15 at 20:57
  • get rid of the trigger and see if you can't do the code that's inside the trigger inside a stored procedure after you insert, update and or delete.. – MethodMan Oct 29 '15 at 21:01
  • Unfortunately I can't do that either. We have a series of well over 1,000 single tenant databases, and this trigger is vital for many of our other applications. – JD Davis Oct 29 '15 at 21:04
  • 1
    When it isn't a race condition in any case, it most likely has to do with the change tracker. I'd expect an auto increment in the table, but not your model. in this case EF tries to save your entry with your provided Id, DBMS sets Id to its own value, EF goes to check if it was correctly saved and can't find an entry with your provided Id value. – DevilSuichiro Oct 29 '15 at 21:15
  • Is there any way to tell EF to basically just insert the row and then forget about it? As in don't even bother populating the object once it's saved the changes? – JD Davis Oct 29 '15 at 21:16
  • 1
    this depends on what your trigger is actually doing. disabling changetracker is pretty easy, but I don't think it would help in your case. What might help is turning off optimistic concurrency in your case, however I wouldn't really advise to do that. – DevilSuichiro Oct 29 '15 at 21:18
  • 1
    what happens when you turn on Identity insert and basically wrap the insert call in the transaction? When still the same error, your trigger is doing something fishy, and you'd most likely have to calculate your insert sql statement yourself. – DevilSuichiro Oct 29 '15 at 21:21
  • It looks like the trigger does "INSTEAD OF INSERT" so when a row is inserted, it determines what organization the user belongs to, then inserts the row on its own. – JD Davis Oct 29 '15 at 21:21
  • 1
    Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/93734/discussion-between-devilsuichiro-and-jdsfighter). – DevilSuichiro Oct 29 '15 at 21:22

2 Answers2

0

You didn't show enough code to determine how an error could occur in the "title". Everything hinges on how the trigger is coded and what it does. I suggest you obtain that code and it may provide a clue as to why the "title" has changed.

Cormagh
  • 3
  • 2
  • 2
    why is this an answer? – devlin carnate Oct 29 '15 at 20:47
  • I know exactly where, when, and why the column is being changed. The problem is that I cannot touch the trigger as it's present in over 1,000 customer databases, and it could get a bit messy. All I need to do is tell entity framework to insert the record. Perhaps disabling Optimistic Concurrency would help. – JD Davis Oct 29 '15 at 20:49
0

This was occurring due to EF's Optimistic Concurrency model. The trigger that was on the table that was receiving the insert had an "INSTEAD OF INSERT" condition on it. This was causing the row not to be inserted, and instead it was inserting the row itself.

By telling EF to simply execute an arbitrary SQL insert statement, everything is handled as it should. The only caveat of this method is that the object is not returned, so one would have to manually fetch the inserted record.

JD Davis
  • 3,517
  • 4
  • 28
  • 61