Entity Framework Core 7 has error with SQL data sync

Question

I have an API in .NET Core that I have been updating from version 3.1 and everything works correctly until version 6 where I am currently. I tried to update to version 7 and everything is fine, the problem occurs when I try to update from EF Core 6 to EF Core 7, the get requests work without any problem, but when trying to insert, update or delete it gives me the following error:

Could not save changes because the target table has database triggers. Please configure your entity type accordingly, see https://aka.ms/efcore-docs-sqlserver-save-changes-and-triggers for more information.
The target table 'table_name' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

I have researched this error and it tells me something about the triggers in my database, but I don't have any triggers.

Up to this point I only have a Microsoft SQL Data Sync service active since I am synchronizing my database on premise with Azure since it is necessary to maintain this function I have not been able to progress much since I cannot configure it to ignore the SQL Data functions Sync as EF Core 6 does. The only thing that the database has are tables and stored procedures that the synchronization agent needs to work. Is there a way to disable EF Core 7's trigger function so that it ignores them when modifying table data?.

This is my configuration class for EF Core:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace Configurations
{
    class MainTokenConfiguration : IEntityTypeConfiguration<MainToken>
    {
        public void Configure(EntityTypeBuilder<MainToken> builder)
        {
            builder.ToTable("main_token");

            builder.Property(e => e.Id)
                .HasMaxLength(100)
                .IsUnicode(false)
                .HasColumnName("id")
                .HasDefaultValueSql("(newid())");

            builder.Property(e => e.Token)
                .HasMaxLength(5000)
                .IsUnicode(false)
                .HasColumnName("token");

            builder.Property(e => e.CreatedBy)
                .HasMaxLength(500)
                .HasColumnName("created_by");

            builder.Property(e => e.CreationDate)
                .HasColumnType("datetime")
                .HasColumnName("creation_date");

            builder.Property(e => e.Email)
                .IsRequired()
                .HasMaxLength(250)
                .IsUnicode(false)
                .HasColumnName("email");

            builder.Property(e => e.ExpirationDate)
                .HasColumnType("datetime")
                .HasColumnName("expiration_date");
        }
    }
}

Answer 1

For future reference on this, this was a breaking change from EF7 and is also present in EF8.

https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/whatsnew#triggers

If the table has triggers, then the call to SaveChanges in the code above will throw an exception:

Unhandled exception. Microsoft.EntityFrameworkCore.DbUpdateException: Could not save changes because the target table has database triggers. Please configure your entity type accordingly, see https://aka.ms/efcore-docs-sqlserver-save-changes-and-triggers for more information. ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The target table 'BlogsWithTriggers' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

The following code can be used to inform EF Core that the table has a trigger:

modelBuilder
    .Entity<BlogWithTrigger>()
    .ToTable(tb => tb.HasTrigger("TRG_InsertUpdateBlog"));

EF7 will then revert to the EF Core 6.0 SQL when sending insert and update commands for this table.

For more information, including a convention to automatically configure all mapped tables with triggers, see SQL Server tables with triggers now require special EF Core configuration in the EF7 breaking changes documentation.

Why

The performance improvements linked to the new method are significant enough that it's important to bring them to users by default. At the same time, we estimate usage of database triggers or the affected computed columns in EF Core applications to be low enough that the negative breaking change consequences are outweighed by the performance gain.

Mitigations

You can let EF Core know that the target table has a trigger; doing so will revert to the previous, less efficient technique. This can be done by configuring the corresponding entity type as follows:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .ToTable(tb => tb.HasTrigger("SomeTrigger"));
}

Note that doing this doesn't actually make EF Core create or manage the trigger in any way - it currently only informs EF Core that triggers are present on the table. As a result, any trigger name can be used, and this can also be used if an unsupported computed column is in use (regardless of triggers).

If most or all of your tables have triggers, you can opt out of using the newer, efficient technique for all your model's tables by using the following model building convention:

public class BlankTriggerAddingConvention : IModelFinalizingConvention
{
    public virtual void ProcessModelFinalizing(
        IConventionModelBuilder modelBuilder,
        IConventionContext<IConventionModelBuilder> context)
    {
        foreach (var entityType in modelBuilder.Metadata.GetEntityTypes())
        {
            var table = StoreObjectIdentifier.Create(entityType, StoreObjectType.Table);
            if (table != null
                && entityType.GetDeclaredTriggers().All(t => t.GetDatabaseName(table.Value) == null)
                && (entityType.BaseType == null
                    || entityType.GetMappingStrategy() != RelationalAnnotationNames.TphMappingStrategy))
            {
                entityType.Builder.HasTrigger(table.Value.Name + "_Trigger");
            }

            foreach (var fragment in entityType.GetMappingFragments(StoreObjectType.Table))
            {
                if (entityType.GetDeclaredTriggers().All(t => t.GetDatabaseName(fragment.StoreObject) == null))
                {
                    entityType.Builder.HasTrigger(fragment.StoreObject.Name + "_Trigger");
                }
            }
        }
    }
}

Use the convention on your DbContext by overriding ConfigureConventions:

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder.Conventions.Add(_ => new BlankTriggerAddingConvention());
}

This effectively calls HasTrigger on all your model's tables, instead of you having to do it manually for each and every table.