efcore EF Core 8.0.7 'HasTrigger' DbUpdateException

Ask a question

We are using EF Core Power tools (2.6.437) to generate our data project. For tables that contain triggers, it is adding the HasTrigger methods to our MainDBContext class. At runtime, however, we are getting exceptions trying to update the tables

We have read through the breaking changes ef-core-7.0/breaking-changes?tabs=v7#sqlserver-tables-with-triggers which indicates that the 'HasTriggers' should be all we need.

Has the UseSqlOutputClause(false) superseded the HasTriggers() approach ?

Could this have anything to do with the compatibility level of the database being too low for EF Core 8 ?

Include your code

Usually the best way to ask a clear question and get a quick response is to show your code. Preferably, attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing.

Use triple-tick code fences for any posted code. For example:

        modelBuilder.Entity<BusinessReviewPerson>(entity =>
        {
            entity.HasKey(e => e.BusinessReviewPeopleId).HasName("PK_BusinessReview_People_1");

            entity.ToTable("BusinessReview_People", "dbo", tb => tb.HasTrigger("BusinessReview_People_AddUser"));

            entity.Property(e => e.AtReview).IsFixedLength();
            entity.Property(e => e.DateAdded).HasDefaultValueSql("(getdate())");
            entity.Property(e => e.DateModifed).HasDefaultValueSql("(getdate())");

            entity.HasOne(d => d.BusinessAreaKeyNavigation).WithMany(p => p.BusinessReviewPeople).HasConstraintName("FK_BusinessReview_People_BusinessArea");

            entity.HasOne(d => d.BusinessReviewMeeting).WithMany(p => p.BusinessReviewPeople).HasConstraintName("FK_BusinessReview_People_BusinessReview_Meeting");

            entity.HasOne(d => d.BusinessReviewPeopleRole).WithMany(p => p.BusinessReviewPeople)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_BusinessReview_People_BusinessReview_PeopleRole");

            entity.HasOne(d => d.BusinessReviewPeopleStatus).WithMany(p => p.BusinessReviewPeople).HasConstraintName("FK_BusinessReview_People_BusinessReview_PeopleStatus");

            entity.HasOne(d => d.MemberUser).WithMany(p => p.BusinessReviewPeople)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_BusinessReview_People_People");
        });

Include stack traces

Category: Microsoft.EntityFrameworkCore.Update
EventId: 10000
SpanId: 9731b06dec0be6ce
TraceId: bc1933104e7854d8d87a3ecb3f684cf3
ParentId: 0000000000000000
RequestId: 80000390-0004-fb00-b63f-84710c7967bb
RequestPath: /api/BusinessReviews/UpdateReviewer
ActionId: e3c276d2-d721-4785-956b-769b4b49e937
ActionName: LAMP.Controllers.BusinessReviewsController.UpdateReviewer (LAMP-App)

An exception occurred in the database while saving changes for context type 'LAMP.Lib.DB.Services.Database.LampDB.MainDBContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: Could not save changes because the target table has database triggers. Please configure your table accordingly, see https://aka.ms/efcore-docs-sqlserver-save-changes-and-output-clause for more information.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The target table 'dbo.BusinessReview_People' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
   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.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   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:876d8f18-3769-44fd-8f36-59523f7d31df
Error Number:334,State:1,Class:16
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

Exception: 
Microsoft.EntityFrameworkCore.DbUpdateException: Could not save changes because the target table has database triggers. Please configure your table accordingly, see https://aka.ms/efcore-docs-sqlserver-save-changes-and-output-clause for more information.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The target table 'dbo.BusinessReview_People' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
   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.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   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:876d8f18-3769-44fd-8f36-59523f7d31df
Error Number:334,State:1,Class:16
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

Include provider and version information

EF Core version: 8.0.7 Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer) Target framework: .NET 8.0) Operating system: Windows 11/Windows Server 2016 IDE: Visual Studio 2022 17.11.0

Aug 28 '24 15:08 awdorrin

This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

Aug 28 '24 15:08 ajcvickers

That may take me awhile, as I don't have an easy way to spin up a 'test' SQL Server instance in our environment. Let me see what I can do.

Aug 28 '24 15:08 awdorrin

This error is not happening when we run against our Dev DB, only production DB. Both are SQL Server 2016 with the same table definitions and triggers.

Aug 28 '24 16:08 awdorrin

Did more investigation and have found the following:

Code built as Debug works fine, issue appears when the code is built in Release mode.
Code built using VS 17.11 works without issue
Code built using our CI/CD pipeline, which uses VS/MSBuild 17.9.8, throws the exceptions described in the initial post
Code built locally is platform specific (win-x64) while the CI/CD pipeline is producing a runtime folder with several variants. (need to figure out how to lock that down to just win-x64)

Aug 28 '24 21:08 awdorrin

Closing this as not an issue. After hours of pulling our hair out, we found that for some reason, the last pull request into our CI/CD pipeline ignored all of the changes in the database project.... None of code building on that platform had any of the HasIndex references (and more) Not sure what happened or how we missed this in the first place. (sometimes Git annoys me to no end....)

Thanks

Aug 28 '24 22:08 awdorrin