The following code example shows how to solve the error Failed executing DbCommand Update in Asp.Net Core and EF Entity Framework.
Task: my Asp.Net Core 2 MVC application hangs up regularly when a record is to be written.
This is a simple counter that increases website access.
The error message in the log file of the Asp Core application is:
fail: Microsoft.EntityFrameworkCore.Database.Command[20102] Failed executing DbCommand (45ms) [Parameters=[@p1='?', @p0='?'], CommandType='Text', CommandTimeout='30'] .. UPDATE [tbl_Notes] SET .. System.Data.SqlClient.SqlException (0x80131904): Timeout expired. |
And this comes the error:
fail: Microsoft.EntityFrameworkCore.Update[10000] An exception occurred in the database while saving changes for context type |
Log file of asp application
Error digest where the code gets stuck.
1: Update a record
fail: Microsoft.EntityFrameworkCore.Database.Command[20102] Failed executing DbCommand (45ms) [Parameters=[@p1='?', @p0='?'], CommandType='Text', CommandTimeout='30'] SET NOCOUNT ON; UPDATE [tbl_Notes] SET [sumViews] = @p0 WHERE [IDNote] = @p1; SELECT @@ROWCOUNT; System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): Der Wartevorgang wurde abgebrochen at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__108_0(Task`1 result) |
2.Update a record
Error Number:-2,State:0,Class:11 fail: Microsoft.EntityFrameworkCore.Update[10000] An exception occurred in the database while saving changes for context type 'Readdy.Data.ApplicationDbContext'. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: Der Wartevorgang wurde abgebrochen |
Excerpt from the error source in the source files:
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Counter_Logger.<counter_of_View_erhoehen>d__0.MoveNext() in D:\Programmierung\Web\Readdy\Readdy\Readdy\_classes\Counter_Logger.cs:line 21 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Readdy.Controllers.NotesController.<Details>d__6.MoveNext() in D:\Programmierung\Web\Readdy\Readdy\Readdy\Controllers\NotesController.cs:line 322 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() |
Solution:
You can enforce simple storage operations with AcceptAllChanges.
To do this, set the Save parameter: AcceptAllChanges in Entity Framework to True
await EF_Model.dbContext.SaveChangesAsync(true); |
C # code example with Asp.Net Core 2 and Entity Framework.
Explanation.
While the method is fetching and changing data, the record may be opened by another page.
For simple meters and non-redundant loggers, simple saving without a transaction check is sufficient
public static async Task<int?> counter_of_View_erhoehen(long IDUser, long IDNote = 0) { //-------------< logViewCounter_erhoehen() >------------- //< Sum ViewLogs > NoteModel note = await EF_Model.dbContext.tbl_Notes.SingleOrDefaultAsync(m => m.IDNote == IDNote); if (note == null) return 0;
if (note.sumViews == null) note.sumViews = 0; note.sumViews += 1; await EF_Model.dbContext.SaveChangesAsync(true); //</ Sum ViewLogs >
return note.sumViews; //-------------</ logViewCounter_erhoehen() >------------- } |
Screenshot
Simply insert the parameter (true) in the code to force the saving