EfCoreTriggers is the library to write native SQL triggers using EFCore model builder. Triggers are automatically translating into sql and adding to migrations.
EfCoreTriggers common package is available on Nuget. Version 7.x.x is compatible with EFCore 7.x.x and targets .NET6, 5.x.x intended for EFCore 5.x.x and targets .NET standard 2.1. Install the provider package corresponding to your target database. See the list of providers in the docs for additional databases.
dotnet add package Laraue.EfCoreTriggers.PostgreSql
dotnet add package Laraue.EfCoreTriggers.MySql
dotnet add package Laraue.EfCoreTriggers.SqlServer
dotnet add package Laraue.EfCoreTriggers.SqlLite
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(tableRefs => tableRefs.Old.IsVeryfied && tableRefs.New.IsVeryfied) // Executes only if condition met
.Update<UserBalance>(
(tableRefs, userBalances) => userBalances.UserId == tableRefs.Old.UserId, // Will be updated entities with matched condition
(tableRefs, oldBalance) => new UserBalance { Balance = oldBalance.Balance + tableRefs.New.Value - tableRefs.Old.Value }))); // New values for matched entities.
After insert Transaction entity, upsert record in the table with UserBalance entities.
modelBuilder.Entity<Transaction>()
.AfterDelete(trigger => trigger
.Action(action => action
.Condition(tableRefs => tableRefs.Old.IsVeryfied)
.Upsert(
(tableRefs, balances) => tableRefs.Old.UserId == balances.UserId, // If this field will match more than 0 rows, will be executed update operation for these rows else insert
tableRefs => new UserBalance { UserId = tableRefs.Old.UserId, Balance = tableRefs.Old.Value }, // Insert, if value didn't exist
(tableRefs, oldUserBalance) => new UserBalance { Balance = oldUserBalance.Balance + tableRefs.Old.Value }))); // Update all matched values
After delete Transaction entity, execute raw SQL. Pass deleted entity fields as arguments.
modelBuilder.Entity<Transaction>()
.AfterDelete(trigger => trigger
.Action(action => action
.ExecuteRawSql("PERFORM recalc_balance({0}, {1})"), tableRefs => tableRefs.Old.UserId, tableRefs => tableRefs.Old.Amount)));
Also, different trigger functions can be used to generate the SQL.
TriggerFunctions.GetTableName<Transaction>();
TriggerFunctions.GetColumnName<Transaction>(transaction => transaction.Value);
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 | + | + | + | - |
var options = new DbContextOptionsBuilder<TestDbContext>()
.UseNpgsql("User ID=test;Password=test;Host=localhost;Port=5432;Database=test;")
.UsePostgreSqlTriggers()
.Options;
var dbContext = new TestDbContext(options);
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);
var options = new DbContextOptionsBuilder<TestDbContext>()
.UseSqlServer("Data Source=(LocalDb)\\v15.0;Database=test;Integrated Security=SSPI;")
.UseSqlServerTriggers()
.Options;
var dbContext = new TestDbContext(options);
var options = new DbContextOptionsBuilder<TestDbContext>()
.UseSqlite("Filename=D://test.db")
.UseSqlLiteTriggers()
.Options;
var dbContext = new TestDbContext(options);
Any service using for generation SQL can be replaced.
private class CustomDbSchemaRetriever : EfCoreDbSchemaRetriever
{
public CustomDbSchemaRetriever(IModel model) : base(model)
{
}
protected override string GetColumnName(Type type, MemberInfo memberInfo)
{
// Change strategy of naming some column
return 'c_' + base.GetColumnName(type, memberInfo);
}
}
Adding this service to the container
var options = new DbContextOptionsBuilder<TestDbContext>()
.UseNpgsql("User ID=test;Password=test;Host=localhost;Port=5432;Database=test;")
.UsePostgreSqlTriggers(services => services.AddSingleton<IDbSchemaRetriever, CustomDbSchemaRetriever>)
.Options;
var dbContext = new TestDbContext(options);
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)
{
throw new InvalidOperationException();
}
}
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(StringExtensions) && MethodName == nameof(StringExtensions.Like);
}
public override SqlBuilder BuildSql(BaseExpressionProvider provider, MethodCallExpression expression)
{
// Generate SQL for arguments, they can be SQL expressions
var argumentSql = provider.GetMethodCallArgumentsSql(expression)[0];
// Generate SQL for this context, it also can be a SQL expression
var sqlBuilder = provider.GetExpressionSql(expression.Object);
// 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(services => services.AddMethodCallConverter(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%'))
You can change the standard library prefix for trigger using the next static variable
Laraue.EfCoreTriggers.Common.Constants.AnnotationKey = "MY_PREFIX"