MSSQL replication triggers, how to handle conditional HasTrigger in EntityFrameworkCore

Question

I am using EntityFrameworkCore version 7 to implement data access across a number of client databases.

I have recently run into the error 'Could not save changes because the target table has database triggers.' on one of the clients. The error is obviously self explanatory and I understand how to fix it using HasTrigger.

The problem is that this error has occurred because this specific client is replicated and has what I assume are auto generated triggers MSmerge_upd, MSmerge_ins, MSmerge_del. Concurrently the majority of my clients are not replicated and would therefore not have any of these triggers in their database.

So, what is the correct way to handle replication triggers in EntityFrameworkCore particularly when your clients have a mishmash where some are replicated and some are not? Is there a way to check inside IEntityTypeConfiguration if you are running on a replicated database and conditionally add the replication triggers? Is there some sort of best practice in terms of how to handle this scenario with the new HasTriggers requirement?

Answer 1

Given that nobody has posted any answer I will post what my workaround is for now.

I have created a class called AutoTriggerBuilderEntityTypeConfiguration which basically attempts to configure all the triggers for a given EF model.

There are some performance implications with this approach and it could potentially be improved by caching the triggers for all tables across the database but its sufficient for my use case.

It looks like this:

public abstract class AutoTriggerBuilderEntityTypeConfiguration<TEntity> : IEntityTypeConfiguration<TEntity>
    where TEntity : class
{
    private readonly string _connectionString;

    public AutoTriggerBuilderEntityTypeConfiguration(string connectionString)
    {
        this._connectionString = connectionString;
    }

    public void Configure(EntityTypeBuilder<TEntity> builder)
    {
        this.ConfigureEntity(builder);

        var tableName = builder.Metadata.GetTableName();
        var tableTriggers = this.GetTriggersForTable(tableName);
        var declaredTriggers = builder.Metadata.GetDeclaredTriggers();
            
        builder.ToTable(t =>
        {
            foreach (var trigger in tableTriggers)
            {
                if (!declaredTriggers.Any(o => o.ModelName.Equals(trigger, StringComparison.InvariantCultureIgnoreCase)))
                    t.HasTrigger(trigger);
            }
        });
    }

    private IEnumerable<string> GetTriggersForTable(string tableName)
    {
        var result = new List<string>();
        using (var connection = new SqlConnection(this._connectionString))
        using (var command = new SqlCommand(@"SELECT sysobjects.name AS Name FROM sysobjects WHERE sysobjects.type = 'TR' AND OBJECT_NAME(parent_obj) = @TableName", connection)
        {
            CommandType = CommandType.Text
        })
        {
            connection.Open();

            command.Parameters.AddWithValue("@TableName", tableName);

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                    result.Add(reader.GetString("Name"));
            }
        }

        return result;
    }
        

    public abstract void ConfigureEntity(EntityTypeBuilder<TEntity> builder);

}