ARTICLE AD BOX
I have written a method like this:
try { _unitOfWork.BeginTransaction(); response = _unitOfWork.DeclarationRepository.ChangeDeclarationSectionFormsState_1Async(declarationId,sectionId, newFormState); // we need this save changes here so that the intermediate result // is available for UpdateDeclarationStatusIfApplicableAsync to read the // statuses of the forms even if they are not yet committed to the db await _unitOfWork.SaveChangesAsync(cancellationToken); //throw new Exception(); DeclarationStatusTypes newDeclarationStatus = Mappers.FormStateToDeclarationStatusMapper[newFormState]; await _unitOfWork.DeclarationRepository.UpdateDeclarationStatusIfApplicableAsync(declarationId, newDeclarationStatus, cancellationToken); await _unitOfWork.CommitAsync(cancellationToken); } catch { _unitOfWork.Rollback(); throw; }I am calling SaveChanges in between, so that next method UpdateDeclarationStatusIfApplicableAsync can read the result with the intermediate changes.
The method tries to read intermediate records like this:
StringBuilder query = new StringBuilder("SELECT CAST(CASE WHEN EXISTS ("); foreach (string formTable in FormDbTableMapper.Values) { query.AppendLine($"SELECT 1 AS [Value] FROM {Constants.Schema.DEC}.{formTable} WHERE DeclarationId=@DeclarationId AND IsActive=1 AND State<>@State"); query.AppendLine(" UNION "); } // remove the last UNION query.Length -= 8; query.AppendLine(") THEN 0 ELSE 1 END AS BIT) AS [Value]"); SqlParameter declarationIdParam = new SqlParameter("DeclarationId",declarationId); SqlParameter stateParam = new SqlParameter("State", (int)state); return await dbContext.Database.SqlQueryRaw<bool>(query.ToString(), new[] { declarationIdParam, stateParam }).FirstOrDefaultAsync();The transaction rolls back completely if an exception occurs - all or nothing is also happening which is what my requirement is. Everything is working perfectly fine.
My question: is it an anti-pattern or wrong to create save points like this before the transaction commit?
