Creating a change history/audit info in a different table

[fritz-net]

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.

[win7user10]

Hi, if I understood you right, you want to save data in the table with the same fields but with a different name. I think in this case you can create a model inherited from the main model

[Table("blances_logs")]
class BalanceLog : Balance
{
}

and then use syntax

modelBuilder.Entity<Balance>()
  .AfterDelete(trigger => trigger
    .Action(action => action
      .Insert<BalanceLog>((balance) => new BalanceLog { Field1 = balnce.Field1 })
    )
  );

The library now hasn't the opportunity to use one model for different tables

[fritz-net]

Thanks for your fast response. You interpreted my question correct.

Your solution seems to be the cleanest - better than changing something in the lib for an edgecase.

I tried it, however I get the following Error when creating the Migration: Unable to cast object of type 'System.Linq.Expressions.TypedParameterExpression' to type 'System.Linq.Expressions.MemberExpression'.

             modelBuilder.Entity<Balance>()
                 .AfterUpdate(trigger => trigger
                     .Action(action => action
                         .Insert<BalanceHistory>((BalanceOld, BalanceNew) => new BalanceHistory(BalanceOld))
                     )
                 );
             modelBuilder.Entity<Balance>()
                 .AfterDelete(trigger => trigger
                     .Action(action => action
                         .Insert<BalanceHistory>(Balance => new BalanceHistory(Balance))
                     )
                 );

EDIT: I didn't pay attention to your example. A copy constructor will not be convertible. However if I do it like in your example I get Value cannot be null. (Parameter 'property') Tomorrow I will further investigate the issue.

[fritz-net]

The issue seems to be that I can't use any objects (as properties) from the original row. So im not sure how to handle references I had to other tables before.

If I uses constants(/predefined vars) as dummy data I get: Unable to cast object of type 'System.Linq.Expressions.ConstantExpression' to type 'System.Linq.Expressions.ParameterExpression'.

If I do something like this AccountId = balanceOld.Account.AccountId, I get Unable to cast object of type 'System.Linq.Expressions.PropertyExpression' to type 'System.Linq.Expressions.ParameterExpression'.

If I just want to set the time MovedToHistory = DateTime.Now I get Object reference not set to an instance of an object.. However DateTime as type works if I copy it from the original.

EDIT: the feature I want to mimic with this trigger is called "Temporal tables" (from MsSql). So I don't think its a to niche use case. Postgresql sadly has not implemented this yet.

This is how the solution could look like without the lib: https://stackoverflow.com/questions/38954139/implementing-history-of-postgresql-table

[fritz-net]

My current solution to get this working was implementing RawSql triggers ( https://github.com/fritz-net/Laraue.EfCoreTriggers/commit/8f977649e53268f0c10d733f103cf8ce3bddeba1 ) as described in #16

modelBuilder.Entity<Balance>()
.AfterDelete(trigger => trigger
    .Action(action => action
        .RawSql(@"
            EXECUTE 
            'INSERT INTO ""BalanceHistory"" '
            '('
                '""BalanceHistoryId"",'
                '""BalanceId"",'
                -- ...
                '""Reason""'
           ' ) VALUES ('
                'gen_random_uuid(),'
                '$1.""BalanceId"",'

                -- ...

                '1' -- 0=update, 1=delete
            ');'
            USING OLD
        ")
    )
);