Entity Framework Core Triggers

EfCoreTriggers is the library to write native SQL triggers using EFCore model builder. Triggers are automatically translating into sql and adding to migrations.

Installation

EfCoreTriggers common package is available on NuGet. Install the provider package corresponding to your target database. See the list of providers in the docs for additional databases.

Configuring DB to use triggers

dotnet add package Laraue.EfCoreTriggers.PostgreSql
dotnet add package Laraue.EfCoreTriggers.MySql
dotnet add package Laraue.EfCoreTriggers.SqlServer
dotnet add package Laraue.EfCoreTriggers.SqlLite

Basic usage

The library has extensions for EntityBuilder to configure DbContext.

After update Transaction entity, update records in the table with UserBalance entities.

modelBuilder.Entity<Transaction>()
    .AfterUpdate(trigger => trigger
        .Action(action => action
            .Condition((oldTransaction, newTransaction) => oldTransaction.IsVeryfied && newTransaction.IsVeryfied) // Executes only if condition met 
            .Update<UserBalance>(
                (oldTransaction, updatedTransaction, userBalances) => userBalances.UserId == oldTransaction.UserId, // Will be updated entities with matched condition
                (oldTransaction, updatedTransaction, oldBalance) => new UserBalance { Balance = oldBalance.Balance + updatedTransaction.Value - oldTransaction.Value }))); // New values for matched entities.

After Insert trigger entity, upsert record in the table with UserBalance entities.

modelBuilder.Entity<Transaction>()
    .AfterDelete(trigger => trigger
        .Action(action => action
            .Condition(deletedTransaction => deletedTransaction.IsVeryfied)
            .Upsert(
                balance => new { balance.UserId }, // If this field is matched, will be executed update operation else insert
                insertedTransaction => new UserBalance { UserId = insertedTransaction.UserId, Balance = insertedTransaction.Value }, // Insert, if value didn't exist
                (insertedTransaction, oldUserBalance) => new UserBalance { Balance = oldUserBalance.Balance + insertedTransaction.Value }))); // Update if value existed

More examples of using are available in Tests/NativeDbContext.cs.

All available triggers

Trigger	PostgreSql	SQL Server	SQLite	MySQL
Before Insert	+	-	+	+
After Insert	+	+	+	+
Instead Of Insert	+	+	+	-
Before Update	+	-	+	+
After Update	+	+	+	+
Instead Of Update	+	+	+	-
Before Delete	+	-	+	+
After Delete	+	+	+	+
Instead Of Delete	+	+	+	-

Available actions after trigger has worked

• Insert
• InsertIfNotExists
• Update
• Upsert
• Delete

Laraue.EfCoreTriggers.PostgreSql

Basic usage

var options = new DbContextOptionsBuilder<TestDbContext>()
    .UseNpgsql("User ID=test;Password=test;Host=localhost;Port=5432;Database=test;")
    .UsePostgreSqlTriggers()
    .Options;

var dbContext = new TestDbContext(options);

Laraue.EfCoreTriggers.MySql

Basic usage

var options = new DbContextOptionsBuilder<TestDbContext>()
    .UseMySql("server=localhost;user=test;password=test;database=test;", new MySqlServerVersion(new Version(8, 0, 22))))
    .UseMySqlTriggers()
    .Options;

var dbContext = new TestDbContext(options);

Laraue.EfCoreTriggers.SqlServer

Basic usage

var options = new DbContextOptionsBuilder<TestDbContext>()
    .UseSqlServer("Data Source=(LocalDb)\\v15.0;Database=test;Integrated Security=SSPI;")
    .UseSqlServerTriggers()
    .Options;

var dbContext = new TestDbContext(options);

Laraue.EfCoreTriggers.SqlLite

Basic usage

var options = new DbContextOptionsBuilder<TestDbContext>()
    .UseSqlite("Filename=D://test.db")
    .UseSqlLiteTriggers()
    .Options;

var dbContext = new TestDbContext(options);

Customization

Using custom provider to extend additional functionality

private class MyCustomSqlProvider : PostgreSqlProvider // Or another used provider
{
    /// Provider will be created via reflection, so constructor only with this argument is allowed 
    public MySqlProvider(IModel model) : base(model)
    {
    }

    protected override string GetColumnName(MemberInfo memberInfo)
    {
        // Change strategy of naming some column
        return 'c_' + base.GetColumnName(memberInfo);
    }
}

Adding this provider to a container

var options = new DbContextOptionsBuilder<TestDbContext>()
    .UseNpgsql("User ID=test;Password=test;Host=localhost;Port=5432;Database=test;")
    .UseTriggers<MyCustomSqlProvider>()
    .Options;

var dbContext = new TestDbContext(options);

Adding translation of some custom function into sql code

To do this thing a custom function converter should be added to a provider

Let's image that we have an extension like

public static class StringExtensions
{
    public static bool Like(this string str, string pattern)
    {
        // Some code
    }
} 

Now a custom converter should be written to translate this function into SQL

public abstract class StringExtensionsLikeConverter : MethodCallConverter
{
    public override bool IsApplicable(MethodCallExpression expression)
    {
        return expression.Method.ReflectedType == typeof(SomeFunctions) && MethodName == nameof(CustomFunctions.Like);
    }
    
    public override SqlBuilder BuildSql(BaseExpressionProvider provider, MethodCallExpression expression, Dictionary<string, ArgumentType> argumentTypes)
    {
        // Generate SQL for arguments, they can be SQL expressions
        var argumentSql = provider.GetMethodCallArgumentsSql(expression, argumentTypes)[0];

        // Generate SQL for this context, it also can be a SQL expression
        var sqlBuilder = provider.GetExpressionSql(expression.Object, argumentTypes);
        
        // Combine SQL for object and SQL for arguments
        // Output will be like "thisValueSql LIKE 'passedArgumentValueSql'"
        return new(sqlBuilder.AffectedColumns, $"{sqlBuilder} LIKE {argumentSql}");
    }
}

All custom converters should be added while setup a database

var options = new DbContextOptionsBuilder<TestDbContext>()
    .UseSqlite("Filename=D://test.db")
    .UseSqlLiteTriggers(converters => converters.ExpressionCallConverters.Push(converter))
    .Options;

var dbContext = new TestDbContext(options);

Now this function can be used in a trigger and it will be translated into SQL

modelBuilder.Entity<Transaction>()
    .AfterDelete(trigger => trigger
        .Action(action => action
            .Condition(oldTransaction => oldTransaction.Description.Like('%payment%'))