c# - EF Core，SaveChages 在更新查询抛出 DbUdpateException 时执行意外查询

Question

当我尝试更新外键并且在 EF 中调用该方法时它不存在SaveChanges 显然会为我的数据库中的所有表创建选择，这会导致我的服务器内存触发。

您可以通过在调用之前验证外键是否存在来解决问题SaveChanges，但是如果我的代码中的其他地方出现意外错误，我仍然会遇到潜在的问题。

当外键正确时：

[11:50:38 DBG] Opening connection to database 'InvoitradeIdentity' on server 'localhost\SQLEXPRESS'.
[11:50:38 DBG] Opened connection to database 'InvoitradeIdentity' on server 'localhost\SQLEXPRESS'.
[11:50:38 DBG] Beginning transaction with isolation level 'Unspecified'.
[11:50:38 DBG] Began transaction with isolation level 'ReadCommitted'.
[11:50:38 DBG] Creating DbCommand for 'ExecuteReader'.
[11:50:38 DBG] Created DbCommand for 'ExecuteReader' (1ms).
[11:50:38 DBG] Executing DbCommand [Parameters=[@p2='3', @p3='0x0000000000023A52' (Nullable = false) (Size = 8), @p0='2021-09-22T11:50:38.8851528-03:00' (Nullable = true) (DbType = DateTime), @p1='1'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [UserData] SET [dateEdited] = @p0, [userStatusId] = @p1
WHERE [userId] = @p2 AND [currentTimestamp] = @p3;
SELECT [currentTimestamp]
FROM [UserData]
WHERE @@ROWCOUNT = 1 AND [userId] = @p2;
[11:50:38 INF] Executed DbCommand (6ms) [Parameters=[@p2='3', @p3='0x0000000000023A52' (Nullable = false) (Size = 8), @p0='2021-09-22T11:50:38.8851528-03:00' (Nullable = true) (DbType = DateTime), @p1='1'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [UserData] SET [dateEdited] = @p0, [userStatusId] = @p1
WHERE [userId] = @p2 AND [currentTimestamp] = @p3;
SELECT [currentTimestamp]
FROM [UserData]
WHERE @@ROWCOUNT = 1 AND [userId] = @p2;
[11:50:38 DBG] A data reader was disposed.
[11:50:38 DBG] Committing transaction.
[11:50:38 DBG] Committed transaction.
[11:50:38 DBG] Closing connection to database 'InvoitradeIdentity' on server 'localhost\SQLEXPRESS'.
[11:50:38 DBG] Closed connection to database 'InvoitradeIdentity' on server 'localhost\SQLEXPRESS'.
[11:50:38 DBG] Disposing transaction.
[11:50:38 DBG] The 'UserData' entity with key '{UserId: 3}' tracked by 'InvoitradeIdentityContext' changed state from 'Modified' to 'Unchanged'.
[11:50:38 DBG] SaveChanges completed for 'InvoitradeIdentityContext' with 1 entities written to the database.

当外键不正确时：

[11:52:48 DBG] Opening connection to database 'InvoitradeIdentity' on server 'localhost\SQLEXPRESS'.
[11:52:48 DBG] Opened connection to database 'InvoitradeIdentity' on server 'localhost\SQLEXPRESS'.
[11:52:48 DBG] Beginning transaction with isolation level 'Unspecified'.
[11:52:48 DBG] Began transaction with isolation level 'ReadCommitted'.
[11:52:48 DBG] Creating DbCommand for 'ExecuteReader'.
[11:52:48 DBG] Created DbCommand for 'ExecuteReader' (1ms).
[11:52:48 DBG] Executing DbCommand [Parameters=[@p3='1', @p4='0x0000000000015FEA' (Nullable = false) (Size = 8), @p0='2021-09-22T11:52:48.2542146-03:00' (Nullable = true) (DbType = DateTime), @p1='system' (Size = 150), @p2='10'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [UserData] SET [dateEdited] = @p0, [editedBy] = @p1, [userStatusId] = @p2
WHERE [userId] = @p3 AND [currentTimestamp] = @p4;
SELECT [currentTimestamp]
FROM [UserData]
WHERE @@ROWCOUNT = 1 AND [userId] = @p3;
[11:52:48 ERR] Failed executing DbCommand (6ms) [Parameters=[@p3='1', @p4='0x0000000000015FEA' (Nullable = false) (Size = 8), @p0='2021-09-22T11:52:48.2542146-03:00' (Nullable = true) (DbType = DateTime), @p1='system' (Size = 150), @p2='10'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [UserData] SET [dateEdited] = @p0, [editedBy] = @p1, [userStatusId] = @p2
WHERE [userId] = @p3 AND [currentTimestamp] = @p4;
SELECT [currentTimestamp]
FROM [UserData]
WHERE @@ROWCOUNT = 1 AND [userId] = @p3;
[11:52:48 DBG] Disposing transaction.
[11:52:48 DBG] Closing connection to database 'InvoitradeIdentity' on server 'localhost\SQLEXPRESS'.
[11:52:48 DBG] Closed connection to database 'InvoitradeIdentity' on server 'localhost\SQLEXPRESS'.
[11:52:48 DBG] Compiling query expression:
'DbSet<Address>()'
[11:52:48 DBG] Generated query execution expression:
'queryContext => new SingleQueryingEnumerable<Address>(
    (RelationalQueryContext)queryContext,
    RelationalCommandCache.SelectExpression(
        Projection Mapping:
            EmptyProjectionMember -> Dictionary<IProperty, int> { [Property: Address.AddressId (int) Required PK AfterSave:Throw ValueGenerated.OnAdd, 0], [Property: Address.Apartment (string) MaxLength(5), 1], [Property: Address.City (string) MaxLength(250), 2], [Property: Address.CountryId (Nullable<int>) FK Index, 3], [Property: Address.Number (string) MaxLength(30), 4], [Property: Address.Observations (string), 5], [Property: Address.PostalCode (string) MaxLength(30), 6], [Property: Address.State (string) MaxLength(250), 7], [Property: Address.Street (string) MaxLength(250), 8], }
        SELECT a.addressId, a.apartment, a.city, a.countryId, a.number, a.observations, a.postalCode, a.state, a.street
        FROM Address AS a),
    Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, Address>,
    Invoitrade.Identity.Data.Models.InvoitradeIdentityContext,
    False,
    True
)'
[11:52:48 DBG] Creating DbCommand for 'ExecuteReader'.
[11:52:48 DBG] Created DbCommand for 'ExecuteReader' (0ms).
[11:52:48 DBG] Opening connection to database 'InvoitradeIdentity' on server 'localhost\SQLEXPRESS'.
[11:52:48 DBG] Opened connection to database 'InvoitradeIdentity' on server 'localhost\SQLEXPRESS'.
[11:52:48 DBG] Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [a].[addressId], [a].[apartment], [a].[city], [a].[countryId], [a].[number], [a].[observations], [a].[postalCode], [a].[state], [a].[street]
FROM [Address] AS [a]
[11:52:48 INF] Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [a].[addressId], [a].[apartment], [a].[city], [a].[countryId], [a].[number], [a].[observations], [a].[postalCode], [a].[state], [a].[street]
FROM [Address] AS [a]
[11:52:48 DBG] Context 'InvoitradeIdentityContext' started tracking 'Address' entity with key '{AddressId: 1}'.
[11:52:48 DBG] Context 'InvoitradeIdentityContext' started tracking 'Address' entity with key '{AddressId: 2}'.
[11:52:48 DBG] Context 'InvoitradeIdentityContext' started tracking 'Address' entity with key '{AddressId: 3}'.
[11:52:48 DBG] Context 'InvoitradeIdentityContext' started tracking 'Address' entity with key '{AddressId: 4}'.
[11:52:48 DBG] Context 'InvoitradeIdentityContext' started tracking 'Address' entity with key '{AddressId: 5}'.
[11:52:48 DBG] Context 'InvoitradeIdentityContext' started tracking 'Address' entity with key '{AddressId: 6}'.
[11:52:48 DBG] Context 'InvoitradeIdentityContext' started tracking 'Address' entity with key '{AddressId: 7}'.
[11:52:48 DBG] Context 'InvoitradeIdentityContext' started tracking 'Address' entity with key '{AddressId: 8}'.
[11:52:48 DBG] A data reader was disposed.
[11:52:48 DBG] Closing connection to database 'InvoitradeIdentity' on server 'localhost\SQLEXPRESS'.
[11:52:48 DBG] Closed connection to database 'InvoitradeIdentity' on server 'localhost\SQLEXPRESS'.

当我的上下文的所有表格时，这种情况会继续

最后：

    [12:30:26 ERR] An exception occurred in the database while saving changes for context type 'Invoitrade.Identity.Data.Models.InvoitradeIdentityContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_UserData_UserStatus". The conflict occurred in database "InvoitradeIdentity", table "dbo.UserStatus", column 'userStatusId'.
The statement has been terminated.
at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__169_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
ClientConnectionId:2f91564c-4f7b-4e68-a564-78a097791eb7

标签： c#sql-serverentity-framework