How to replicate SQL queries generated by the ORM nHibernate?

Question

In a C# application, using nHibernate & fluent nHibernate, we're looking to capture certain INSERT, UPDATE, DELETE queries performed on a Sqlite database in order to replicate them. We'd like to replicate only certain queries that affect certain entity types.

After several searches and tests, we haven't found a way to do this.
We have developed a solution using an Interceptor as well as Listeners, but neither solution seems to work. An Interceptor can be used to retrieve the generated SQL, but we lack context for its application. Listeners (IPostInsertEventListener, IPostUpdateEventListener, IPostDeleteEventListener) have many more parameters, but we can't find a way to retrieve the raw SQL query.

Using Interceptor by overriding OnPrepareStatement(SqlString sql) is the simplest method, but it lacks the context of the entity on which the operation has been performed.

public override SqlString OnPrepareStatement(SqlString sql)
{
    // Only raw SQL
    Console.WriteLine(sql);
    return base.OnPrepareStatement(sql);
}

And by using Listeners, we get a lot more information, we can understand the context and the entity that is being modified, but we can't find the SQL generated.

    public class QueryListener(Configuration cfg) : IPreUpdateEventListener, IPostUpdateEventListener, IPreInsertEventListener, IPostInsertEventListener
    {
        public void Register()
        {
            cfg.EventListeners.PreUpdateEventListeners = [.. cfg.EventListeners.PreUpdateEventListeners, this];
            cfg.EventListeners.PostUpdateEventListeners = [.. cfg.EventListeners.PostUpdateEventListeners, this];
            cfg.EventListeners.PreInsertEventListeners = [.. cfg.EventListeners.PreInsertEventListeners, this];
            cfg.EventListeners.PostInsertEventListeners = [.. cfg.EventListeners.PostInsertEventListeners, this];
        }

        public void OnPostUpdate(PostUpdateEvent @event)
        {
            // Capture SQL here ?
        }

        public bool OnPreUpdate(PreUpdateEvent @event)
        {
            // Capture SQL here ?
            return false;
        }

        public bool OnPreInsert(PreInsertEvent @event)
        {
            // Capture SQL here ?
            return false;
        }

        public void OnPostInsert(PostInsertEvent @event)
        {
            // Capture SQL here ?
        }

        #region Async methods
        public Task OnPostUpdateAsync(PostUpdateEvent @event, CancellationToken cancellationToken)
        {
            OnPostUpdate(@event);
            return Task.CompletedTask;
        }

        public Task<bool> OnPreUpdateAsync(PreUpdateEvent @event, CancellationToken cancellationToken)
        {
            return Task.FromResult(OnPreUpdate(@event));
        }

        public Task<bool> OnPreInsertAsync(PreInsertEvent @event, CancellationToken cancellationToken)
        {
            return Task.FromResult(OnPreInsert(@event));
        }

        public Task OnPostInsertAsync(PostInsertEvent @event, CancellationToken cancellationToken)
        {
            OnPostInsert(@event);
            return Task.CompletedTask;
        } 
        #endregion
    }

Ideally, we'd like to reproduce the delta mechanism proposed by SyncFramework (https://github.com/egarim/SyncFramework/tree/main).

Thanks in advance for your help!

Answer 1

keeping some form of context within your interceptor? If the order of the method calls is compatible and you set the context, it will be available when the OnPrepareStatement method is called.

Something like:

public class SqlLoggingInterceptor : EmptyInterceptor {
    private object _currentEntity;

    public override bool OnSave(object entity, 
                                object id, 
                                object[] state,
                                string[] propertyNames,
                                IType[] types)
    {
        _currentEntity = entity;

        return true;
    }

    public override SqlString OnPrepareStatement(SqlString sql)
    {
        // Use _currentEntity here

        return base.OnPrepareStatement(sql);
    }
    
}

If I've read the documentation correctly, interceptors are scoped per-session, so there's some potential issues with keeping track of the correct context, but it could be worth exploring.

The stumbling block may be that the OnPrepareStatement method is called before you can set the context.