Efcore.pg: Database operation expected to affect 1 row(s) but actually affected 0 row(s)

[NYMEZIDE]

Microsoft.EntityFrameworkCore 3.0.0-preview7.19362.6
Npgsql.EntityFrameworkCore.PostgreSQL 3.0.0-preview7

I have 2 classes:

public class Parent 
{
        private Parent () { }
        public Guid ParentId { get; private set; }
        public ICollection<Child> Childs { get; private set; }
        public Guid ConcurrencyToken { get; private set; }

        public void Add(Child child)
        {
              Childs.Add(child);
              ConcurrencyToken = Guid.NewGuid();
        }
}

public class Child
{
        private Child() { }
        public Guid ChildId { get; private set; }
        public string Name { get; private set; }
        public Guid ParentId { get; private set; }
}

and the default implementation of a repository pattern:

public async Task<Parent > Get(Guid id, CancellationToken cancellationToken)
{
      return await _context.Parents
           .Include(p => p.Childs) // including or disable including - no matter
           .SingleOrDefaultAsync(n => n.ParentId == id, cancellationToken);
}

public async Task Save(Parent parent )
{
      var entry = _context.Entry(parent);

      if (entry.State == EntityState.Detached)
            _context.Parents.Add(parent);

     await _context.SaveChangesAsync();
}

If I create a new Parent instance and insert a new Child instance I get two sql inserts to the database(INSERT one Parent and INSERT one Child) which is all right, but when I append one more Child to the existing Parent instance with already/or not filled Childs I get the following error:

Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: 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.
   at Npgsql.EntityFrameworkCore.PostgreSQL.Update.Internal.NpgsqlModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple`2 parameters, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple`2 parameters, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

Created SQL-statements (from logs):

"UPDATE \"Parent\" SET \"ConcurrencyToken\" = @p0
WHERE \"ParentId\" = @p1 AND \"ConcurrencyToken\" = @p2;

UPDATE \"Child\" SET \"Name \" = @p3
WHERE \"ChildId \" = @p4;"

It tries to update the last appended Child instead of inserting it.

Created example Repository for reproduction:
https://github.com/NYMEZIDE/ConsoleNpgsqlBug

[ZekeLu]

The reason is that you set an explicit value for the primary key of the child object:

C# public Child(string name) { ChildId = Guid.NewGuid(); // remove this line to address the issue Name = name; }

By convention, Guid is setup to have values generated on add:

By convention, non-composite primary keys of type short, int, long, or Guid will be setup to have values generated on add. (see Generated Values)

And there is a breaking change introduced by EF Core 3.0-preview 3 : DetectChanges honors store-generated key values

New behavior

Starting with EF Core 3.0, if an entity is using generated key values and some key value is set, then the entity will be tracked in the Modified state.

[NYMEZIDE]

Generate of GUID happened on the front-end side.

Why should I trust id generation to EF Core ?

Object Child - its NEW entry. NOT Tracking on GET method, why result SQL query contains UPDATE for NEW entry?

And if ChildId been not Guid, for example, string type? EF Core will be able to create STRING value?

[ZekeLu]

1

Generate of GUID happened on the front-end side.

If you want to generate the id yourself, you can explicitly configure the key properties to not use generated values:

--- a/ConsoleNpgsqlBug/MyDbContext.cs
+++ b/ConsoleNpgsqlBug/MyDbContext.cs
@@ -52,7 +52,7 @@ namespace ConsoleNpgsqlBug
             {
                 builder.ToTable("Child");
                 builder.HasKey(p => p.ChildId);
-                builder.Property(p => p.ChildId).IsRequired();
+                builder.Property(p => p.ChildId).IsRequired().ValueGeneratedNever();
                 builder.Property(p => p.ParentId).IsRequired();
                 builder.Property(p => p.Name).IsRequired();
             });

The document about this breaking change is very helpful. You should read it.

2

And if ChildId been not Guid, for example, string type? EF Core will be able to create STRING value?

I have tested this case. If ChildId is of type string and its value is not set explicitly, it will result in this exception:

System.InvalidOperationException: Unable to track an entity of type 'Child' because primary key property 'ChildId' is null.

Then I configure the key properties to use generated values:

diff --git a/ConsoleNpgsqlBug/Child.cs b/ConsoleNpgsqlBug/Child.cs
index c462292..d714ad7 100644
--- a/ConsoleNpgsqlBug/Child.cs
+++ b/ConsoleNpgsqlBug/Child.cs
@@ -7,14 +7,13 @@ namespace ConsoleNpgsqlBug
     public class Child
     {
         private Child() { }
-        public Guid ChildId { get; private set; }
+        public string ChildId { get; private set; }
         public string Name { get; private set; }

         public Guid ParentId { get; private set; }

         public Child(string name)
         {
-            ChildId = Guid.NewGuid();
             Name = name;
         }
     }
diff --git a/ConsoleNpgsqlBug/MyDbContext.cs b/ConsoleNpgsqlBug/MyDbContext.cs
index 99d9851..a682057 100644
--- a/ConsoleNpgsqlBug/MyDbContext.cs
+++ b/ConsoleNpgsqlBug/MyDbContext.cs
@@ -52,7 +52,7 @@ namespace ConsoleNpgsqlBug
             {
                 builder.ToTable("Child");
                 builder.HasKey(p => p.ChildId);
-                builder.Property(p => p.ChildId).IsRequired();
+                builder.Property(p => p.ChildId).IsRequired().ValueGeneratedOnAdd();
                 builder.Property(p => p.ParentId).IsRequired();
                 builder.Property(p => p.Name).IsRequired();
             });

And yes, EF Core will generate the key. And the key happens to be a GUID:

info: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@p0='c959b500-0602-4978-a8bd-03fa0fa16fb7' (Nullable = false), @p1='child 2' (Nullable = false), @p2='afc218dc-e684-43dc-a2fa-f8cc7b9dd054'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "Child" ("ChildId", "Name", "ParentId")
      VALUES (@p0, @p1, @p2);

[NYMEZIDE]

Thanks. I solved via .ValueGeneratedNever()
I will waiting release versions and reading breaking-changes.

[ZekeLu]

Congratulations! If you don't have any further questions, please close this issue. Thank you!

[crgarcia12]

Things like this make me want to stop using EF. The benefits are quickly gone by the amount of hacks you need to do, and all the disconnected code that you cannot easily find in a project but that heavily affects the behavior of your code.

[aklaffenboeck]

@ZekeLu: How can I buy you a beer?

[Asim-A]

1

Generate of GUID happened on the front-end side.

If you want to generate the id yourself, you can explicitly configure the key properties to not use generated values:

--- a/ConsoleNpgsqlBug/MyDbContext.cs
+++ b/ConsoleNpgsqlBug/MyDbContext.cs
@@ -52,7 +52,7 @@ namespace ConsoleNpgsqlBug
             {
                 builder.ToTable("Child");
                 builder.HasKey(p => p.ChildId);
-                builder.Property(p => p.ChildId).IsRequired();
+                builder.Property(p => p.ChildId).IsRequired().ValueGeneratedNever();
                 builder.Property(p => p.ParentId).IsRequired();
                 builder.Property(p => p.Name).IsRequired();
             });

The document about this breaking change is very helpful. You should read it.

2

And if ChildId been not Guid, for example, string type? EF Core will be able to create STRING value?

I have tested this case. If ChildId is of type string and its value is not set explicitly, it will result in this exception:

System.InvalidOperationException: Unable to track an entity of type 'Child' because primary key property 'ChildId' is null.

Then I configure the key properties to use generated values:

diff --git a/ConsoleNpgsqlBug/Child.cs b/ConsoleNpgsqlBug/Child.cs
index c462292..d714ad7 100644
--- a/ConsoleNpgsqlBug/Child.cs
+++ b/ConsoleNpgsqlBug/Child.cs
@@ -7,14 +7,13 @@ namespace ConsoleNpgsqlBug
     public class Child
     {
         private Child() { }
-        public Guid ChildId { get; private set; }
+        public string ChildId { get; private set; }
         public string Name { get; private set; }

         public Guid ParentId { get; private set; }

         public Child(string name)
         {
-            ChildId = Guid.NewGuid();
             Name = name;
         }
     }
diff --git a/ConsoleNpgsqlBug/MyDbContext.cs b/ConsoleNpgsqlBug/MyDbContext.cs
index 99d9851..a682057 100644
--- a/ConsoleNpgsqlBug/MyDbContext.cs
+++ b/ConsoleNpgsqlBug/MyDbContext.cs
@@ -52,7 +52,7 @@ namespace ConsoleNpgsqlBug
             {
                 builder.ToTable("Child");
                 builder.HasKey(p => p.ChildId);
-                builder.Property(p => p.ChildId).IsRequired();
+                builder.Property(p => p.ChildId).IsRequired().ValueGeneratedOnAdd();
                 builder.Property(p => p.ParentId).IsRequired();
                 builder.Property(p => p.Name).IsRequired();
             });

And yes, EF Core will generate the key. And the key happens to be a GUID:

info: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@p0='c959b500-0602-4978-a8bd-03fa0fa16fb7' (Nullable = false), @p1='child 2' (Nullable = false), @p2='afc218dc-e684-43dc-a2fa-f8cc7b9dd054'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "Child" ("ChildId", "Name", "ParentId")
      VALUES (@p0, @p1, @p2);

Thank you. 💯