EF Core Postgres DbUpdateConcurrencyException when updating detached data

.net-core c# entity-framework-core npgsql postgresql

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?

1
1
3/22/2020 4:06:39 PM

Accepted Answer

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.

1
3/29/2020 6:20:45 PM

Popular Answer

It may to be an implementation problem in the Npgsql provider.

Did you check your Provider / EF Core / .NET Core versions? https://github.com/npgsql/efcore.pg/issues/1059

If you don't get it running with krisztiankocsis' and roji's hint, you need to start tracing out all queries against the database and compare the SQL statements and the EF Core model state.

Alternatively, maybe changing the database vendor may be an option?

BTW, this older post could give you some more hints: https://github.com/npgsql/efcore.pg/issues/19



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow