Failed Castle ActiveRecord TransactionScope causes future queries to be invalid

I am trying to solve an issue when using a Castle ActiveRecord TransactionScope which is rolled back.

After the rollback, I am unable to query the Dog table. The "Dog.FindFirst()" line fails with "Could not perform SlicedFindAll for Dog", because it cannot insert dogMissingName.

using (new SessionScope())
{
    try
    {
        var trans = new TransactionScope(TransactionMode.New, OnDispose.Commit);

        try 
        {
             var dog = new Dog
             {
                 Name = "Snowy"
             };
             dog.Save();
             var dogMissingName = new Dog();
             dogMissingName.Save();
        }
        catch (Exception)
        {
           trans.VoteRollBack();
           throw;
        }
        finally
        {
           trans.Dispose();
        }   
     }
     catch (Exception ex)
     {
         var dogFromDatabase = Dog.FindFirst();
         Console.WriteLine("A dog: " + dogFromDatabase.Name);
     }
 }

Stacktrace is as follows:

Castle.ActiveRecord.Framework.ActiveRecordException: Could not perform SlicedFindAll for Dog ---> NHibernate.Exceptions.GenericADOException: could not insert: [Mvno.Dal.Dog#219e86fa-1081-490a-92d1-9d480171fcfd][SQL: INSERT INTO Dog (Name, Id) VALUES (?, ?)] ---> System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Name', table 'Dog'; column does not allow nulls. INSERT fails.
The statement has been terminated.
   ved System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   ved System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   ved System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   ved System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   ved System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   ved System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   ved System.Data.SqlClient.SqlCommand.RunExecuteR开发者_如何学运维eader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   ved System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   ved System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   ved NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd)
   ved NHibernate.AdoNet.NonBatchingBatcher.AddToBatch(IExpectation expectation)
   ved NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
   --- End of inner exception stack trace ---
   ved NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
   ved NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Object obj, ISessionImplementor session)
   ved NHibernate.Action.EntityInsertAction.Execute()
   ved NHibernate.Engine.ActionQueue.Execute(IExecutable executable)
   ved NHibernate.Engine.ActionQueue.ExecuteActions(IList list)
   ved NHibernate.Engine.ActionQueue.ExecuteActions()
   ved NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session)
   ved NHibernate.Event.Default.DefaultAutoFlushEventListener.OnAutoFlush(AutoFlushEvent event)
   ved NHibernate.Impl.SessionImpl.AutoFlushIfRequired(ISet`1 querySpaces)
   ved NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results)
   ved NHibernate.Impl.CriteriaImpl.List(IList results)
   ved NHibernate.Impl.CriteriaImpl.List()
   ved Castle.ActiveRecord.ActiveRecordBase.SlicedFindAll(Type targetType, Int32 firstResult, Int32 maxResults, Order[] orders, ICriterion[] criteria)
   --- End of inner exception stack trace ---
   ved Castle.ActiveRecord.ActiveRecordBase.SlicedFindAll(Type targetType, Int32 firstResult, Int32 maxResults, Order[] orders, ICriterion[] criteria)
   ved Castle.ActiveRecord.ActiveRecordBase.FindFirst(Type targetType, Order[] orders, ICriterion[] criteria)
   ved Castle.ActiveRecord.ActiveRecordBase.FindFirst(Type targetType, ICriterion[] criteria)
   ved Castle.ActiveRecord.ActiveRecordBase`1.FindFirst(ICriterion[] criteria)

If you look at the stack trace, you'll realize that the invalid dogMissingName record is still hanging around in the session's batched insertion buffer, even after the first attempt to perform the insertion failed. Calling Dog.FindFirst() later in the same session re-triggers an internal Flush() (which attempts once again the failed insert.)

From section 9.6 of the documentation:

From time to time the ISession will execute the SQL statements needed to synchronize the ADO.NET connection's state with the state of objects held in memory. This process, flush, occurs by default at the following points

• from some invocations of Find() or Enumerable()
• from NHibernate.ITransaction.Commit()
• from ISession.Flush()

Additionally, from section 9.7.2 of the documentation:

If you rollback the transaction you should immediately close and discard the current session to ensure that NHibernate's internal state is consistent.

Simply moving using (new SessionScope()) inside the outermost try/catch may be a viable workaround (the initial insertion will fail, raise an exception which will take you out of the SessionScope, likely triggering a second failure on the same insert, failure which you finally catch -- also see "data is not flushed on SessionScope.Flush()" in com.googlegroups.castle-project-users.).

Alternatively, if you don't want to close the session, you should be able to simply change the session default flush behaviour (see the FlushMode class) so that it never flushes unless Flush() is called explicitly (e.g. before committing.) Note that managing flushing in this way will get complex and error-prone very quickly, though.

The key is right on Vlad's answer:

If you rollback the transaction you should immediately close and discard the current session to ensure that NHibernate's internal state is consistent.

After you understand and apply that, your code should then look like this:

try
{
    using (new SessionScope())
    using (var trans = new TransactionScope(TransactionMode.New, OnDispose.Commit))
    {
        try 
        {
            var dog = new Dog { Name = "Snowy" };
            dog.Save();
            var dogMissingName = new Dog();
            dogMissingName.Save();
        }
        catch (Exception)
        {
            trans.VoteRollBack();
            throw;
        }
    }
}
catch (Exception ex)
{
    using (new SessionScope())
    {
        var dogFromDatabase = Dog.FindFirst();
        Console.WriteLine("A dog: " + dogFromDatabase.Name);
    }
}