Oracle managed driver ODP.NET with NHibernate 4.0 FLOAT (126) to C# DECIMAL/Double

Question

I am trying to create a mapping using Fluent NHibernate with Oracle 11g. This is the mapping I am creating:-

public class Abc : ClassMap<Abc>
{
    public Abc() 
    {
        Table("Abc");
        DynamicUpdate();

        Id(x => x.Id, "IdColumn").GeneratedBy.GuidComb();
        Map(x => x.DecimalColumn, "DecimalColumn").Formula("TRUNC(DecimalColumn, 28)");
    }
}

Now when I get the data using criteria query:-

var criteria = Session.QueryOver<Abc>().Where(x => x.Id == Id);
criteria.Future();

It throws Nhibernate.Exceptions.GenericADOException with InnerException (InvalidCastException) for the columns where decimal points are more than 28 so it requires truncation.

but If I remove the column from mapping such as (Note column name string is missing for DecimalColumn), it works.

public class Abc : ClassMap<Abc>
    {
        public Abc() 
        {
            Table("Abc");
            DynamicUpdate();

            Id(x => x.Id, "IdColumn").GeneratedBy.GuidComb();
            Map(x => x.DecimalColumn).Formula("TRUNC(DecimalColumn, 28)");
        }
    }

The problem is I have tests using SQLite and SQLite doesn't like it when the column name is omitted.

So if I fix it by removing the column name then I can't run tests. Any ideas ?

score 3 · Accepted Answer · edited May 23 '17 at 11:59

So, what I have figured out is as follows:-

Database column is float(126) which has more precision than supported by .NET hence, in some cases exception is thrown when .NET cannot handle the data (overflow). (Oracle number to C# decimal)
When a formula is specified in fluent mapping, a column becomes read only. The reason data can be read in the second case specified above is that Formula was being used but it also means that the same mapping cannot be used to insert/Update DecimalColumn. (How to map an NHibernate entity property using both a formula and a column specification)

As mentioned above, the type is database (FLOAT 126) which can have more precision(number of digits) than .NET supports. The ideal solution would be change the database column type of FLOAT(53) or something smaller if the data is never going to need more than FLOAT(53) (Precision 15 digits approx.) but if that is not possible then following can be done.

So the problem of overflow can be resolved without making the field read only (using formula in NHibernate mappings). A custom user type can be used. In the user type's "NullSafeGet" method use DataReader.GetDouble to read the data from the data reader and it works.

I am sharing the code below (which can definitely be improved and shouldn't be used in production without understanding and improving it as I am not sure all the methods are properly implemented).

    namespace ABCD
    {
        using System;
        using System.Data;

        using NHibernate;
        using NHibernate.SqlTypes;
        using NHibernate.UserTypes;

        public class MyDecimalType : IUserType 
        {
            public bool IsMutable
            {
                get
                {
                    return false;
                }
            }

            public System.Type ReturnedType
            {
                get
                {
                    return typeof(decimal);
                }
            }

            public NHibernate.SqlTypes.SqlType[] SqlTypes
            {
                get
                {
                    return new[] { SqlTypeFactory.GetSqlType(DbType.Decimal) };
                }
            }

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

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

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

            bool IUserType.Equals(object x, object y)
            {
                if (object.ReferenceEquals(x, y))
                {
                    return true;
                }

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

                return x.Equals(y);
            }

            public int GetHashCode(object x)
            {
                return x.GetHashCode();
            }

            public object NullSafeGet(System.Data.IDataReader rs, string[] names, object owner)
            {
                var index = rs.GetOrdinal(names[0]);
                var obj = rs.GetDouble(index);
                return Convert.ToDecimal(obj);
            }

            public void NullSafeSet(System.Data.IDbCommand cmd, object value, int index)
            {
                if (value == null)
                {
                    ((IDataParameter)cmd.Parameters[index]).Value = DBNull.Value;
                }
                else
                {
                    ((IDataParameter)cmd.Parameters[index]).Value = value;
                }
            }

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

Then use the custom user type in the mapping:-

    public class Abc : ClassMap<Abc>
    {
        public Abc() 
        {
            Table("Abc");
            DynamicUpdate();

            Id(x => x.Id, "IdColumn").GeneratedBy.GuidComb();
            Map(x => x.DecimalColumn, "DecimalColumn").CustomType<MyDecimalType>();
        }
    }

Oracle managed driver ODP.NET with NHibernate 4.0 FLOAT (126) to C# DECIMAL/Double

1 Answers1