EFCore 7.0 Bug - Invalid Operation Exception RE "Temporary Value"

[TonyValenti]

File a bug

Hi All,
I just upgraded my app to EF7.0 and I'm bumping into what I believe is new bug.

Related to my investigation, I've noticed a few things that either don't seem quite right or aren't quite what I would expect.

Here is my scenario. I am inserting:

• 1000 rows
• Using 1 "SaveChanges"
• Into 1 table
• Which has 57 columns (most of which are nvarchar(MAX))
• And uses Owned Entities
• And has a unique constraint on one of the string columns
• The primary key is a DB-generated long named Row which is never set in my code.

Here are some odd things I've noticed:

• AddRange takes 4-6 seconds!
• Per SQL Profiler, EFCore seems to split the command into multiple sp_executesql commands.
• Some commands start with SET IMPLICIT_TRANSACTIONS OFF;SET NOCOUNT ON; and some start with just SET NOCOUNT ON;
• Some commands INSERT INTO and others MERGE
• EFCore errors with:

The property 'ExpenseActivityImportJob.Row' has a temporary value while attempting to change the entity's state to 'Unchanged'. Either set a permanent value explicitly, or ensure that the database is configured to generate values for this property.

• The database is configured to generate values for that column and I never set it via code.
• Even though I get the above error, 900ish rows actually get inserted into the database (I thought it should be all or nothing?)

What should I do next in terms of troubleshooting this?

Something else I thought of:

• When inserting multiple rows at once, it is conceivable that there will be many shared values between the different rows (at least there are in my data). I think the generated SQL could be more efficient if it deduplicated parameter values before using them. For example, maybe @p1522 gets used everywhere a certain value comes into play. I suspect this would reduce network latency and the size of the SQL commands sent to SQL server.

Include your code

I can't at this time.

Include stack traces

   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntry.SetEntityState(EntityState oldState, EntityState newState, Boolean acceptChanges, Boolean modifyProperties)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntry.SetEntityState(EntityState entityState, Boolean acceptChanges, Boolean modifyProperties, Nullable`1 forceStateWhenUnknownKey, Nullable`1 fallbackState)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntry.AcceptChanges()
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.AcceptAllChanges(IReadOnlyList`1 changedEntries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager stateManager, Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at UniversalMigrator.Storage.MigrationDbContext.SaveChanges(Boolean acceptAllChangesOnSuccess) in C:\Users\TonyV\source\repos\MediatedCommunications\UniversalMigrator\UniversalMigrator.Storage\Context\MigrationDbContext.cs:line 127
   at Framework.Storage.DbContextExtensions.<SaveChangesFastAsync>d__0.MoveNext() in C:\Users\TonyV\source\repos\MediatedCommunications\Framework\Framework.Storage\Microsoft\EntityFrameworkCore\DbContextExtensions.cs:line 14
   at UniversalMigrator.Agents.DataflowExtractorAgent.<>c__DisplayClass26_0.<<SaveActionAsync>b__2>d.MoveNext() in C:\Users\TonyV\source\repos\MediatedCommunications\UniversalMigrator\UniversalMigrator\Agents.Bases\DataflowExtractorAgent.cs:line 137

Include provider and version information

EF Core version: 7.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Operating system: Win11
IDE: Visual Studio 2022 17.4

[roji]

Very probable duplicate of #29565, which is being fixed for 7.0.1. As a workaround, configure your target table with HasTriggers as described in the docs.

[TonyValenti]

Hi @roji - Thank you so much for the reply. This looks like it may be the issue I'm experiencing, however, the suggested workaround does not alleviate it.

I updated my "MapTable" method as demonstated below:

            static EntityTypeBuilder<T> MapTable<T>(ModelBuilder This) where T : ImportJob {
                var ret = This.Entity<T>()
                    .ToTable(
                        MigrationDbContextTables.ForType<T>()
                        , x => x.HasTrigger("__NONE")  // <------ Added This
                    )
                    ;

                return ret;

            }

And I still get the error.

I did some testing and started shrinking my batch sizes. I was even getting this error with batch sizes as low as 50! At 25 items per save, the error seems to go away.

I'm happy to wait till 7.0.1 is released but I'd like to help determine if my issue is the same bug or a similar one.

[roji]

@TonyValenti can you please post the SQL sent by EF for the failing operation? Preferably before and after adding the HasTrigger configuration, to make sure it's properly taking effect.

[TonyValenti]

@roji - How should I collect the SQL? Is a SQL Profiler trace acceptable?

Also, can you emailme? I'll send you the data that way. The trace may have sensitive information that I don't want to publish publicly.

[TonyValenti]

Also, when I add a watch on modelBuilder.Entity<ExpenseActivityImportJob>().Metadata I see this:

which makes me believe that the trigger is being added.

[roji]

The preferred way to collect SQL is to turn on EF's logging and send that; be sure to send the SQL fragment which corresponds to the exception you're getting.

My email is shown on my Github profile.

When inserting multiple rows at once, it is conceivable that there will be many shared values between the different rows (at least there are in my data). I think the generated SQL could be more efficient if it deduplicated parameter values before using them. For example, maybe @p1522 gets used everywhere a certain value comes into play. I suspect this would reduce network latency and the size of the SQL commands sent to SQL server.

See #28768 for a discussion around this.

[roji]

@TonyValenti there's a very good chance that this is a duplicate of #29502, which is already fixed in our main branch.

Can you please try daily build 8.0.0-alpha.1.22567.1 or above, and confirm whether this solves the problem for you? Instructions on installing daily builds is here.

[TonyValenti]

Hi @roji - Thanks for being patient with me while I test.
I can confirm that this issue does not appear after upgrading to .NET 8.0 alpha.

[roji]

Thanks for confirming!

[TonyValenti]

I am curious:
Why was this closed as "not planned" when actually, it is planned and implemented and fixed?

[ajcvickers]

@TonyValenti Because it's a duplicate of #29502