I got an OptimisticConcurrencyException error with description of "Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries." when trying to insert into a table using the Entity Framework 4.
I found that the table I was working on had an INSTEAD OF INSERT on it. This was doing a few things and then returning (0 row(s) affected). Entity Framework wasn't happy with this since it looked as though nothing was being inserted and so threw the confusing OptimisticConcurrencyException error.
Workaround
I've been trying to get this to work for a fancy workaround so a drop will be the order of the day:
DROP TRIGGER [dbo].[trg_PITA]
Job done. The main problem was finding this trigger in the first place since it is not something you normally look at and almost impossible to debug.
Better workaround
Almost 3 years later! Rachel Peirson has come up with a workaround to this problem that also allows you to keep your trigger in place. Please see her comments below for details.
Posts
Deleting of trigger is not a solution.
ReplyDeleteDEFINITELY NOT
ReplyDeleteHence the title "Workaround"
DeleteJust letting you know the cause to save you several hours of head scratching. If you have a solution please share. Although, we are on EF 5.0 now so it may well have been fixed since I originally discovered this bug
Still a problem at present. Bugger, I needed that INSTEAD OF INSERT, UPDATE, DELETE Trigger as well.
ReplyDeleteIn case anybody else is still having trouble with this, I found that the error was caused by Entity Framework getting confused by the ROWCOUNT being reported by Sql Server. This is a problem even though the ROWCOUNT is reported correctly when you run UPDATE commands, etc, within Enterprise Manager.
DeleteThe solution is to add the following SELECT statement at the end of your Trigger:
SELECT * FROM deleted UNION ALL
SELECT * FROM inserted;
This causes a SELECT with the appropriate number of rows to keep Entity Framework happy. Looks a bit messy when you use Enterprise Manager, though, and get resulting SELECTs you don't need. Grrrr!
Rachel! Awesome find! Thanks for posting that. Seems to be working. It's the best workaround I've seen so far.
DeleteThanks!
Excellent! Thanks so much for finding a proper solution to work around the cause. I'll mention your solution in an update to my post
DeleteA couple issues you will run into with this solution is that returning results from triggers is deprecated in SQL Server (http://msdn.microsoft.com/en-us/library/ms143729.aspx) and the UNION ALL means that an UPDATE statement would return 2 rows for every update since an UPDATE populates both the inserted and deleted virtual table.
DeleteYou are great!
DeleteGreat suggestion Rachel.... :) it worked for me finally after trying so many things.
ReplyDeleteAkhil.
This saved my bacon...hours I spend trying to figure out what was going wrong. 4 years later! Thanks Rachel!
ReplyDeleteThe blog or and best that is extremely useful to keep I can share the ideas
ReplyDeleteof the future as this is really what I was looking for, I am very comfortable and pleased to come here. Thank you very much.
tanki online | 2048 game|
Bài viết rất hay: Chúng tôi chuyên cung cấp các sản phẩm chất lượng sau:
ReplyDeletebồn massage
bon ngam chan
máy massage chân
Cảm ơn các bạn!
stickers printing uk
ReplyDeleteJust admiring your work and wondering how you managed this blog so well. It’s so remarkable that I can't afford to not go through this valuable information whenever I surf the internet! Mega Millions Payout
ReplyDeleteOur easy-to-use website and the multitude of custom stickers printing option are just some of the reasons why business, graphic designers, and all our customers trust us with their printing – all backed by our 100% Guarantee!
ReplyDeleteCustom Sticker Printing
BUY Packaging Boxes Suppliers Have Great Experience Just imagine your brand we will print your Custom Packaging Boxes
ReplyDeleteCustom Packaging Boxes
home salon
ReplyDeleteI've read your entire information that you stocks in your article and I must say I love it. Many thanks for writing this post. I appreciate it.
ReplyDeletebubble shooter
Permainan Sabung Ayam tentunya sudah pada tahu ya, yang dimana ayam melawan ayam pertandingan yang sangat seru ini bisa kalian nonton secara live lohh, banyak yang bermain di situs kami dan merasa sangat nyaman, bagi kalian yang ingin bermain bisa kunjungi situs kami, dijamin kalian akan merasa sangat senang.
ReplyDeleteDownload S128 Apk
aplikasi sabung ayam
aplikasi sabung ayam s128
aplikasi s128 apk
aplikasi s128
s128 apk sabung ayam
s128 apk
Daftar s128
daftar sabung ayam s128
daftar s128 sabung ayam
daftar s128 apk
Sabung ayam deposit pulsa
s128 deposit pulsa
Login s128
Link alternatif s128
situs alternatif s128
link alternatif sabung ayam
link alternatif L128
link alternatif s12888
link alternatif s1288
Permainan tembak ikan adalah permainan yang paling rame orang mainkan, apalagi jika pelayanannya yang sangat memuaskan bagi anda tentunya kalian pasti akan merasa sangat puas juga kan ?, bagi kalian yang sangat menyukai permainan tembak ikan bisa datang ke situs kami :)
ReplyDeletelayanan livechat joker123
livechat joker123
joker123 deposit 25rb
joker123 deposit pulsa
deposit slot joker123 ovo
livechat joker123
Permainan tembak ikan adalah permainan yang paling rame orang mainkan, apalagi jika pelayanannya yang sangat memuaskan bagi anda tentunya kalian pasti akan merasa sangat puas juga kan ?, bagi kalian yang sangat menyukai permainan tembak ikan bisa datang ke situs kami :)new birthday wishes
ReplyDeletePermainan tembak ikan adalah permainan yang paling rame orang mainkan, apalagi jika pelayanannya yang sangat memuaskan bagi anda tentunya kalian pasti akan merasa sangat puas juga kan ?, bagi kalian yang sangat menyukai permainan tembak ikan bisa datang ke situs kami :)new birthday wishes
ReplyDeleteCustom Packaging Boxes