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

i trying create mapping using fluent nhibernate oracle 11g. mapping 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 data using criteria query:-

var criteria = session.queryover<abc>().where(x => x.id == id); criteria.future(); 

it throws nhibernate.exceptions.genericadoexception innerexception (invalidcastexception) columns decimal points more 28 requires truncation.

but if remove column mapping such (note column name string missing decimalcolumn), 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 have tests using sqlite , sqlite doesn't when column name omitted.

so if fix removing column name can't run tests. ideas ?

so, have figured out follows:-

1. database column float(126) has more precision supported .net hence, in cases exception thrown when .net cannot handle data (overflow). (oracle number c# decimal)
2. when formula specified in fluent mapping, column becomes read only. reason data can read in second case specified above formula being used means same mapping cannot used insert/update decimalcolumn. (how map nhibernate entity property using both formula , column specification)

as mentioned above, type database (float 126) can have more precision(number of digits) .net supports. ideal solution change database column type of float(53) or smaller if data never going need more float(53) (precision 15 digits approx.) if not possible following can done.

so problem of overflow can resolved without making field read (using formula in nhibernate mappings). custom user type can used. in user type's "nullsafeget" method use datareader.getdouble read data data reader , works.

i sharing code below (which can improved , shouldn't used in production without understanding , improving not sure methods implemented).

    namespace abcd     {         using system;         using system.data;          using nhibernate;         using nhibernate.sqltypes;         using nhibernate.usertypes;          public class mydecimaltype : iusertype          {             public bool ismutable             {                                 {                     return false;                 }             }              public system.type returnedtype             {                                 {                     return typeof(decimal);                 }             }              public nhibernate.sqltypes.sqltype[] sqltypes             {                                 {                     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 custom user type in 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>();         }     }