I have a little specific update scenario. In database table, we have to update entity - just one property. But the problem is that each record has relatively large binary object (varbinary - few megabytes at least). There is issue in Azure environment when only one property of such entity has to be updated but we always get error when calling _repository.GetById(id). (There is no issue if we read this binary object inside entity, write it to temp folder location so that client application gets FileDto object to download it).
How can we implement such update scenario? Thank you for suggestions.
7 Answer(s)
-
0
What error?
-
0
I will post it tomorrow. But anway: There is no problem if query is without binary data filed included. On local installations, there is no such problem, but execution time is really slow.
-
0
ERROR 2019-04-26 04:39:02,652 [23 ] Mvc.ExceptionHandling.AbpExceptionFilter - An error occurred while updating the entries. See the inner exception for details. 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: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__122_0(Task
1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() at System.Threading.Tasks.Task.<>c.<.cctor>b__278_1(Object obj) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) --- End of stack trace from previous location where exception was thrown --- at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot) --- End of stack trace from previous location where exception was thrown --- at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken) --- End of inner exception stack trace --- at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple2 parameters, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList1 entriesToSave, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken) at Abp.EntityFrameworkCore.AbpDbContext.SaveChangesAsync(CancellationToken cancellationToken) at Abp.Zero.EntityFrameworkCore.AbpZeroCommonDbContext3.SaveChangesAsync(CancellationToken cancellationToken) at Abp.EntityFrameworkCore.Uow.EfCoreUnitOfWork.SaveChangesInDbContextAsync(DbContext dbContext) at Abp.EntityFrameworkCore.Uow.EfCoreUnitOfWork.SaveChangesAsync() at Abp.EntityFrameworkCore.Uow.EfCoreUnitOfWork.CompleteUowAsync() at Abp.Domain.Uow.UnitOfWorkBase.CompleteAsync() at Abp.AspNetCore.Mvc.Uow.AbpUowActionFilter.OnActionExecutionAsync(ActionExecutingContext context, ActionExecutionDelegate next) at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync() at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context) at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeInnerFilterAsync() at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextExceptionFilterAsync() INFO 2019-04-26 04:39:02,721 [23 ] .Mvc.Infrastructure.ObjectResultExecutor - Executing ObjectResult, writing value of type 'Abp.Web.Models.AjaxResponse'. INFO 2019-04-26 04:39:02,786 [23 ] ore.Mvc.Internal.ControllerActionInvoker - Executed action xxxx (xxxx.Application) in 31729.9908ms INFO 2019-04-26 04:39:02,847 [23 ] soft.AspNetCore.Hosting.Internal.WebHost - Request finished in 32032.3872ms 500 application/json; charset=utf-8This is from Logs.txt file. There is no record in AbpAuditLogs table.
-
0
You can use Context.Database.ExecuteSqlCommand(...) in a custom repository. It directly executed in the database.
-
0
Yes, I know and it is much faster. Maybe I have to move this varbinary data to seperate table, so that EF won't load it by default.
With ExecuteSqlCommand method, you lose advantages of strongly-typed queries.
-
0
Yes. You can make a choice based on your actual situation.
-
0
I could be wrong but it looks like there is a timeout when the update, (database migration) is being applyed.
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: The wait operation timed outA simple test would be to increase the timeout in the connectionstring and see if it goes away.
https://stackoverflow.com/questions/4308417/connection-timeout-for-sql-server
But thats not a solution, because eventually when you have more rows, the same problem will occur again.
I've found that blob's and/or large binary objects should be put in a seporate data base table, or even a different database, and use a seporate query to retrieve one blob row at a time, when its needed.
Reason: Doing any querying on that table requires the blob to be loaded, even if blob field is NOT in the returned result set. The timeout is caused because the blob field is pushing all values out of cache , or the large amount of IO required to bring the blob off disk, makes the query hit the Max IO bytes per second, and therefor the database pauses your query.
