inserting using linq-to-entities

Question

Hi I'm having trouble solving this error. Any help on the problem would be much appreciated, thanks!

Error message:

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.

Whenever I try to add a Laptop/Desktop, I get the above error message.
Everything works fine when running locally, but not on dev. The website and service/database are on two different dev boxes.

Tables:
Computer: ComputerID, UserID, HardwareName, Brand, IsDefaultDevice
Desktop: ComputerID,  MonitorWidth
Laptop: ComputerID, BatteryLife

generated sql:
exec sp_executesql N'insert [ScratchPad].[Computer]([UserID], [ComputerName], [Brand], [IsDefaultDevice])
values (@0, @1, @2, @3)
select [ComputerID]
from [ScratchPad].[Computer]
where @@ROWCOUNT > 0 and [ComputerID] = scope_identity()',N'@0 bigint,@1 nvarchar(19),@2 int,@3 bit',@0=2,@1=N'Computer666',@2=1,@3=0


using(var context = new MyDatabaseEntities())
{
       User user = context.Users.FirstOrDefault(x => x.UserID == userId);
       entityToAdd.User = user;
       bool hasOthers = context.Computers.Any(x=>x.User.UserID == userId);
       if(!hasOthers && !entityToAdd.IsDefaultDevice)
            entityToAdd.IsDefaultDevice = true;
       entityToAdd.BrandReference.EntityKey = Brand.GetDellProviderKey();
       context.AddToComputers(entityToAdd);
       context.SaveChanges();
}

Here is the stack trace:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[FaultException`1: 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.]
   System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) +10259418
   System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) +539
   myWebPortal.Repositories.UserServiceRef.IUserService.AddComputer(Int64 userId, Computer toAdd) +0
   myWebPortal.Repositories.UserServiceRef.UseServiceClient.AddComputer(Int64 userId, Computer toAdd) in c:\users\katelyn\documents\my web project\myWebPortal.repositories\service references\userserviceref\reference.cs:1282
   myWebPortal.Repositories.UserAccountRepository.AddComputer(Int64 userId, Computer computer) in C:\Users\katelyn\Documents\my Web Project\myWebPortal.Repositories\UserRepository.cs:238
   myWebPortal.Web.Controllers.ComputerController.AddComputer(ComputerModel model) in C:\Users\katelyn\Documents\my Web Project\myWebPortal.Web\Controllers\ComputerController.cs:71
   lambda_method(ExecutionScope , ControllerBase , Object[] ) +69
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +236
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +31
   System.Web.Mvc.<>c__DisplayClassd.<InvokeActionMethodWithFilters>b__a() +85
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +632195
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +288
   System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +630660
   System.Web.Mvc.Controller.ExecuteCore() +125
   System.Web.Mvc.<>c__DisplayClass8.<BeginProcessRequest>b__4() +48
   System.Web.Mvc.Async.<>c__DisplayClass1.<MakeVoidDelegate>b__0() +21
   System.Web.Mvc.Async.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _) +15
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +85
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +51
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +454
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +263

Also when trying to add a Laptop/Desktop, sometimes a Computer row would be inserted but not a Laptop/Desktop row.

You will *never* see `OptimisticConcurrencyException` unless you have some field where `ConcurrencyMode` is `Fixed`. You don't show that, nor do you show code which compiles or the SQL which is generated. If you would like help, you should provide the *real code and model* you are using, not a made-up example which you think might reflect what you're doing. — Craig Stuntz, Mar 01 '10 at 18:32
I don't have ConcurrencyMode set anywhere. I think it's OptimisticConcurrencyException since it's caught in the catch(OptimisticConcurrencyException e) block. And that is the real code. And what generated SQL? Sorry, new to the whole thing here and just trying to play with linq-to-entities. — user283802, Mar 01 '10 at 18:49
No, a `catch` block cannot change the type of an exception unless it throws a wholly new exception (and I presume you checked for that). If you see `OptimisticConcurrencyException` then some field somewhere has its `ConcurrencyMode` set to `Fixed`. The intention of the feature is for optimistic concurrency control via, e.g., a SQL Server `TIMESTAMP` column. To view the SQL generated by the EF, use SQL Profiler. That will show you the `UPDATE` statement, which should clarify why it doesn't actually update any data. — Craig Stuntz, Mar 01 '10 at 20:02
Thanks. I added the SQL generated and I edited my code to make the concept more clear. — user283802, Mar 02 '10 at 01:11
I still don't see the problem -- what you are doing in the revised code looks OK. Can you add a stack trace for the exception? — Craig Stuntz, Mar 02 '10 at 04:09
Thanks, but this is the trace from your web app. It appears to me you're remoting to another process. The exception is thrown there, so that's the stack we need to look at. One other thing to try is to execute that `INSERT` SQL in SQL Server Management Studio and see if it reports 1 row modified (it should). — Craig Stuntz, Mar 02 '10 at 20:51

score 1 · Answer 1 · answered Sep 20 '10 at 22:24

1

Where are you instantiating/initialising entityToAdd?

Can you confirm the foreign key constraints and primary key information (auto-increment, type, etc.) It seems that the insert is failing for some reason which is being caught by the incorrect number of rows affected.

try running the SQL yourself from within sql management console - do you get any errors? is the record inserted correctly?

You may also want to ensure there are no triggers or similar on the table which may be causing issues.

answered Sep 20 '10 at 22:24

Basic

26,321
24
115
201

Had the similar problem, with same exception. My problem was trigger. Tnx – 100r Feb 02 '11 at 12:38
@100r - I am also running into this error. How did you fix the trigger issue. – muruge Jun 17 '11 at 23:26
@muruge The issue is that the EF attempts to track object state and manage it - Triggers change the state in the db but not in the EF's cache resulting is a mis-match which may cause errors later. The easiest way around this is to implement the trigger in your DAL/BL dpending on the reason for it - Thus all changes are made via entities. If you'd like to post a question with some details of your problem and give us a link, we'll try to help with an alternate approach. FYI It's possible to extend the object context to catch events like `SavingChanges` which may be of use to you. – Basic Jun 18 '11 at 13:33
@Basiclife - I found the solution from the following link. http://stackoverflow.com/questions/5820992/error-when-inserting-into-table-having-instead-of-trigger-from-entity-data-framew/6298870#6298870 – muruge Jun 20 '11 at 20:30
@muruge Tthat's a nice work around to trick it into thinking it's done only the work it was intended to do - It may be intersting the retrieve the record modified by the trigger before the trigger fires, the run your query, then get the same record again - There's a chance the 2nd get via EF will not include the changes made by the trigger. I'm not sure but you'll probably want to test this to death before using it in production – Basic Jun 21 '11 at 22:00
@muruge Sorry, meant to thank you for pointing me at that answer - very interesting - cheers :) – Basic Jun 21 '11 at 22:31

score 1 · Answer 2 · edited May 23 '17 at 11:48

I have experienced the same problem before, and it was caused by an INSTEAD OF trigger in SQL Server. It seems to me that OptimisticConcurrencyExceptions occur when the SQL Server reports that the query completed successfully, but the proceeding SELECT statement does not return the expected number of rows.

@mrunge provided a link to the solution I posted for that problem in the comments on @BasicLife's answer.

Another potential cause is a stored procedure that is used for inserts and updates for your entity. From http://msdn.microsoft.com/en-us/library/bb738618.aspx:

An OptimisticConcurrencyException can also occur when you define an Entity Data Model that uses stored procedures to make updates to the data source. In this case, the exception is raised when the stored procedure that is used to perform updates reports that zero rows were updated.

I'm fairly sure that actual SQL errors (key violations, null errors, etc.) that occur during the normal execution of the SQL query issued by will be reported using a different exception than the one you saw. I can't remember the name of the exception though.

inserting using linq-to-entities

2 Answers2