I have inherited a legacy codebase for an existing system. Some new features are to be added and bugs are to be fixed, but the database cannot be changed.
One of the bugs is that when trying to update records from a specific table, I get the exception:
System.Data.Entity.Core.OptimisticConcurrencyException: 'Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.'
The table in question has a composite Primary Key, 2 fields are tinyints and the other is datetime in the database. The EF used was created using Model First, so the EDMX is built from the database and of course maps the datetime to a .NET DateTime.
Looking around at other issues, the problem seems to be with that DateTime mapping; even though I get an entity from the database, edit it and mark it as EntityState.Modified, when I try to write back it can't find the matching record because the datetime part of the PK doesn't match the value of the .NET DateTime - due to precision. Other suggestions have been "change the datetime to datetime2 in the database", but as specified I am not able to change the database.
I've tried manually setting the precision in the EDMX for the 'DateTime' to 0,3 and 7 but it has had no impact. How can I make the EF create the right SQL statement here without changing the database and, for preference, keeping the model-first approach (because it's a complex EDMX and would take a long time to remove)?
I think that the only answer is going to be to use a stored proc to do the update. You can import that proc as an entity function or just use EF to call it directly.
Assuming that when you say that the DB can not be changed, your talking about the schema.
If you can't added a SP, then you would have to execute dynamic SQL instead, which really isn't the best idea.
Thanks, Gary