How to exchange unique, ordering column records with NHibernate and Envers?

I have been working with NHibernate for a while and finally got myself into a problem, I can neither find an answer nor anyone else how this problem seemed to be. Environment - NHibernate 3.3.1.4000 and NHibernate.Envers 1.4 for history keeping.

I have a database table / entity class that looks like this:

public class ProcedureStep
{
    public virtual int Id { get; protected internal set; }
    public virtual Procedure ParentObject { get; set; }
    public virtual int StepNo { get; set; }
    public virtual string Name { get; set; }
}

      

        
        
        
      

    

with display:

public class ProcedureStepMap : ClassMap<ProcedureStep>
{
    public ProcedureStepMap()
    {
        Table("ProcedureStep");
        Id(x => x.Id);

        References(x => x.ParentObject).Not.Nullable().UniqueKey("UK_PO_SN");
        Map(x => x.StepNo).Not.Nullable().UniqueKey("UK_PO_SN");
        Map(x => x.Name).Not.Nullable();
    }
}

      

        
        
        
      

    

We have a parent object with a list of procedure steps. The user can now add, remove or modify various stages. One "modification" is to change the order of procedure steps by using a button to move the selected item up or down in the list of steps.

Take a concrete example:

The script is a list of 4 steps in procedure 1-4:

(1) Aaaa
(2) Bbbb <--
(3) Cccc
(4) Dddd

      

        
        
        
      

    

Users select the second item "Bbbb" and press the Up button to get:

(1) Bbbb <--
(2) Aaaa
(3) Cccc
(4) Dddd

      

        
        
        
      

    

In the code I was now thinking about doing something like

Begin Transaction
1) Set "Aaaa" to StepNo = 0
2) Set "Bbbb" to StepNo = 1
3) Set "Aaaa" to StepNo = 2
End Transaction

      

        
        
        
      

    

But now NHibernates are coming into play. NHibernate is unaware of step 1 when committing a transaction and therefore tries to work with two update commands in the database (steps 2 and 3) that do not work because of the unique column symbol StepNo. Adding session.flush () after step 1 does not change any of this behavior (quite understandable to me since we are inside a transaction).

The next idea was to just use session.CreateQuery("update...").ExecuteUpdate()

to make changes to the database, which works fine but works around Envers and leaves me with no history record for this change (quite understandable again since sql just went through).

But what would be the correct way to make this change, having everything in one transaction and having Envers history? Maybe the unique "StepNo" column isn't the only way to make it work? Losing the unique character of the column would not be desirable as it is important to have an ordering order and have a database that provides this would be a big help.

Thanks for your ideas!

[Decision]:

jbl and cremor only seem to have answers. Along with some other issues, we have now decided to skip Envers entirely and use a more specialized way of doing history, less general, more tailored to our needs. Without Envers, the problem can be solved with a simple 3-step upgrade using sql commands.