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;
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.