NHibernate IUserType convert nullable DateTime to DB not-null value

Question

I have legacy DB that store dates that means no-date as 9999-21-31, The column Till_Date is of type DateTime not-null="true".

in the application i want to build persisted class that represent no-date as null, So i used nullable DateTime in C# //public DateTime? TillDate {get; set; }

I created IUserType that knows to convert the entity null value to DB 9999-12-31

but it seems that NHibernate doesn't call SafeNullGet, SafeNullSet on my IUserType when the entity value is null, and report a null is used for not-null column.

I tried to by-pass it by mapping the column as not-null="false" (changed only the mapping file, not the DB) but it still didn't help, only now it tries to insert the null value to the DB and get ADOException.

Any knowledge if NHibernate doesn't support IUseType that convert null to not-null values?

//Implementation before required fixes !!!

public class NullableDateTimeToNotNullUserType : IUserType
{
        private static readonly DateTime MaxDate = new DateTime(9999, 12, 31);

        public new bool Equals(object x, object y)
        {                                                   //This didn't work as well
            if (ReferenceEquals(x, y)) return true; //if(x == null && y == null) return false;

            if (x == null || y == null) return false;

            return x.Equals(y);
        }

        public int GetHashCode(object x)
        {
            return x == null ? 0 : x.GetHashCode();
        }

        public object NullSafeGet(IDataReader rs, string[] names, object owner)
        {
            var value = rs.GetDateTime(rs.GetOrdinal(names[0]));

            return (value == MaxDate)? null : value;
        }

        public void NullSafeSet(IDbCommand cmd, object value, int index)
        {
            var dateValue = (DateTime?)value;

            var dbValue = (dateValue.HasValue) ? dateValue.Value : MaxDate;

            ((IDataParameter)cmd.Parameters[index]).Value = dbValue;
        }

        public object DeepCopy(object value)
        {
            return value;
        }

        public object Replace(object original, object target, object owner)
        {
            return original;
        }

        public object Assemble(object cached, object owner)
        {
            return cached;
        }

        public object Disassemble(object value)
        {
            return value;
        }

        public SqlType[] SqlTypes
        {
            get { return new[] { NHibernateUtil.DateTime.SqlType }; }
        }

        public Type ReturnedType
        {
            get { return typeof(DateTime?); }
        }

        public bool IsMutable
        {
            get { return false; }
        }
    }
}

//Final Implementation with fixes.

//Make the column mapping in hbm.xml not-null="false" even if in DB null not allowed.
//Make sure the class mapping in xml doesn't have dynamic-insert="true"

public class NullableDateTimeToNotNullUserType : IUserType
{
        private static readonly DateTime MaxDate = new DateTime(9999, 12, 31);

        public new bool Equals(object x, object y)
        {                                                   //This didn't work as well
            if (ReferenceEquals(x, y)) return true; //if(x == null && y == null) return false;

            if (x == null || y == null) return false;

            return x.Equals(y);
        }

        public int GetHashCode(object x)
        {
            return x == null ? 0 : x.GetHashCode();
        }

        public object NullSafeGet(IDataReader rs, string[] names, object owner)
        {
            var value = NHibernateUtil.Date.NullSafeGet(rs, names[0]);

            return (value == MaxDate)? default(DateTime?) : value;
        }

        public void NullSafeSet(IDbCommand cmd, object value, int index)
        {
            var dateValue = (DateTime?)value;

            var dbValue = (dateValue.HasValue) ? dateValue.Value : MaxDate;

            NHibernateUtil.Date.NullSafeSet(cmd, valueToSet, index);
        }

        public object DeepCopy(object value)
        {
            return value;
        }

        public object Replace(object original, object target, object owner)
        {
            return original;
        }

        public object Assemble(object cached, object owner)
        {
            return cached;
        }

        public object Disassemble(object value)
        {
            return value;
        }

        public SqlType[] SqlTypes
        {
            get { return new[] { NHibernateUtil.DateTime.SqlType }; }
        }

        public Type ReturnedType
        {
            get { return typeof(DateTime?); }
        }

        public bool IsMutable
        {
            get { return false; }
        }
    }
}

Jamie Ide Jamie Ide · Accepted Answer · 2010-03-25T11:44:12

The problem is likely in your implementation of Equals. NHibernate uses Equals to determine if it needs to get or set the value. Please post your IUserType implementation.

Edit: OK, I think I see the problem. Try this:

public object NullSafeGet(IDataReader rs, string[] names, object owner)
{
    var value = NHibernateUtil.Date.NullSafeGet(rs, names[0]);
    return (value == MaxDate) ? null : value;
}

public void NullSafeSet(IDbCommand cmd, object value, int index)
{
    var dateValue = (DateTime?)value;
    var dbValue = (dateValue.HasValue) ? dateValue.Value : MaxDate;
    NHibernateUtil.Date.NullSafeSet(cmd, valueToSet, index);
}

NullSafeGet may also need to cast the return value as DateTime?. Your implementation of Equals looks right.

NHibernate IUserType convert nullable DateTime to DB not-null value

1 Answers