NHibernate - Conversion failed when converting datetime from binary/varbinary string

Question

Note, this is some ancient NHibernate installation! I have the following NHibernate class:

[Serializable]
[Class(Table = "SomeEvents")]
public class SomeEvent
{
    [Property(Column="processDate")]
    public DateTime? ProcessDate { get; set; }
}

When trying to update some events like:

using (ISession session = FDK_Hibernate_Manager.OpenSession())
{
    IQuery query = session.CreateQuery(string.Format("FROM {0} e WHERE e.ContractId = :cid", typeof(ContractLeverconditiesEvent)));
    query.SetInt32("cid", contractId);

    foreach(var evnt in query.List().Cast<SomeEvent>())
    {
        evnt.ProcessDate = DateTime.Now;
        session.SaveOrUpdate(evnt);
    }

    session.Flush();
}

I receive the following exception:

Conversion failed when converting datetime from binary/varbinary string.

So I basically guess that NHibernate doesn't understand my DateTime? yet. My NHibernate install doesn't have any fancy Nullables.NHibernate.NullableDateTimeType. So anyone has a clue to solve this ancient NHibernate issue?

Answer 1

This article might help.

Answer 2

Unfortunately, you'll probably have to use a proxy value (magic number) for the null date. 1/1/1900 is a common choice. If you can map a private member (I don't use attributes), you could control the value through the public property:

public class SomeEvent
{
    private DateTime _processDate; // map this

    public SomeEvent()
    {
        _processDate = new DateTime(1900, 1, 1);
    }

    public DateTime? ProcessDate
    {
        get
        {
            if (_processDate == new DateTime(1900, 1, 1))
            {
                return null;
            }
            return _processDate;
        }
        set
        {
            _processDate = value ?? new DateTime(1900, 1, 1);
        }
    }
}

Answer 3

I ended up with this (although it can require some work in error-checking and such :-))

Implement it using [Property(Column = "processDate", TypeType = typeof(NullableDateTime))]

class NullableDateTime : IUserType
{
    #region IUserType Members

    public new bool Equals(object obj, object obj2)
    {
        return false;
    }

    public override int GetHashCode()
    {
        return base.GetHashCode();
    }

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

    public bool IsMutable
    {
        get { return true; }
    }

    public object NullSafeGet(System.Data.IDataReader rs, string[] names, object owner)
    {
        object o = rs[names[0]];
        if (o == DBNull.Value) return new Nullable<DateTime>();
        else
            return new Nullable<DateTime>(((System.Data.SqlTypes.SqlDateTime)o).Value);
    }

    public void NullSafeSet(System.Data.IDbCommand cmd, object value, int index)
    {
        System.Data.Common.DbParameter parameter = (System.Data.Common.DbParameter)cmd.Parameters[index];
        if (value == null)
        {
            parameter.Value = DBNull.Value;
            return;
        }
        else
        {
            parameter.Value = new System.Data.SqlTypes.SqlDateTime((DateTime)value);
        }
    }

    public Type ReturnedType
    {
        get { return this.GetType(); }
    }

    public NHibernate.SqlTypes.SqlType[] SqlTypes
    {
        get { return new NHibernate.SqlTypes.SqlType[] { new SqlDateTimeType() }; }
    }

    #endregion
}


public class SqlDateTimeType : NHibernate.SqlTypes.SqlType
{
    public SqlDateTimeType() : base(System.Data.DbType.DateTime)
    {
    }
}