Insert/Update filter - Inserting to another table when there is a transaction

Inside of a filter I’m trying to insert into another table. There is an existing transaction however the queries in the filter never save.

Somewhere in the app.

public void DoSomething() {
  using (var trans = Db.OpenTransaction()) {
   Db.Save(new Test() { Id=5});
  }
}
```

Filter:

```csharp
  OrmLiteConfig.InsertFilter = (dbCmd, row) => {
                
                if (row.GetType().GetCustomAttribute<NoTrackAttribute>()!=null) return;
                IDbConnection db = dbCmd.Connection;
                if (dbCmd.Transaction!=null)
                {
                    db = dbCmd.Transaction.Connection;
                
                }
                try
                {
                    var modelDef = row.GetType().GetModelMetadata();
                    //var name = modelDef.ModelName;
                    var dialectProvider = dbCmd.GetDialectProvider();
                    var tableName = dialectProvider.NamingStrategy.GetTableName(modelDef);



                    var key = modelDef.GetPrimaryKey(row);
                    var auditLog = new AuditLog();
                    auditLog.EventDateUTC = DateTime.UtcNow;
                    auditLog.TableName = tableName;
                    auditLog.EventType = 1;  // This is hard coded, not sure what it needs to be
                    auditLog.RecordId = key.ToString();
                   
                        var identity = db.Insert<AuditLog>(auditLog, selectIdentity: true);  // fails here
                        List<AuditLogDetails> details = new List<AuditLogDetails>();



                        foreach (var def in modelDef.FieldDefinitions)
                        {
                            if (def == null || def.IsComputed || def.IgnoreOnInsert) continue;

                            var record = new AuditLogDetails()
                            {
                                AuditLogId = (int)identity,
                                ColumnName = def.Alias?.ToString() ?? def.Name, //def.FieldName might be too
                                NewValue = def.GetValue(row)?.ToString() ?? "",
                            };
                            db.Insert<AuditLogDetails>(
                           record);

                        }
                    
                    
                } catch (Exception ex)
                {
                    Log.Logger.Debug(ex.Message); 
                }
                
            };
```

It fails with this:

ExecuteScalar requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized.

Please wrap any C# source code in backticks, e.g:

```csharp
//C# Source code
```

Please provide a complete stand-alone example that shows the issue (please do this for all issues). Preferably an executable snippet on Gistlyn otherwise provide the complete source code I can copy + run to show the issue.

Also when there’s an Exception please include the full StackTrace.

Sorry I wasn’t necessarily reporting an issue but asking for advice. I’ll update it with more source code but the idea is pretty simple: Do some operation in a transaction and inside the insert/update filter insert into another table.

Here is a boiled down gistlyn.

https://gistlyn.com/?gist=029f09379428a68a27194d6155839df4&collection=2cc6b5db6afd3ccb0d0149e55fdb3a6a

Unfortunately, it just stays on executing script forever. I also updated my first post.

The OrmLiteConfig.InsertFilter just gets called with the object before it gets inserted. It’s useful for doing things like validation or auto populating properties.

The dbCmd is part already configured with the transaction but OrmLite APIs that use IDbCommand are internal so you wont be able to access them. You’re going to have issues trying to participate within the same transaction because as the Error says the open dbCmd is still pending so you can’t create and execute a new command while it’s still pending.

If you want to include it apart from the same transaction you’d need to use the open dbCmd which will require accessing internal APIs with something like:

dialectProvider.PrepareParameterizedInsertStatement<AuditLogDetails>(dbCmd, 
    insertFields: OrmLiteUtils.GetNonDefaultValueInsertFields(record));

dialectProvider.SetParameterValues<AuditLogDetails>(dbCmd, obj);
dbCmd.ExecNonQuery();

Otherwise you wont be able to include it as part of the transaction unless you don’t rely on the filter and add the Audit record as normal OrmLite command, e.g:

public void DoSomething() 
{
    using (var trans = Db.OpenTransaction()) 
    {
        var record = new Test { Id=5};
       Db.Insert(CreateAuditRecord(record));
       Db.Save(record);
     }
}

Thank you for the quick response.