problem with nullable versioned column

[Luis Silva]

I'm having a big problem, I'm creating a database integration and I'm using nhibernate because I already have a great knowledge, but the legacy system that does the persistence uses the ORM XPO that uses a column for competition, so far so good but is nullable and the default value in insert is NULL and in the next updates it will increment, start at NULL then go to 0,1,2 and so on. However I am using nhibernate when it will autalize a null value from the null value error, because it cannot increment a null value, so far so good, I implemented a class that inherits from IUserVersionType and overwrote the NEXT method, everything worked correctly, however in query generation it still continues with error because in query generation by own nhibernate.

public class MyVersion : NHibernate.UserTypes.IUserVersionType
{
   public object Next(object current, ISessionImplementor session)
   {
      if (current==null)
      {
         return 0;
      }
      return ((int)current) + 1;
    }
}

The code that nhibernate generates is the sql shown, as you can see the parameter p0 was solved with the class and the method NEXT, but in parameter p6 it makes the comparison with null what is wrong, was to do [is null] because value cannot be compared to null

UPDATE
        table 
    SET
        OptimisticLockField = @p0,
        a = @p1,
        b = @p2,
        c = @p3,
        d = @p4 
    WHERE
        e = @p5 
        AND OptimisticLockField = @p6;
    @p0 = 0 [Type: Int32 (0:0:0)], @p1 = '851' [Type: AnsiString (8000:0:0)], @p2 = 2015-09-23T00:00:00.0000000 [Type: DateTime (0:0:0)], @p3 = '12' [Type: AnsiString (8000:0:0)], @p4 = 2015-09-23T12:53:24.0000000 [Type: DateTime (0:0:0)], @p5 = 108 [Type: Int64 (0:0:0)], @p6 = NULL [Type: Int32 (0:0:0)]

any suggestions on how to proceed because I did not want to use another orm.

I am using FluentNHibernate 2.1.2 and NHibernate 5.2.5

public class DbDtoBase<T>
{
    public virtual int? OptimisticLockField { get; set; }
}

 public class table: DbDtoBase<Table>
 {
     public virtual long e { get; set; }
     public virtual string a { get; set; }
     public virtual DateTime  b { get; set; }
     public virtual string c { get; set; }
     public virtual DateTime d { get; set; }
 }

[Uwe Laas]

Hi,

you could use an Interceptor like this (but it is a hack!):

public override SqlString OnPrepareStatement(SqlString sql)
{
    var isRelevant = sql.IndexOfCaseInsensitive(" AND OptimisticLockField = ") > -1;
    if (isRelevant)
    {
        var builder = new SqlStringBuilder(sql);
        builder.RemoveAt(sql.Count - 1);
        builder.RemoveAt(sql.Count - 2);

        builder.Add(" AND (OptimisticLockField is null or OptimisticLockField = ");
        builder.AddParameter();
        builder.Add(") ");

        var result = builder.ToSqlString();
        return result;
    }

    return sql;
}
Code hier eingeben...