EF Core Postgres DbUpdateConcurrencyException when updating detached data

Question

I am trying to update data on my Postgresdatabase in a MSTest unit test. Before each test I delete all books and create a few new ones to ensure proper data for the test:

        private static Book CreateDefaultBook(int i)
        {
            return new Book
            {
                Title = TitlePrefix + i,
                Description = DescriptionPrefix + i
            };
        }

        [TestInitialize]
        public void InitializeContext()
        {
            using (var context = new MampfContext(DbContextOptions))
            {
                foreach (var contextBook in context.Books)
                {
                    context.Entry(contextBook).State = EntityState.Deleted;
                }

                context.SaveChanges();

                for (int i = 1; i <= NumberOfBooks; i++)
                {
                    context.Books.Add(CreateDefaultBook(i));
                }

                context.SaveChanges();
            }
        }

This works fine. Then I try to update data in a test:

        [TestMethod]
        public void UpdateBookTest()
        {
            Book book = null;
            using (var context = new MampfContext(DbContextOptions))
            {
                book = context.Books.FirstOrDefault(r => r.Title == TitlePrefix + 1);
                Assert.IsNotNull(book);
            }

            book.Description = "Changed";

            using (var context = new MampfContext(DbContextOptions))
            {
                var entry = context.Entry(book);
                entry.State = EntityState.Modified;
                context.SaveChanges(); //Exception!
            }

            using (var context = new MampfContext(DbContextOptions))
            {
                var updatedBook = context.Books.FirstOrDefault(r => r.Title == TitlePrefix + 1);
                Assert.IsNotNull(updatedBook);
                Assert.AreEqual("Changed", updatedBook.Description);
            }
        }

I do this in three steps. First I get a entity. Then I change it, while it is detached from the context. Finally I attach the book to a new context and set the state to modified and try to save the changes. But there I get a DbUpdateConcurrencyException with the message:

"Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions."

I am using the optimistic concurrency provided by postgres calling UseXminAsConcurrencyToken() in OnModelCreating on my Book Entity as described on their Website https://www.npgsql.org/efcore/modeling/concurrency.html

How can I fix this problem?

Answer 1

When you call UseXminAsConcurrencyToken, this sets up an xmin property on your entity which will hold the value of the xmin column in PostgreSQL (which is auto-generated etc.). Since your actual Book CLR type doesn't have an xmin member, a shadow property is configured; this means that the value of the column is stored inside the context and not on the CLR instance.

Because of this, when you move the loaded CLR instance from one context to another, that value is lost - because it's stored inside the context. So when you attempt to update it in the new context, the value defaults to 0 - which is incorrect - and you get the exception.

To work around this, you can: 1. Simply define an xmin property of type uint on your Book class. This will cause it to be used instead of a shadow property, and it will therefore persist across instances. 2. Reload the entity in the new context, in order to reload the value.