win7user10 / laraue.efcoretriggers Goto Github PK

If I have tables setup for table splitting like

public class IntEntity {
	public int ID { get; set; }
	public int Value { get; set; }

	public virtual EntityDetails Details { get; set; }
}

public class EntityDetails {
	public int ID { get; set; }
	public string Text { get; set; }
}

and

public class EntityHistory {
	public int ID { get; set; }
	public int Value { get; set; }
	public string Text { get; set; }
}

and on the contexts' OnModelCreating method I setup the context like this

modelBuilder.Entity<IntEntity>(eb => {
	eb.ToTable("IntEntity")
		.HasKey(e => e.ID);

	eb.HasOne(e => e.Details)
		.WithOne()
		.HasPrincipalKey<IntEntity>(e => e.ID)
		.HasForeignKey<EntityDetails>(d => d.ID);

	eb.AfterInsert(trigger => trigger
		.Action(action => action
			.Insert<EntityHistory>(tableRefs => new EntityHistory() {
				Value = tableRefs.New.Value,
				Text = tableRefs.New.Details.Text
			})
		)
	);
});

modelBuilder.Entity<EntityDetails>(db => {
	db.ToTable("IntEntity");
});

modelBuilder.Entity<EntityHistory>(db => {
	db.ToTable("EntityHistory");
});

If I then try to create a new IntEntity with some Details and save it to the database, the program hits an exception "Column Text was not found in EFCoreTriggerTest.IntEntity" with a stacktrace

     at Laraue.EfCoreTriggers.Common.SqlGeneration.EfCoreDbSchemaRetriever.GetColumn(Type type, MemberInfo memberInfo)
   at Laraue.EfCoreTriggers.Common.SqlGeneration.EfCoreDbSchemaRetriever.GetColumnName(Type type, MemberInfo memberInfo)
   at Laraue.EfCoreTriggers.Common.SqlGeneration.SqlGenerator.GetColumnSql(Type type, MemberInfo memberInfo, ArgumentType argumentType)
   at Laraue.EfCoreTriggers.Common.Visitors.ExpressionVisitors.MemberExpressionVisitor.GetColumnSql(Type tableType, MemberInfo columnMember, ArgumentType argumentType)
   at Laraue.EfCoreTriggers.Common.Visitors.ExpressionVisitors.MemberExpressionVisitor.GetColumnSql(MemberExpression memberExpression, MemberInfo parentMember, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.ExpressionVisitors.MemberExpressionVisitor.Visit(MemberExpression memberExpression, ArgumentType argumentType, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.ExpressionVisitors.MemberExpressionVisitor.Visit(MemberExpression expression, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.ExpressionVisitors.ExpressionVisitorFactory.Visit[TExpression](TExpression expression, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.ExpressionVisitors.ExpressionVisitorFactory.<>c__DisplayClass4_0.<VisitAndRememberMember>b__0()
   at Laraue.EfCoreTriggers.Common.Visitors.ExpressionVisitors.VisitingInfoExtensions.ExecuteWithChangingMember[T](VisitingInfo info, MemberInfo memberInfo, Func`1 action)
   at Laraue.EfCoreTriggers.Common.Visitors.ExpressionVisitors.ExpressionVisitorFactory.VisitAndRememberMember(MemberExpression expression, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.ExpressionVisitors.ExpressionVisitorFactory.Visit(Expression expression, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.SetExpressionVisitors.SetMemberInitExpressionVisitor.<>c__DisplayClass3_1.<Visit>b__3()
   at Laraue.EfCoreTriggers.Common.Visitors.ExpressionVisitors.VisitingInfoExtensions.ExecuteWithChangingMember[T](VisitingInfo info, MemberInfo memberInfo, Func`1 action)
   at Laraue.EfCoreTriggers.Common.Visitors.SetExpressionVisitors.SetMemberInitExpressionVisitor.<>c__DisplayClass3_0.<Visit>b__0(MemberBinding memberBinding)
   at System.Linq.Enumerable.SelectIListIterator`2.MoveNext()
   at System.Linq.Enumerable.ToDictionary[TSource,TKey,TElement](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
   at System.Linq.Enumerable.ToDictionary[TSource,TKey,TElement](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector)
   at Laraue.EfCoreTriggers.Common.Visitors.SetExpressionVisitors.SetMemberInitExpressionVisitor.Visit(MemberInitExpression expression, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.SetExpressionVisitors.MemberInfoVisitorFactory.Visit[TExpression](TExpression expression, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.SetExpressionVisitors.MemberInfoVisitorFactory.Visit(Expression expression, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.SetExpressionVisitors.SetLambdaExpressionVisitor.Visit(LambdaExpression expression, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.SetExpressionVisitors.MemberInfoVisitorFactory.Visit[TExpression](TExpression expression, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.SetExpressionVisitors.MemberInfoVisitorFactory.Visit(Expression expression, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.TriggerVisitors.Statements.InsertExpressionVisitor.Visit(LambdaExpression expression, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.TriggerVisitors.TriggerInsertActionVisitor.Visit(TriggerInsertAction triggerAction, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.TriggerVisitors.TriggerActionVisitorFactory.Visit[T](T triggerAction, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.TriggerVisitors.TriggerActionVisitorFactory.Visit(ITriggerAction triggerAction, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.TriggerVisitors.BaseTriggerActionsGroupVisitor.<>c__DisplayClass2_0.<Visit>b__0(ITriggerAction action)
   at System.Linq.Enumerable.SelectListIterator`2.ToArray()
   at Laraue.EfCoreTriggers.Common.Visitors.TriggerVisitors.BaseTriggerActionsGroupVisitor.Visit(TriggerActionsGroup triggerActionsGroup, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.TriggerVisitors.TriggerActionVisitorFactory.Visit[T](T triggerAction, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.Common.Visitors.TriggerVisitors.TriggerActionVisitorFactory.Visit(ITriggerAction triggerAction, VisitedMembers visitedMembers)
   at Laraue.EfCoreTriggers.SqlServer.SqlServerTriggerVisitor.<>c__DisplayClass7_0.<GenerateCreateTriggerSql>b__0(TriggerActionsGroup action)
   at System.Linq.Enumerable.SelectListIterator`2.ToArray()
   at Laraue.EfCoreTriggers.SqlServer.SqlServerTriggerVisitor.GenerateCreateTriggerSql(ITrigger trigger)
   at Laraue.EfCoreTriggers.Common.Migrations.MigrationsExtensions.ConvertTriggerAnnotationsToSql(ITriggerVisitor triggerVisitor, IModel model)
   at Laraue.EfCoreTriggers.Common.Migrations.TriggerModelDiffer.AddTriggerOperations(IEnumerable`1 operations, IRelationalModel source, IRelationalModel target)
   at Laraue.EfCoreTriggers.Common.Migrations.MigrationsModelDiffer.GetDifferences(IRelationalModel source, IRelationalModel target)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.GetCreateTablesCommands(MigrationsSqlGenerationOptions options)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.CreateTables()
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreated()
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureCreated()

No tables are created in this case when before saving the entity I call ctx.Database.EnsureCreated(). This seems to be an issue with this library because if I comment out setting the Text property on the history entity in the Insert trigger, everything works, the IntEntity is saved with details and an EntityHistory row is created. I tested this on version 7.1.1 against a SQL Server instance.

If I have a table like

public class NullableHolder {
	[Key]
	public int ID { get; set; }
	public int? Value { get; set; }
}

And I create Insert and Update triggers like (take note of the comparisons)

modelBuilder.Entity<NullableHolder>()
	.AfterInsert(trigger => trigger
		.Action(action => action
			.Condition(nh => nh.Value != null)
		)
	)
	.AfterUpdate(trigger => trigger
		.Action(action => action
			.Condition((before, after) => (before.Value != null) && (after.Value == null))
		)
	);

After running Add-Migration I get the following triggers

CREATE TRIGGER LC_TRIGGER_AFTER_INSERT_NULLABLEHOLDER 
--
IF (@NewValue IS NULL)

and

CREATE TRIGGER LC_TRIGGER_AFTER_UPDATE_NULLABLEHOLDER 
--
IF (@OldValue IS NULL AND @NewValue IS NULL)

Two of the created IS NULLs seem to be incorrect when it's created even when the actual comparison was .Value != null?

Unfortunately, I'm not able to run .NET6 so this behaviour is seen against library version 5.3.6.

I think this awesome library could support collection of owned entities, which get mapped to separate tables.
But I guess this would require some effort, because as of now all the triggers reference only generic types, and an owned type could have many underlying EF types, since it could be used in multiple entities.

I'm willing to help if you could guide me in what needs to be done in order to account for this.
I think that adding a reference to an EF entity would be enough, from there it could pick all the informations about the table, while all the generic methods would stay the same, since the columns are all the same.

Hi,

we are using parallelized the test execution to run our integration tests and therefore use multiple DB Contexts in different threads.

I noticed that the build sometimes fails with the following exception:

Failed Execute_HasPersonsLinked_SendsEmails [5 s]
  Error Message:
   Initialization method Ofcas.Odid.IntegrationTests.InfrastructureTest.QuartzTest.JobsTest.NotifyAboutPrivacyPolicyUpdateJobTest.TestInitialize threw exception. System.InvalidOperationException: Collection was modified after the enumerator was instantiated..
  Stack Trace:
      at System.Collections.Generic.SortedSet`1.Enumerator.MoveNext()
   at System.Collections.Generic.SortedDictionary`2.Enumerator.MoveNext()
   at System.Collections.Generic.SortedDictionary`2.ValueCollection.Enumerator.MoveNext()
   at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
   at Laraue.EfCoreTriggers.Common.Migrations.MigrationsModelDiffer.GetDifferences(IRelationalModel source, IRelationalModel target)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.GetCreateTablesCommands(MigrationsSqlGenerationOptions options)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.CreateTables()
   at Ofcas.Odid.IntegrationTests.SQLiteConnectionTestHelper.TestInitialize()

The failure is more likely the more parallel runners I add.

I investigated this and I think I found the problem. The library modifies the annotations of the model in the MigrationsModelDiffer:

This causes the SortedDict for the annotations to be modified:
https://github.com/dotnet/efcore/blob/ab0509d03fd93aecfe692348e2867257a5568d82/src/EFCore/Infrastructure/AnnotatableBase.cs#L24

The issue is caused by the fact, that EF Core build the model only once and caches the it for future calls: https://learn.microsoft.com/en-us/ef/core/modeling/dynamic-model. This means if CreateTables is called by multiple threads at the same time, one thread will modify the annotations, while the other threads holds an iterator for the annotations, by executing GetTriggerAnnotations() here:

I verified this by creating my own IModelCacheKeyFactory that essentially disables caching by generating a random cache key:

public class NoChachingModelCacheKeyFactory : IModelCacheKeyFactory
{
    public object Create(DbContext context, bool designTime)
        => Guid.NewGuid();
}

new DbContextOptionsBuilder<DbContext>()
                .UseSqlite(_connection)
                .UseSqlLiteTriggers(services => services.AddMethodCallConverter<TriggerMethodCallConverter>())
                .ReplaceService<IModelCacheKeyFactory, NoChachingModelCacheKeyFactory>()
                .Options;

This prevents the exception, but it is obviously not optimal, because it incurs a performance penalty.

Maybe there is a better way to solve this.

If I have tables setup like this:

public class Project {
   [Key]
   public int ID { get; set; }
}

public class ProjectHistory{
   [Key]
   public int ID { get; set; }
   public int ProjectID { get; set; }
}

And I'm thinking about generating Insert, Update, Delete history from the Project table to ProjectHistory with a trigger like this:

modelBuilder.Entity<Project>()
	.AfterUpdate(trigger => trigger
		.Action(action => action
			.Condition((prevProject, newProject) => true)
			.Insert<ProjectHistory>((prevProject, newProject) => new ProjectHistory() {
				ProjectID = prevProject.ID,
			})
		)
	)

where I was thinking I'd be clever and add the Condition call immediately, being always true, so when I actually figure out a good condition, I'll remember to modify it. But this generates SQL that SQL Server doesn't accept:

CREATE TRIGGER LC_TRIGGER_AFTER_UPDATE_PROJECT ON Project AFTER Update AS
      BEGIN
        DECLARE @OldID INT
        DECLARE DeletedProjectCursor CURSOR FOR SELECT ID FROM Deleted
        OPEN DeletedProjectCursor
        FETCH NEXT FROM DeletedProjectCursor INTO @OldID
        WHILE @@FETCH_STATUS = 0
        BEGIN
          IF (1)
          INSERT INTO ProjectHistory ("ProjectID") SELECT @OldID;
          FETCH NEXT FROM DeletedProjectCursor INTO @OldID
        END
        CLOSE DeletedProjectCursor DEALLOCATE DeletedProjectCursor
      END

The offending line is the one coming from the Condition call "IF (1)" which gets an error "An expression of non-boolean type specified in a context where a condition is expected, near 'INSERT'" where as something like "IF (1=1)" would pass.

Unfortunately, I'm not able to run .NET6 so this behaviour is seen against library version 5.3.2.

If I have a table like

public class NullableHolder {
	[Key]
	public int ID { get; set; }
	public int? Value { get; set; }
}

And I try to create an AfterInsert trigger with a Condition like

modelBuilder.Entity<NullableHolder>()
	.AfterInsert(trigger => trigger
		.Action(action => action
			.Condition(nh => (nh.Value ?? -1) != 7)
		)
	);

or a similar Update trigger with a condition comparing values, then after doing an Add-Migration I get an error

System.NotSupportedException: Unknown sign of Coalesce
at Laraue.EfCoreTriggers.Common.Services.Impl.SqlGenerator.GetOperand(Expression expression)
at Laraue.EfCoreTriggers.SqlServer.SqlServerSqlGenerator.GetOperand(Expression expression)
...

This is with the Sql Server triggers package. Is the null coalescing something that could be supported and turned into something like ISNULL(VALUE, -1) <> 7 (or equivalent in whatever SQL product this is run against)?

Unfortunately, I'm not able to run .NET6 so this behaviour is seen against library version 5.3.6.

I want to be able to run a select statement as part of an update trigger, in this case COUNT the number of rows when a change has been made and write this to an audit table. I am attempting to write a MethodCallConverter but I am not having much luck. My structure looks something like this:

public class TypeConfiguration :	IEntityTypeConfiguration<AuditableThing>
{
	public void Configure(EntityTypeBuilder<AuditableThing> builder)
	{
        builder
			.AfterUpdate(trigger => trigger.Action(action => action.Insert((previous, next) =>
				new AuditEntry
				{
					Total = next.Rows.Count(),
                    TotalX = next.Rows.Count(x => x > 1),
				}
			)));
    }
}

public class CountConverter : MethodCallConverter
{
	public override SqlBuilder BuildSql(BaseExpressionProvider provider, MethodCallExpression expression, Dictionary<string, ArgumentType> argumentTypes)
	{
		Debugger.Launch();
		// 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, $"(SELECT COUNT(1) FROM ??? WHERE )");
	}

	public override string MethodName { get; } = nameof(Enumerable.Count);
	public override Type ReflectedType { get; } = typeof(Enumerable);
}

provider.GetMethodCallArgumentsSql(expression, argumentTypes)[0] yields System.ArgumentNullException: 'Value cannot be null. Arg_ParamName_Name' and provider.GetExpressionSql(expression.Object, argumentTypes) yields System.ArgumentNullException: 'Value cannot be null. Arg_ParamName_Name' do you have any advice or pointers on how to implement this?

Many thanks

I tried to create idempotent scripts with this library and I'm running into the problem that the script will be created properly, but when I want to execute the script this error message comes up:

Can't create a TRIGGER from within another stored routine

Is there a possiblity to enable this feature? I can imagine it could be tricky because it would be necessary to manipulate the generation of SQL there.

Hi, everyone!

While using Laraue.EfCoreTriggers.PostgreSql version 7.1.1 with net8, I encounter the following error while creating ef model. After I rebuild package for net8 by changing net7.0 to net8.0 everything is fine.

 ---> System.MissingMethodException: Method not found: 'Microsoft.EntityFrameworkCore.Metadata.IMutableModel Microsoft.EntityFrameworkCore.Metadata.IMutableEntityType.get_Model()'.
   at Laraue.EfCoreTriggers.Common.Extensions.EntityTypeBuilderExtensions.AddTriggerAnnotation[T](EntityTypeBuilder`1 entityTypeBuilder, ITrigger configuredTrigger)
   at Laraue.EfCoreTriggers.Common.Extensions.EntityTypeBuilderExtensions.AddTrigger[T,TRefs](EntityTypeBuilder`1 entityTypeBuilder, TriggerEvent triggerEvent, TriggerTime triggerTime, Action`1 configureTrigger)
   at Laraue.EfCoreTriggers.Common.Extensions.EntityTypeBuilderExtensions.AfterDelete[T](EntityTypeBuilder`1 entityTypeBuilder, Action`1 configuration)```

How can I make multiple conditions for for example, that is to say (If ElseIf else)Example:

EntityTypeBuilder.AfterUpdate(trigger => trigger
.Action(action => action
.Condition((transactionBeforeUpdate, transactionAfterUpdate) => transactionAfterUpdate.Method== "cash")
.Delete(
(entityBeforeUpdate, entityAfterUpdate, transaction) => transaction.PaymentId == entityAfterUpdate.Id)
.Condition((transactionBeforeUpdate, transactionAfterUpdate) => transactionAfterUpdate.Area== "bank")
.Delete(
(entityBeforeUpdate, entityAfterUpdate, cash) => cash.PaymentId == entityAfterUpdate.Id)) );

In this situation i get "And" not "Else if"
How i can get in sql "If Payment.Method Else If Payment.Method............"

Hi,
When I create a project using Enums on the entities as a property, then I'm unable to generate a trigger base on that property.

For example, I added the Property Status to the User Class and declared it as a enum UserStatus.

If I create a trigger to do another action based on the status changing from draft to verified, then the Add-Migration fails with Convert is not supported.

I've done a little work to fix the issue, but I'm not sure its quite right. I include the files I updated for you to look at. I will explain the changes below.

2 changes are required to fix the issue.

In the implementations of BaseTriggerProvider GetSqlServerType need updating to include Enum : "INT" in the mapping variable, and the try get needs to be updated to try the base type as well.
return mapping.TryGetValue(propertyInfo.PropertyType, out String type)
|| propertyInfo.PropertyType.BaseType != null && mapping.TryGetValue(propertyInfo.PropertyType.BaseType, out type)
? type
: throw new NotSupportedException($"Unknown data type {propertyInfo.PropertyType}");

In BaseExpressionProvider GetBinaryExpressionSql need to be updated to test if either part is a ExpressionType.Convert, if so it needs to assign the operand property as the part.
Expression[] GetBinaryExpressionParts()
{
Expression[] parts = new[] { binaryExpression.Left, binaryExpression.Right };

            for (int i = 0; i < parts.Length; i++)
            {
                var part = parts[i];

if (part.NodeType == ExpressionType.Convert)
{
UnaryExpression unaryExpression = part as UnaryExpression;
if (unaryExpression != null)
{
parts[i] = unaryExpression.Operand;
}
}
}

            if (binaryExpression.Method is null)
            {
                if (binaryExpression.Left is MemberExpression leftMemberExpression && leftMemberExpression.Type == typeof(bool))
                    parts[0] = Expression.IsTrue(binaryExpression.Left);
                if (binaryExpression.Right is MemberExpression rightMemberExpression && rightMemberExpression.Type == typeof(bool))
                    parts[1] = Expression.IsTrue(binaryExpression.Right);
            }
            return parts;
        };

Changes.zip

Hi,

just hit the following exception:

MySqlConnector.MySqlException (0x80004005): Identifier name 'LC_TRIGGER_BEFORE_UPDATE_ORGANIZATIONDELETEREQUESTSTATUSTYPEENTITY' is too long

the library should probably respect the max identifier length set by database providers via convention:

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/25ba058c35f6c7f77414df594399a100bac64716/src/EFCore.MySql/Metadata/Conventions/MySqlConventionSetBuilder.cs#L38

like EFCore does here:

https://github.com/dotnet/efcore/blob/2a77dbd6d4bf67378ee9d1a27f0ec68a704ca10c/src/EFCore.Relational/Extensions/RelationalTriggerExtensions.cs#L72

Hello
Thankyou for this wonderful package, i wondering if its possible to create a multiple AfterUpdate with diffrents Names?

I have the following which I took from examples I have seen

    builder.AfterUpdate(trigger => trigger
        .Action(action => action
            .Insert<CalendarAuditEntity>(
                (oldUpdatedEntity, newUpdatedEntity) => oldUpdatedEntity.ToCalendarAudit()
            )));

When I run this through a IEntityTypeConfiguration instance I am getting a NotImplementedException and for the life of me I cannot see what is wrong with this. I have a base table builder is the Calendar table and on update I want to insert the updated record into the CalendarAudit table.

NOTE: oldUpdatedEntity.ToCalendarAudit() returns a new CalendarAuditEntity instance.

Any help would be appreciated!

DotNet Core -> 6.*
EF Core -> 6.*
Laraue.EfCoreTriggers -> 6.1.*

Looks like the json properties are mapped as navigation properties instead of scalar properties, despite being in the same table. Would be great if the GetColumnName() could also look at these navigation properties for determining the column name in such circurmstances.

My temporary solution for now was to make the column a string as I don't plan on querying it for now. I could see how this can get inconvenient as more people start using the native json column implementation in EF Core. I tried to implement a custom DbSchemaRetriever but was running into some strange errors during the design time. It'd also be great if we could add an inbuilt jsonb comparer for nested properties to check for equality on json objects in postgres. Thanks for creating this wonderful package, everything just works as expected.

`public class JsonbMethodCallConverter : IMethodCallVisitor
{
private readonly IExpressionVisitorFactory _visitorFactory;

public JsonbMethodCallConverter(IExpressionVisitorFactory visitorFactory)
{
    _visitorFactory = visitorFactory;
}


public bool IsApplicable(MethodCallExpression expression)
{
    return expression.Method.ReflectedType == typeof(JsonbEfCoreDesignTriggerExtensions) && expression.Method.Name == "ToJsonbNoOpEfComparer";
}


public SqlBuilder Visit(MethodCallExpression expression, VisitedMembers visitedMembers)
{
    var sqlBuilder = _visitorFactory.Visit(expression.Arguments.First(), visitedMembers);
    return sqlBuilder.Append("::jsonb");
}

}
`

I'm trying to install Laraue.EfCoreTriggers.MySql 6.3.2 via Nuget but I get this message:
Laraue.EfCoreTriggers.MySql 6.3.2 is not compatible with netcoreapp3.1

Please add support DateTime values.
Following example produces NullReferenceException:

            Action<EntityTypeBuilder<SourceEntity>> builder = x =>
                x.AfterUpdate(trigger => trigger
                    .Action(action => action
                        .Update<DestinationEntity>(
                            (tableRefs, destinationEntities) 
                                => destinationEntities.StringField == tableRefs.New.StringField + tableRefs.Old.StringField,
                            (tableRefs, destinationEntities) 
                                => new DestinationEntity
                                {
                                    DateTimeValue = DateTime.UtcNow
                                })));

Useful case:

            MofiedOn = DateTime.UtcNow

Just to save time to search for a place with an error:

    public sealed class MemberExpressionVisitor : BaseExpressionVisitor<MemberExpression>
    {
        private string GetTableSql(MemberExpression memberExpression, ArgumentType argumentType)
        {
            if (memberExpression.Member.TryGetNewTableRef(out _))
            {
                return _generator.NewEntityPrefix;
            }
            if (memberExpression.Member.TryGetOldTableRef(out _))
            {
                return _generator.OldEntityPrefix;
            }
            return memberExpression.Expression != null
                ? GetColumnSql(memberExpression.Expression.Type, memberExpression.Member, argumentType)
                : GetMethodSql(memberExpression);
        }
        private string GetMethodSql(MemberExpression memberExpression)
        {
            var method = memberExpression.ToString();
            switch (method)
            {
                case "String.Empty": return _generator.GetSql(string.Empty);
                case "DateTime.UtcNow": return "GetUtcDate()"; // for SQL Server
            }
            throw new InvalidOperationException($"Unknown method: {method}");
        }
    }

var entities = modelBuilder.Model.GetEntityTypes().ToList();

entities.ForEach(entity =>
{
var entityType = entity.GetType();
var fks = entity.GetForeignKeys().ToList();
if(fks.Count == 0)
{
modelBuilder.Entity(entityType)/* EXTENSION METHODS HERE*/
}
});

Is there a way to create triggers by the sample code above? I actually know that using modelBuilder.Entity() is possible. But I would like to create a generic trigger generator.

It seems that the SQL generated for a char property can be wrong in some cases? I was looking into using this library for generating triggers to save History information for Insert, Update and Delete with tables like below

public class Project {
   [Key]
   public int ID { get; set; }
}

public class ProjectHistory {
   [Key]
   public int ID { get; set; }
   public int ProjectID { get; set; }
   public char ChangeType { get; set; }
}

Then, if I create the triggers with something like this (and the equivalent for Update and Delete), so that the ChangeType is set from a constant

modelBuilder.Entity<Project>()
	.AfterInsert(trigger => trigger
		.Action(action => action
			.Insert<ProjectHistory>(fromProject => new ProjectHistory() {
				ProjectID = fromProject.ID
                                ChangeType = 'I',				
			})
		)
	)

I get erroneus SQL like this where the 'I' is not set, but is instead used incorrectly:
CREATE TRIGGER LC_TRIGGER_AFTER_INSERT_PROJECT ON Project AFTER Insert AS BEGIN DECLARE @NewID INT DECLARE InsertedProjectCursor CURSOR FOR SELECT ID FROM Inserted OPEN InsertedProjectCursor FETCH NEXT FROM InsertedProjectCursor INTO @NewID WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO ProjectHistory (\"ChangeType\", \"ProjectID\") SELECT i, @NewID; FETCH NEXT FROM InsertedProjectCursor INTO @NewID END CLOSE InsertedProjectCursor DEALLOCATE InsertedProjectCursor END"

The line to lookout for being "INSERT INTO ProjectHistory (\"ChangeType\", \"ProjectID\") SELECT i, @NewID;".

Unfortunately, I am not currently able to develop for .NET6 so this is coming from library version 5.3.2.

I have a problem with BaseEntity!

When I created Company I want to add a default value for Currency.
My Company Model looks this and He extends AuditableEntityWithSoftDelete and AuditableEntityWithSoftDelete extends BaseEntity.

public class Company : AuditableEntityWithSoftDelete
{
    public string Name { get; set; }
    public string Email { get; set; }
    public string Website { get; set; }
    public string ContactPerson { get; set; }
    public string CustomDomain { get; set; }
    public string Logo { get; set; }
    public string LightLogo { get; set; }
    public string BrandColor { get; set; }
    public CompanyStatus Status { get; set; }
    public int PasscodeLength { get; set; }
}

public class AuditableEntityWithSoftDelete : BaseEntity, ISoftDelete
{
    public bool IsDeleted { get; set; }
}

public class BaseEntity
{
    public Guid Id { get; set; }
    public string Referral { get; set; }
}

I created an AfterInsert trigger.

modelBuilder.Entity<Company>()
               .AfterInsert(trigger => trigger.Action(action => action.Insert(newCompany => 
                new Currency { CompanyId = newCompany.Id, Name = "USD", Code = "USD", Prefix = "USD", TextPrefix = "USD", Suffix = "USD", TextSuffix = "USD", Delimiter = ".", Separator = ",", Decimal = 2 })));

When I added the Company record I want to set CompanyId in Currency table (that is FK), but Id isn't part of Company. That is part of BaseEntity, and when I put like this newCompany.Id, I get an error: DbSet should be added to the DbContext.

Does anyone have any idea how to solve this?

I really like the lib, however I ran into an Problem where I can't figure out the solution myself. I want to save all changes and also deletions to a different (audit/history)table.

I think the code should look something like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Balance>()
        .AfterUpdate(trigger => trigger
            .Action(action => action
                .Insert<Balance>((balanceOld, balanceNew) => balanceOld)
            )
        );
    modelBuilder.Entity<Balance>()
        .AfterDelete(trigger => trigger
            .Action(action => action
                .Insert<Balance>((balance) => balance)
            )
        );
}

However this would insert the Balance again in the same table causing issues with Pk. So my question is if it is somehow possible to change the target table?

One workaround in my mind is using a wrapper object, which I don't need since my Balance object already has a LastModified property.

Would love to see the explicit nullable feature used in your library. One could make sure variables are used correctly and needed checks are added to it.

Is it possible to add to the library support for triggers that are triggered only when certain columns in the table are updated?

expected sql of such a trigger:

CREATE TRIGGER trg_update_trigger
AFTER UPDATE OF column1 ON your_table
FOR EACH ROW
EXECUTE FUNCTION your_update_function();

syntax how it would look like:

modelBuilder.Entity<YourEntity>()
            .AfterUpdateOf(x => x.column1
                .Action(x => x.ExecuteRawSql("some sql")));

How about adding a raw SQL trigger action?

I recently had to create a trigger that generates a notification in postgresql.
(e.g.: "PERFORM pg_notify('insert_item', 'payload');")

But I can't create a custom provider because ITriggerProvider doesn't have an interface for it.

I think plain SQL is easy, but SQL with arguments won't be that easy.
I would be very happy if you could provide that feature.

When writing a boolean constant to a nullable boolean field, the SQL generated is CASE WHEN 0 THEN 1 ELSE 0 END which is invalid and cause an exception An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'

version: 7.0.0-alpha

SQLite has limited support for schema changes and in many cases a table rebuild is required. EF Core does the table rebuilds automatically behind the scenes and it's not obvious when they are happening. EF assumes the domain/EF model fully represents the database schema. Triggers are not part of the model, and they get removed without any warning.
See dotnet/EntityFramework.Docs#4429 for more information.

It would be great to automatically detect if a table rebuild is required and add the CREATE TRIGGER statement to the migration if necessary.

As far as I can tell from the code and my attempts to use it...

It doesn't appear to be possible to modify a new entity as it is being added.
For example:
When an item is being inserted I want the database trigger to populate a field if it is currently null by calling a mapped user-defined function.

my initial attempt looked something like:

modelBuilder
                .HasDbFunction(typeof(EFCustomFunctions).GetMethod(nameof(EFCustomFunctions.GetNextValue), new[] { typeof(string) }))
                .HasName("get_next_value");

modelBuilder.Entity<MyEntity>(e =>
  e.BeforeInsert(trigger => trigger
  .Action(action => action
    .Condition(thing => thing.AutoGeneratedValue == null)
    .Insert(@new => new MyEntity
    {
      AutoGeneratedValue  = EFCustomFunctions.MyCustomFunction(@new.NextValueProviderName),
    })));
);

This would appear to be trying to add an INSERT INTO ... within my trigger, instead of just setting the relevant property of the @new entity before it is inserted.

Also I appear to be getting an error when trying to generate a migration (I assume this is due to the custom user-defined function usage?).
Error:

Expression GetNextValue is not supported
at Laraue.EfCoreTriggers.Common.Builders.Providers.BaseExpressionProvider.GetMethodCallExpressionSql(MethodCallExpression methodCallExpression, Dictionary`2 argumentTypes)

Is there a way I can inject custom SQL into the middle of my trigger instead of having to use the Insert, Update, Upsert or Delete extensions?

Also, should the trigger not expose a .Condition(Expression<Func<IEntity,bool>>) to enable database to only call the trigger function if the precondition is true?

Hello, everyone.

We're using EfCoreTriggers together with our own modified MigrationsModelDiffer. Right now we have to subclass EfCoreTriggers MigrationsModelDiffer since migration code is fully inside GetDifferences. If migrations were implemented via some kind of mixin class that would use ITriggerVisitor to generate and merge migrations operations so it could be chained with another migration modifications.

Made a PR: #76

Hi,

If i have an enum:

public enum ServiceStatus
{
  NotActive,
  Active 
}

and classes Service and ServiceHistory:

public class Service
{
  public ServiceStatus Status { get; set; }
}

public class ServiceHistory
{
  public ServiceStatus OldStatus { get; set; }
  public ServiceStatus NewStatus { get; set; }
}

with:

builder.Entity<Service>().Property(s => s.Status).HasColumnType("varchar(100)").HasConversion<string>();
builder.Entity<ServiceHistory>().Property(s => s.Status).HasColumnType("varchar(100)").HasConversion<string>();

then

builder.Entity<Service>().AfterUpdate(trigger =>
  trigger.Action(action => action.Insert((sold, snew) => new ServiceHistory { OldStatus = sold.Status, NewStatus = snew.Status })))

generates TRIGGER with

... @OldStatus INT ... @NewStatus INT

and then when updating Service "Conversion failed when converting the varchar value 'NotActive' to data type int." is thrown.

Hi,

im using this library and just parallelised the test execution of all of my EF Core tests. But while doing this, I hit the following error:

 Message: 
  Initialization method Ofcas.Odid.IntegrationTests.CoreTest.ServicesTest.AddressServiceTest.TestInitialize threw exception. System.ArgumentException: An item with the same key has already been added. Key: Ofcas.Odid.Core.Entities.AddressEntity.

  Stack Trace: 
    Dictionary`2.TryInsert(TKey key, TValue value, InsertionBehavior behavior)
    Dictionary`2.Add(TKey key, TValue value)
    EfCoreDbSchemaRetriever.GetTableName(Type entity)
    TriggerMethodCallConverter.Visit(MethodCallExpression expression, ArgumentTypes argumentTypes, VisitedMembers visitedMembers) line 27
    MethodCallExpressionVisitor.Visit(MethodCallExpression expression, ArgumentTypes argumentTypes, VisitedMembers visitedMembers)
    ExpressionVisitorFactory.Visit[TExpression](TExpression expression, ArgumentTypes argumentTypes, VisitedMembers visitedMembers)
    ExpressionVisitorFactory.Visit(Expression expression, ArgumentTypes argumentTypes, VisitedMembers visitedMembers)
    TriggerRawActionVisitor.Visit(TriggerRawAction triggerAction, VisitedMembers visitedMembers)
    TriggerActionVisitorFactory.Visit[T](T triggerAction, VisitedMembers visitedMembers)
    TriggerActionVisitorFactory.Visit(ITriggerAction triggerAction, VisitedMembers visitedMembers)
    SqliteTriggerVisitor.<GenerateCreateTriggerSql>b__3_0(ITriggerAction action)
    SelectListIterator`2.ToArray()
    SqliteTriggerVisitor.GenerateCreateTriggerSql(ITrigger trigger)
    MigrationsExtensions.ConvertTriggerAnnotationsToSql(ITriggerVisitor triggerVisitor, IModel model)
    MigrationsModelDiffer.GetDifferences(IRelationalModel source, IRelationalModel target)
    RelationalDatabaseCreator.GetCreateTablesCommands(MigrationsSqlGenerationOptions options)
    RelationalDatabaseCreator.CreateTables()
    SQLiteConnectionTestHelper.TestInitialize() line 53

I am using a custom MethodCallConverter called TriggerMethodCallConverter, which injects IDbSchemaRetriever.

public class TriggerMethodCallConverter : BaseMethodCallVisitor
{
    private readonly IDbSchemaRetriever _dbSchemaRetriever;

    protected override Type ReflectedType => typeof(TriggerFunctions);

    protected override string MethodName => nameof(TriggerFunctions.GetDbTableName);

    public TriggerMethodCallConverter(IDbSchemaRetriever dbSchemaRetriever, IExpressionVisitorFactory visitorFactory) : base(visitorFactory)
    {
        _dbSchemaRetriever = dbSchemaRetriever;
    }

    public override SqlBuilder Visit(MethodCallExpression expression, ArgumentTypes argumentTypes, VisitedMembers visitedMembers)
    {
        var genericArguments = expression.Method.GetGenericArguments();
        return SqlBuilder.FromString(_dbSchemaRetriever.GetTableName(genericArguments[0]));
    }
}

Because IDbSchemaRetriever.GetTableName caches the table names in a static variable, I assume i run into a race condition here:

I think instead of a static variable, the cache should be managed by the dependency injection container of EFCore. Then each of my DbContexts will have a different cache.

The other option would be to use a concurrent dictionary, but I feel like that is not 100% clean.

Furthermore you might still need a concurrent dictionary using the first option, I am not 100% sure how the concurrency guarantees are, if two DbContext instances share the same dependency container.

I plan to have several entities that implement common interfaces. For example, I might have an interface that includes an Inactive flag. For ease of maintenance, I want to automatically identify entities that implement these interfaces and add triggers to them. The problem I came up against is that the types returned by methods like IMutableModel.GetEntityTypes() are not generic. And pretty much everything in Laraue.EfCoreTriggers.Common operates on generics.

So I forked the lib and started digging. I think it would be pretty simple to add support for non-generics like EntityTypeBuilder and IMutableEntityType. The only fundamental problem is that Trigger<T> assumes the trigger type (the type that actions operate on) and the entity type are one and the same. I made a few simple changes to allow the types to be distinguished while maintaining backward compatibility. The Trigger constructor takes a parameter identifying the entity type. If it's null, it falls back to the generic type. This small change was all I needed for this proof of concept (Action and Throw are extension methods in my project):

var types = builder.Model.GetEntityTypes().Where(t => t.ClrType.IsAssignableTo(typeof(RowVersionedEntity)));
foreach (var type in types)
{
	var trigger = new OnUpdateTrigger<RowVersionedEntity>(TriggerTime.After, type.ClrType);
	trigger.Action(actions => actions.Throw(50000, "You foolian!"));
	type.AddAnnotation(trigger.Name, trigger);
}

Other classes in Common could have methods or constructors to expose this. What do you think?

https://github.com/kjkrum/Laraue.EfCoreTriggers/tree/non-generic-support

Hi I am a newer to this. I am using another EF Core trigger extension and I was attracted to this extension. When I want to migrate to this, a subtle but hard problem come to me.
My database has many tables, and all these tables has three common columns including id, createTime and updateTime. Thus I use a BaseEntity abstract class, which involves the three columns, as all tables parent class. I hope to create some triggers to solve all tables' update operations. When a row is updated, the column updateTime is updated by the operation time, whatever entitis the row is.
It's easy to complete it when I use another extension. However that extension is a "runtime" trigger extension and I want to make it be real triggers in the database . Unfortunately, it seems not easy to reach the same function with this extension. I found #50 maybe helpful. But after trying it, I can not do the update operations.
I am using postgresql and EF Core 7. I really want to know how to make this function.

Here is my configuration for my entity:

public void Configure(EntityTypeBuilder<Categorie> builder)
{
    string sql = @"IF EXISTS (
		     		    SELECT 1
		     		    FROM inserted i
		     		    JOIN dbo.categories p ON i.parentid = p.id
		     		    WHERE p.parentid IS NOT NULL
		 		    )
		 		    BEGIN
		     		    RAISERROR('Cannot insert more than one level deep', 16, 1);
		     		    ROLLBACK TRANSACTION;
		     		    RETURN;
		 		    END;";
    
    builder.HasOne(c => c.Parent)
	    .WithMany()
	    .HasForeignKey(c => c.ParentId)
	    .OnDelete(DeleteBehavior.Restrict); // Don't delete parent if child exists
    
    builder.AfterInsert(trigger => trigger
			    .Action(action => action.ExecuteRawSql(sql)));
}

What I was expecting is that I'd get a trigger written like this:

CREATE TRIGGER prevent_deep_inserts
ON dbo.Categories
AFTER INSERT
AS
BEGIN
  IF EXISTS (
		   SELECT 1
		   FROM inserted i
		   JOIN dbo.categories p ON i.parentid = p.id
		   WHERE p.parentid IS NOT NULL
  )
  BEGIN
	RAISERROR('Cannot insert more than one level deep', 16, 1);
	ROLLBACK TRANSACTION;
	RETURN;
  END;
END;

Instead, I got a trigger written like this:

USE [BIBLIOTHEQUE]
GO
/****** Object:  Trigger [dbo].[LC_TRIGGER_AFTER_INSERT_CATEGORIE]    Script Date: 2023-04-27 11:51:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[LC_TRIGGER_AFTER_INSERT_CATEGORIE] ON [dbo].[Categories] AFTER Insert AS
BEGIN
  DECLARE InsertedCategorieCursor CURSOR LOCAL FOR SELECT * FROM Inserted
  OPEN InsertedCategorieCursor
  FETCH NEXT FROM InsertedCategorieCursor
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF EXISTS (
		     				SELECT 1
		     				FROM inserted i
		     				JOIN dbo.categories p ON i.parentid = p.id
		     				WHERE p.parentid IS NOT NULL
		 				)
		 				BEGIN
		     				RAISERROR('Cannot insert more than one level deep', 16, 1);
		     				ROLLBACK TRANSACTION;
		     				RETURN;
		 				END;
  FETCH NEXT FROM InsertedCategorieCursor
  END
  CLOSE InsertedCategorieCursor DEALLOCATE InsertedCategorieCursor
END

It seems that if I try to update a table on a delete trigger, but in the finding of the rows to update I compare a property to a constant char, incorrect SQL is generated. If I have tables like below

public class Project {
   [Key]
   public int ID { get; set; }
}

public class ProjectHistory {
   [Key]
   public int ID { get; set; }
   public int ProjectID { get; set; }
   public char ChangeType { get; set; }
}

and this time, instead of inserting a new ProjectHistory row, I want to find a suitable history row to update with a trigger like this:

modelBuilder.Entity<Project>()			
   .AfterDelete(trigger => trigger
	.Action(action => action
		.Update<ProjectHistory>(
			(deletedProject, findHistory) =>
				findHistory.ChangeType == 'U' &&
				findHistory.ProjectID == deletedProject.ID,
			(deletedProject, oldHistory) => new ProjectHistory() {
				ChangeType = 'D'
			}
		)
	)
);

then everything else goes fine, but the comparison of the ChangeType is incorrect:

ALTER TRIGGER [dbo].[LC_TRIGGER_AFTER_DELETE_PROJECT] ON [dbo].[Project] AFTER Delete AS
BEGIN
  DECLARE @OldID INT
  DECLARE DeletedProjectCursor CURSOR FOR SELECT ID FROM Deleted
  OPEN DeletedProjectCursor
  FETCH NEXT FROM DeletedProjectCursor INTO @OldID
  WHILE @@FETCH_STATUS = 0
  BEGIN
    UPDATE ProjectHistory
    SET ChangeType = 'D'
    WHERE CAST(ProjectHistory.ChangeType AS INT) = 85 AND ProjectHistory.ProjectID = @OldID;
    FETCH NEXT FROM DeletedProjectCursor INTO @OldID
  END
  CLOSE DeletedProjectCursor DEALLOCATE DeletedProjectCursor
END

and deletion from Project fails with

"Msg 245, Level 16, State 1, Procedure LC_TRIGGER_AFTER_DELETE_PROJECT, Line 9 [Batch Start Line 25]
Conversion failed when converting the nvarchar value 'U' to data type int."

Unfortunately, I am not currently able to develop for .NET6 so this is coming from library version 5.3.3.

You could use TestContainers for the UnitTests.

With that you could start a database instance for each specific test,

Is there any way, or can a way be added, to abort the update if a condition is not met, or to alter the incoming value before the update happens?

I am trying to implement a trigger for a RowVersion column, where I only allow the update to happen if the RowVersion on the new row matches the RowVersion on the old row, and to increment it if it is allowed, and I need this functionality in order for it to work.

For PostgreSQL:

   builder.BeforeDelete(trigger => trigger
            .Action(action => action
                .Condition(v => v.PublishedId != null)
                .ExecuteRawSql("RAISE EXCEPTION 'Forbidden';")
            )
        );

generate

migrationBuilder.Sql("CREATE FUNCTION \"core_service\".\"LC_TRIGGER_BEFORE_DELETE_FORM\"() RETURNS trigger as $LC_TRIGGER_BEFORE_DELETE_FORM$\r\nBEGIN\r\n  IF OLD.\"published_id\" IS NOT NULL THEN \r\n    RAISE EXCEPTION 'Forbidden';\r\n  END IF;\r\nRETURN NEW;\r\nEND;\r\n$LC_TRIGGER_BEFORE_DELETE_FORM$ LANGUAGE plpgsql;\r\nCREATE TRIGGER LC_TRIGGER_BEFORE_DELETE_FORM BEFORE DELETE\r\nON \"core_service\".\"forms\"\r\nFOR EACH ROW EXECUTE PROCEDURE \"core_service\".\"LC_TRIGGER_BEFORE_DELETE_FORM\"();");

but function for trigger must RETURN OLD (not RETURN NEW) in this case, otherwise row will not be removed

https://github.com/win7user10/Laraue.EfCoreTriggers/blob/master/src/Laraue.EfCoreTriggers.PostgreSql/PostgreSqlTriggerVisitor.cs#L50

Hi,

As mention in topic, can we configure the library or is there a way to use snake case as column name?

I have run migration and its seems to working fine but there is an error when trigger occur as I have use snake case column name in my database as default.

for example,
if I have these column name in my DB

id, some_id, some_value

and in my code like so

public class SomeClass
{
   public Guid Id { get; set; }
   public Guid SomeId { get; set; }
   public string SomeValue { get; set; }
}

It could not map column name because the trigger script generate the column as

Id, SomeId, SomeValue

which will translate to

id, someid, somevalue

BTW, I have use PostgreSQL as my database.

There are quotes on the trigger function create statement, and no quotes on the drop statement.

This causes subsequent Up migrations to fail, as the trigger/function cannot be found when dropped.
This is the case for the Down as well.

Laraue.EfCoreTriggers.Common: 7.0.4 (also tested on 7.0.3 - 7.0.0)
Laraue.EfCoreTriggers.PostgreSql: 7.0.4 (also tested on 7.0.3 - 7.0.0)

modelBuilder.Entity()
.AfterUpdate(trigger => trigger
.Action(action => action
.Condition((oldTransaction, newTransaction) => oldTransaction.ViewCount + 1 == newTransaction.ViewCount)
.Update(
(oldTransaction, updatedTransaction, user) => user.Guid == oldTransaction.CreatedBy,
(oldTransaction, updatedTransaction, user) => new Users { BlogPostViewCount = user.BlogPostViewCount + 1 })))

at Microsoft.EntityFrameworkCore.RelationalEntityTypeExtensions.GetTableName(IEntityType entityType)
at Laraue.EfCoreTriggers.Common.Builders.Providers.BaseExpressionProvider.GetTableName(Type entity)
at Laraue.EfCoreTriggers.Common.Builders.Providers.BaseExpressionProvider.GetTableName(MemberInfo memberInfo)
at Laraue.EfCoreTriggers.Common.Builders.Providers.BaseExpressionProvider.GetMemberExpressionSql(MemberExpression memberExpression, ArgumentType argumentType)
at Laraue.EfCoreTriggers.Common.Builders.Providers.BaseExpressionProvider.GetMemberExpressionSql(MemberExpression memberExpression, Dictionary2 argumentTypes) at Laraue.EfCoreTriggers.Common.Builders.Providers.BaseExpressionProvider.GetUnaryExpressionSql(UnaryExpression unaryExpression, Dictionary2 argumentTypes)
at Laraue.EfCoreTriggers.Common.Builders.Providers.BaseExpressionProvider.GetExpressionSql(Expression expression, Dictionary2 argumentTypes) at Laraue.EfCoreTriggers.Common.Builders.Providers.BaseExpressionProvider.<>c__DisplayClass39_0.<GetBinaryExpressionSql>b__1(Expression part) at System.Linq.Enumerable.SelectArrayIterator2.MoveNext()
at Laraue.EfCoreTriggers.Common.Builders.Providers.SqlBuilder.MergeColumnsInfo(IEnumerable1 generatedSqls) at Laraue.EfCoreTriggers.Common.Builders.Providers.SqlBuilder..ctor(IEnumerable1 generatedSqls)
at Laraue.EfCoreTriggers.Common.Builders.Providers.BaseExpressionProvider.GetBinaryExpressionSql(BinaryExpression binaryExpression, Dictionary2 argumentTypes) at Laraue.EfCoreTriggers.Common.Builders.Providers.BaseTriggerProvider.GetConditionStatementSql(LambdaExpression conditionExpression, Dictionary2 argumentTypees)
at Laraue.EfCoreTriggers.Common.Builders.Providers.BaseTriggerProvider.GetTriggerUpdateActionSql[TTriggerEntity,TUpdateEntity](TriggerUpdateAction2 triggerUpdateAction) at Laraue.EfCoreTriggers.Common.Builders.Triggers.Base.TriggerUpdateAction2.BuildSql(ITriggerProvider visitor)
at Laraue.EfCoreTriggers.Common.Builders.Providers.MySqlProvider.b__11_0[TTriggerEntity](ITriggerAction action)
at System.Linq.Enumerable.SelectListIterator2.MoveNext() at Laraue.EfCoreTriggers.Common.Builders.Providers.SqlBuilder.MergeColumnsInfo(IEnumerable1 generatedSqls)
at Laraue.EfCoreTriggers.Common.Builders.Providers.MySqlProvider.GetTriggerActionsSql[TTriggerEntity](TriggerActions1 triggerActions) at Laraue.EfCoreTriggers.Common.Builders.Triggers.Base.TriggerActions1.BuildSql(ITriggerProvider visitor)
at Laraue.EfCoreTriggers.Common.Builders.Providers.MySqlProvider.b__12_0[TTriggerEntity](ISqlConvertible action)
at System.Linq.Enumerable.SelectListIterator2.MoveNext() at Laraue.EfCoreTriggers.Common.Builders.Providers.SqlBuilder.MergeColumnsInfo(IEnumerable1 generatedSqls)
at Laraue.EfCoreTriggers.Common.Builders.Providers.SqlBuilder..ctor(IEnumerable1 generatedSqls) at Laraue.EfCoreTriggers.Common.Builders.Providers.MySqlProvider.GetTriggerSql[TTriggerEntity](Trigger1 trigger)
at Laraue.EfCoreTriggers.Common.Builders.Triggers.Base.Trigger1.BuildSql(ITriggerProvider visitor) at Laraue.EfCoreTriggers.Extensions.EntityTypeBuilderExtensions.AddTriggerAnnotation[T](EntityTypeBuilder1 entityTypeBuilder, Trigger1 configuredTrigger) at Laraue.EfCoreTriggers.Extensions.EntityTypeBuilderExtensions.AddOnUpdateTrigger[T](EntityTypeBuilder1 entityTypeBuilder, Action1 configuration, TriggerTime triggerTime) at Laraue.EfCoreTriggers.Extensions.EntityTypeBuilderExtensions.AfterUpdate[T](EntityTypeBuilder1 entityTypeBuilder, Action`1 configuration)

Is there a way to get Entity in my c# code that inserted, updated, or deleted?
for example, I want to get that entity in the trigger action:

            modelBuilder.Entity<TEntity>()
                .AfterInsert(trigger => trigger.Action(action =>
                {
                    action.OnChange((entity) =>
                    {
                        Console.WriteLine($"Inserted {entity.Id}");
                    });
                }))
                .AfterDelete(trigger => trigger.Action(action =>
                {
                    action.OnChange((entity) =>
                    {
                        Console.WriteLine($"Deleted {entity.Id}");
                    });
                }))
                .AfterUpdate(trigger => trigger.Action(action =>
                {
                    action.OnChange((entity) =>
                    {
                        Console.WriteLine($"Updated {entity.Id}");
                    });
                }));

version: 7.0.0-alpha2

Hi, I am having a very difficult time understanding how to create a simple 'after update' trigger using your library. I am using MS SQL.

I need to script triggers to update columns with SQL built-in functions, e.g.:

`CREATE TRIGGER [dbo].[MyEntityTable_UPDATE] ON [dbo].[MyEntityTable]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

IF ((SELECT TRIGGER_NESTLEVEL()) > 1) RETURN; --optional

DECLARE @Id INT

SELECT @Id = INSERTED.Id
FROM INSERTED

UPDATE dbo.MyTable
SET ModifiedDate = SYSUTCDATETIME(),
	LoggedInUser = SUSER_NAME()
WHERE Id = @Id

END`

What is the correct way to use your library to do this? Thank you.

edit: I should also mention, the entity properties ModifiedDate and LoggedInUser are read-only, since the data is generated in the database.

Is it possible to configure a trigger like this one with this library?

What i'm trying to achieve is to delete one side of a many to many relationship table if this is the last row pointing to it

Tables:
-Persons
-PersonProducts
-Products

i want the product to be deleted when the last PersonProduct referencing it is deleted

builder
                .AfterDelete(trigger => trigger
                    .Action(action => action
                        .Condition(pp => pp.Product!.PersonProducts.Count() == 1)
                        .Delete<Product>((personProduct, product) => personProduct.ProductId == product.Id)
                    )
                );

Laraue.EfCoreTriggers.Common 6.3.2
Laraue.EfCoreTriggers.SqlServer 6.3.2
Microsoft.EntityFrameworkCore.SqlServer 6.0.7
Microsoft.EntityFrameworkCore.Tools 6.0.7

I created a test trigger that does nothing:

builder.Entity<User>().AfterUpdate(trigger =>
{
	trigger.Action(action =>
	{
		action.ExecuteRawSql("NULL");
	});
});

Then I ran add-migration and got this error:

Cannot scaffold C# literals of type Laraue.EfCoreTriggers.Common.TriggerBuilders.OnUpdate.OnUpdateTrigger`1[MyProject.Entities.User]'. The provider should implement CoreTypeMapping.GenerateCodeLiteral to support using it at design time.

Hi. I have two triggers on an entity, one AfterInsert(t => t.Action(a => a.Update(...))) and one AfterUpdate(t => t.Action(a => a.Update(...))). These both get the same cursor name Inserted<ENTITY_NAME>Cursor.
This causes an error when I try to insert a row to the table:

A cursor with the same name 'InsertedMyEntityCursor' already exists.

Am I doing something wrong, or should each trigger instead be gettting their own uniquely named cursor?

My simple scenario here is setting a CreatedDate after insert and ModifiedDate after update.

At the moment EF.Property is not supported.
I tried adding it as an IMethodCallVisitor but it gives me an error:

Expression of type System.Linq.Expressions.TypedParameterExpression is not supported

I guess it's because of the first argument which is the instance of the entity.

I'm using it as argumentSelectors parameter in ExecuteRawSql with something like that:

modelBuilder.Entity<Brand>().InsteadOfDelete(a => a.Action(a => a.ExecuteRawSql("... = {0}", b => EF.Property<int>(b, "MyId"))));

but it think it should be the same in other expressions.

I would need this feature to allow triggers to target a shadow property (which is mapped to a column) of the entity, my question is: how can I replace this condition correctly?
So that it gets added to the list of columns which get fetched into the cursor, and generates the correct SQL.
I don't think IMethodCallVisitor is the most appropriate method at this point.

From a quick look at the code (specially the TriggerVisitor) I see that for each visited member should be declared on the entity, right?

My issue here is that if I am trying to create triggers for auditing, I have to specify each column individually or execute a raw SQL query that looks something like this:

INSERT INTO auditing (SELECT * FROM source WHERE source.something = something)

The issue with this is that the auditing table has to look exactly the same as the source table. But that only allows insertion of one entry per element if you want to have the ID as a primary key. I would want to have an "AuditId" and a "SourceId" but that is not possible when using this query!

So I think it would be cool to have a method that you can use similarly to methods like Insert, Update, Upsert and so on called Audit

Would be cool if this could automatically map the columns with the same name and then you would have the ability to attach another method or perhaps a function argument that maps the rest of the values.

Usage:

 modelBuilder.Entity<Source>()
     .BeforeUpdate(trigger => trigger
         .Action(action => action
           //Does the automatic mapping
            .Audit<Source, Audit>()
            .HandleUnmapped( (source, audit) => audit.Action = "Update" )));

Hi, I am trying to use the most recent version (8.0.3) of the package in my .Net 6 project, but I can't add the package because it says that it is not compatible with net6.0.

Yet, if I open my package manager, I see that 8.0.3 supports .Net 6.

For it to work, I have to go down to 7.1.1, which is the last version that doesn't target .Net 8.

Even if I try the next subversion, 7.1.2, I have the error.

Then I went to check online, if I go to the NuGet page for the 7.1.1, there is tag for .Net 6, but if I go to the 7.1.2, it is now .Net 8.

Also, if I navigate to the package locally, there is no folder for .Net 6.

My hypothesis is that either the .net 6 version is not compiled anymore, or that it nerver was, and the dependencies in my Visual Studio are wrong.