DbUpdateConcurrency Exceptions

[gpranjan]

I keep getting this exception most of the time for the code below
But there is no concurrency in the system. No other process even runs on the database.

Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException : The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See https://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

Is there anything fundamentally wrong with the code below?

1. Delete existing records of the specified name by
   • Fetching records of the relevant name, by using the Where clause
   • Execute DbSet.RemoveRange
   • Execute DbContext.SaveChanges
2. Create a new record by
   • Execute DbSet.Add
   • Execute DbContext.SaveChanges
3. Update the new record by
   • Fetch the record again from the database by specifying its primary key
   • Make modifications on the C# structure
   • Execute DbSet.Update
   • Execute DbContext.SaveChanges

[Test]
public void Expt2() {
    string? userName = createUserViewRequest.userName;
    var dbUserViews = exptContext.userViews.Where(
        dbUserView => dbUserView.userName == userName);
    if (dbUserViews.Count() > 0) {
        exptContext.userViews.RemoveRange(dbUserViews);
        exptContext.SaveChanges();
    }

    DbUserView initialView = createUserViewRequest.toDbUserView();
    exptContext.userViews.Add(initialView);
    exptContext.SaveChanges();
    Thread.Sleep(2000);
    DbUserView? _refreshView = exptContext.userViews.Find(
        initialView.userName, initialView.fromUrl, initialView.startTime);
    if (_refreshView == null) 
        Assert.Fail("Db fetch gave null view");
    else {
        DbUserView refreshView = (DbUserView)_refreshView;
        refreshView.lastActiveTime = DateTime.Now.ToUniversalTime();
        exptContext.userViews.Update(refreshView);
        exptContext.SaveChanges();
    }
}

I checked by printing the ChangeTracker as
exptContext.ChangeTracker.DebugView.LongView, and it appeared there was nothing wrong, but not sure

 

[Skydiver]

Thanks for trying to use code tags. Please use actual code tags for multiline code. It's the </> icon. You've been using the inline code tags (>_).

Yes, it's another quirk of this forum. Most other sites use the </> to mean HTML, while >_ is means code. Alas, the designers of this forum software doesn't go by that convention.

 

[Skydiver]

Which line is throwing the exception?

 

[gpranjan]

Found the solution, with hints and help

1. When doing UnitTests through VSCode, the context seems to be preserved across multiple test-runs. So, creating the DbContext object inside the Test, instead of a global DbContext object helps
2. When using the same Context object after a DbContext.SaveChanges, it simplifies code to Clear the ChangeTracker. This has been added to the code.
3. When fetching an object and updating the object, there is no need to call DbSet.Update(). The DbContext.saveChanges itself takes care of it. In fact, DbSet.Update() actually causes a DbUpdateConcurrencyException

Code shown with comments on the changes

    [Test]
    public void AllSteps() {

        // Add this envelope
        using(ExptContext exptContext = new ExptContext()) {

            // Add this line
            exptContext.ChangeTracker.Clear();
            var dbUserViews = exptContext.userViews.Where(
                dbUserView => dbUserView.userName == userName);
            if (dbUserViews.Count() > 0) {
                exptContext.RemoveRange(dbUserViews);
                exptContext.SaveChanges();
            }

            DbUserView initialView = getInitialView();
            exptContext.ChangeTracker.Clear();
            exptContext.userViews.Add(initialView);
            exptContext.SaveChanges();

            // Add this line
            exptContext.ChangeTracker.Clear();
            DbUserView? _refreshView = exptContext.userViews.First();
            _refreshView.lastActiveTime = DateTime.UtcNow;

            Assert.That(_refreshView.startTime, Is.Not.EqualTo(_refreshView.lastActiveTime));
            if (_refreshView == null)
                Assert.Fail("Db fetch gave null view");
            else {

                DbUserView refreshView = (DbUserView)_refreshView;
                refreshView.lastActiveTime = DateTime.Now.ToUniversalTime();

                // Comment out this line
                // exptContext.userViews.Update(refreshView);

                exptContext.SaveChanges();
            }
        }          
    }

 

[gpranjan]

Which line is throwing the exception?

The first and the third dbContext.SaveChanges(),
- One after the RemoveRange, which was very weird
- One after the DbUpdate, which made a little more sense.

I did get help to solve the problem, and have posted the solution.
So, see my other post on the same thread.
-------------------------------------------------------------------------------------------------------------

[Rant]
Seems like there are guidelines to code, that I could not easily find in MSFT documentation.
MSFT does not fully (or clearly) explain the design aspects of DbContext.
They put a case-by-case analysis, which makes it hard to get the overall picture,
and arrive at robust code.
[End Rant]

 

[gpranjan]

Looks like I spoke too soon.
As soon as I split the code to run the tests across a server and a client, the tests stopped working,
and it has not been working even after I shutdown the server, and ran the code from a single place,
even with all the changes I mentioned.

I suspect there is either some permanency to the caches,
or there is some timeout issue, as a result of which it always comes back as
"modified zero records from database"

Will try a few more alternatives.

 

[Skydiver]

What happens when you run the code as an actual console program? Dump NUnit to get rid of one of the variables.

 

[gpranjan]

Ported it to the ConsoleApp. Seems like I am not able to reproduce the problem in either the UnitTest or the ConsoleApp.
Here, I kept the App and the UnitTest same as yesterday.

Incidentally, on other tests, I faced other problems of delayed write-back to database in other test-scripts.
In these, I had wrapped each database call in its own context.
Based on this, I put a transaction scope inside the dbcontext for each of the three phases.

Did not face any problems after this today (whereas yesterday, it was the trigger for everything to stop working). It even worked with one phase from unit-test, and the other two from the server

So, perhaps those are the rules. I will know after further experience.

Though, I am a bit surprised. I thought EntityFramework did its own transactions on a need basis. Perhaps TransactionScope is using an IsolationLevel of Serializable, while EntityFramework is perhaps using an isolation level of ReadCommitted.

That said, lot of loosely hanging observations. Will leave it open for a day, and then Resolve this issue. Also, will place code for information sakes.

 

[gpranjan]

New proposed solution. Under observation

    public void AllSteps() {

        // Add this envelope
        using(ExptContext exptContext = new ExptContext())
        using(TransactionScope xaction = new TransactionScope())
        {

            var dbUserViews = exptContext.userViews.Where(
                dbUserView => dbUserView.userName == userName);
            if (dbUserViews.Count() > 0) {
                exptContext.userViews.RemoveRange(dbUserViews);
                exptContext.SaveChanges();
            }
            xaction.Complete();
        }

        DbUserView initialView = getInitialView();

        using(ExptContext exptContext = new ExptContext())
        using(TransactionScope xaction = new TransactionScope())
        {

            exptContext.ChangeTracker.Clear();
            exptContext.userViews.Add(initialView);
            exptContext.SaveChanges(false);
            xaction.Complete();
        }

        Thread.Sleep(2000);
        Console.WriteLine(initialView.startTime);

        using(ExptContext exptContext = new ExptContext())
        using(TransactionScope xaction = new TransactionScope())
        {
            exptContext.ChangeTracker.Clear();

            DbUserView? refreshView =  exptContext.userViews.First(
                dbUserView =>
                    ( dbUserView.userName == initialView.userName ) &&
                    ( dbUserView.fromUrl == initialView.fromUrl ) &&
                    dbUserView.startTime.Equals(initialView.startTime)
            );
            if (refreshView == null) Assert.Fail("refreshView is null");
            refreshView.lastActiveTime = GetCurrentDateTime();
            exptContext.SaveChanges();
            xaction.Complete();
        }           
    }

 

[Skydiver]

Take the following with a huge grain of salt since I'm anti-EF, and anti-NUnit... (Go NoSQL and xUnit!)

When I first tried NUnit many years, I found it so disconcerting that it would only create one instance of the test class, run the setup, run the various tests sequentially, then run the teardown. This seemed completely against the idea of object oriented programming where setup is supposed to happen in the constructor, and teardown in the destructor -- or IDisposable.Dispose() in the CLR. It was one of the major points that drove me to xUnit. The oher was the sequential runs. (The other was that I was working side by side with Brad Wilson and he understood all my NUnit and MSTest woes. But that's neither here nor there.)

Anyway, apparently NUnit now supports what xUnit had natively: parallel execution of test. You mentioned that you had other tests. Are you sure that the other tests are not running in parallel and stepping over the data in this particular test?

I don't use EF day-to-day. (I was an early adopter and got burned by v1.0, then v4.0, and then v4.3. Never again!) On a high level, my understanding is that EF object instances have some kind of synchronization token associated with them so that the next time an attempt to save or delete happens, EF can detect if the data in database has changed since the last time the object was read from the database. Are you sure that you have not overridden the implementation of that token?

Have you looked at the exceptions, Entries list to see which objects EF things have changed?

Handling Concurrency Conflicts - EF Core

Managing conflicts when the same data is updated concurrently with Entity Framework Core

learn.microsoft.com