I am new to EF and am trying to work out why this simple update of a Transaction record generates 9 identical select queries for the record and then the update query. (I see this debugging in the output window in VS) When I check ChangeTracker, entries only has one record for the transaction being updated.

_appDbContext.Transactions.Update(transaction);
  var entries = _appDbContext.ChangeTracker.Entries();
  await _appDbContext.SaveChangesAsync();

I changed the update to this (no change to the queries)

_appDbContext.Entry(transaction).State = EntityState.Modified;

This is the output (showing 3 of 9 selects then the update)

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[@__p_0='54626'], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [t].[Id], [t].[Amount], [t].[Amtforsgl], [t].[Description], [t].[EmployeePayId], [t].[LineSeq], [t].[Lumpcode], [t].[PayitemId], [t].[Qty], [t].[Transactiontype] FROM [Transactions] AS [t] WHERE [t].[Id] = @__p_0

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[@__p_0='54626'], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [t].[Id], [t].[Amount], [t].[Amtforsgl], [t].[Description], [t].[EmployeePayId], [t].[LineSeq], [t].[Lumpcode], [t].[PayitemId], [t].[Qty], [t].[Transactiontype] FROM [Transactions] AS [t] WHERE [t].[Id] = @__p_0

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[@__p_0='54626'], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [t].[Id], [t].[Amount], [t].[Amtforsgl], [t].[Description], [t].[EmployeePayId], [t].[LineSeq], [t].[Lumpcode], [t].[PayitemId], [t].[Qty], [t].[Transactiontype] FROM [Transactions] AS [t] WHERE [t].[Id] = @__p_0

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (12ms) [Parameters=[@p9='54626', @p0='800.00' (Precision = 18) (Scale = 2), @p1='0' (Precision = 18) (Scale = 2), @p2='Ordinary Time' (Size = 4000), @p3='22274', @p4='0', @p5=NULL (Size = 4000), @p6='3770', @p7='40.0000' (Precision = 18) (Scale = 4), @p8='1'], CommandType='Text', CommandTimeout='30'] SET IMPLICIT_TRANSACTIONS OFF; SET NOCOUNT ON; UPDATE [Transactions] SET [Amount] = @p0, [Amtforsgl] = @p1, [Description] = @p2, [EmployeePayId] = @p3, [LineSeq] = @p4, [Lumpcode] = @p5, [PayitemId] = @p6, [Qty] = @p7, [Transactiontype] = @p8 OUTPUT 1 WHERE [Id] = @p9;

I am new to EF and am trying to work out why this simple update of a Transaction record generates 9 identical select queries for the record and then the update query. (I see this debugging in the output window in VS) When I check ChangeTracker, entries only has one record for the transaction being updated.

_appDbContext.Transactions.Update(transaction);
  var entries = _appDbContext.ChangeTracker.Entries();
  await _appDbContext.SaveChangesAsync();

I changed the update to this (no change to the queries)

_appDbContext.Entry(transaction).State = EntityState.Modified;

This is the output (showing 3 of 9 selects then the update)

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[@__p_0='54626'], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [t].[Id], [t].[Amount], [t].[Amtforsgl], [t].[Description], [t].[EmployeePayId], [t].[LineSeq], [t].[Lumpcode], [t].[PayitemId], [t].[Qty], [t].[Transactiontype] FROM [Transactions] AS [t] WHERE [t].[Id] = @__p_0

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[@__p_0='54626'], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [t].[Id], [t].[Amount], [t].[Amtforsgl], [t].[Description], [t].[EmployeePayId], [t].[LineSeq], [t].[Lumpcode], [t].[PayitemId], [t].[Qty], [t].[Transactiontype] FROM [Transactions] AS [t] WHERE [t].[Id] = @__p_0

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[@__p_0='54626'], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [t].[Id], [t].[Amount], [t].[Amtforsgl], [t].[Description], [t].[EmployeePayId], [t].[LineSeq], [t].[Lumpcode], [t].[PayitemId], [t].[Qty], [t].[Transactiontype] FROM [Transactions] AS [t] WHERE [t].[Id] = @__p_0

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (12ms) [Parameters=[@p9='54626', @p0='800.00' (Precision = 18) (Scale = 2), @p1='0' (Precision = 18) (Scale = 2), @p2='Ordinary Time' (Size = 4000), @p3='22274', @p4='0', @p5=NULL (Size = 4000), @p6='3770', @p7='40.0000' (Precision = 18) (Scale = 4), @p8='1'], CommandType='Text', CommandTimeout='30'] SET IMPLICIT_TRANSACTIONS OFF; SET NOCOUNT ON; UPDATE [Transactions] SET [Amount] = @p0, [Amtforsgl] = @p1, [Description] = @p2, [EmployeePayId] = @p3, [LineSeq] = @p4, [Lumpcode] = @p5, [PayitemId] = @p6, [Qty] = @p7, [Transactiontype] = @p8 OUTPUT 1 WHERE [Id] = @p9;

• sql-server
• entity-framework-core
• ef-core-9.0

• 2 Do not use Update, just change property of loaded entity. Also show generated SQL and specify exact EF version. – Svyatoslav Danyliv Commented Mar 4 at 7:44
• Sounds weird. We need a minimal reproducible example to see what happens. Maybe lazy loading is involved? Also, please add a [ef-core-xx] tag for the EF version in use + the database provider. – Gert Arnold Commented Mar 4 at 8:01
• You're not adding any new information. We need a minimal reproducible example. There's nothing in the tiny code snippet that gives any clue. – Gert Arnold Commented Mar 5 at 22:09
• Appreciate the assistance. While trying to create a reproducible example I found the issue was my clumsy diff logging in my SaveChangesAsync (Exactly what Steve Py suggested) – Tony Commented Mar 6 at 3:56

1 Answer 1

The fact that there are 9 calls and that the transaction class has 9 properties outside of the PK hints that you might have some form of poorly implemented change diff logging wired up in a DbContext SaveChangesAsync() override or some other code hooked into the DbContext. This could be code specific to Transaction or a generic method /w reflection or some other implementation iterating through properties to get original values one at a time, triggering a query for each property instead of simply reading the original state once (or relying on the change tracking original values)

For instance something like:

// Note leaving this as an IQueryable without materializing it with `First()` etc.
var originalTransaction = this.Transactions.AsNoTracking().Where(x => x.Id == transaction.Id); 
var audit = new Audit();
audit.Fields.Add(new AuditField
{ 
   Name = nameof(transaction.Amount);
   OriginalValue = originalTransaction.First().Amount; //Executes an SQL call. Once per property read this way.
   UpdatedValue = trasation.Amount;
});
// repeated for each column in Transaction.

This could take a lot of different forms including iterating over the columns via reflection. Repeated calls to an AsNoTracking() query will each generate a SELECT call against the database. The fix to something like the above would be to capture the single call to get the originalTransaction one time before attempting to read the properties.

Feel free to post any custom code in the DbContext or hooks if you need assistance confirming what code might be responsible.