BeforeInsertTriggerAction cannot make change to insertingEntity.

[izzycoding]

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?

[win7user10]

Hi, @izzycoding. Unfortunately, using EF functions is not supported in the library now. You can add support of some expression adding specified code to the method GetMethodCallExpressionSql() of BaseExpressionProvider class.

Using .Condition(Expression<Func<IEntity,bool>>) is not necessary if precondition is always true.

[izzycoding]

Hi,

Thanks for the reply.

I have started looking at adding support for user-defined functions. Also, adding support for each provider to specify their own database functions as I use more than just lower case upper case.

For the condition, shouldn’t it be possible to put it on the trigger and not the body of the trigger? Will post example trigger SQL when I get back to my desk.

Regards,

On 20 Mar 2021, at 08:44, Ilya Belyanskiy @.***> wrote:

 Hi, @izzycoding. Unfortunately, using EF functions is not supported in the library now. You can add support of some expression adding specified code to the method GetMethodCallExpressionSql() of BaseExpressionProvider class.

Using .Condition(Expression<Func<IEntity,bool>>) is not necessary if precondition is always true.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[win7user10]

Opportunity for translation of user-defined functions added. Converter for this case will be look like

public abstract class EFCustomFunctionsMyCustomFunctionConverter : MethodCallConverter
{
    public override bool IsApplicable(MethodCallExpression expression)
    {
        return expression.Method.ReflectedType == typeof(EFCustomFunctions) && MethodName == nameof(EFCustomFunctions.MyCustomFunction);
    }

    public override SqlBuilder BuildSql(BaseExpressionProvider provider, MethodCallExpression expression, Dictionary<string, ArgumentType> argumentTypes)
    {
        var argumentSql = provider.GetMethodCallArgumentsSql(expression, argumentTypes)[0];
        return new(sqlBuilder.AffectedColumns, $"CustomSqlFunction({argumentSql})");
    }
}