C# – Oracle Entity Framework ‘Specified cast is not valid’ GetDecimal

All, I am writing an application that is connecting to an Oracle server via the .NET Entity Framework in C#. I am able to insert data just fine.
Also, I am able to query most data just fine. The exception I am seeing comes from a NUMBER or FLOAT in Oracle being converted to the .NET type decimal. But it doesn't always happen.

If I have this number in Oracle "0.96511627906976744186046511627906976744" then I get the exception. Not during the LINQ query, but when I execute a foreach with the queried data. But, If I have 0.9651162790 in the cell the query works fine.

Is Oracle's Entity Framework not supposed to reduce the precision to that of a decimal type?

Stripping precision off the hundreds of millions of entries in our database obviously is not an option.

Here is some code:

 using (Entities context = new Entities())
 {
     var data = from row in context.YieldsTestWeeklies
                select new
                {
                    D2Yield = row.D2Yield
                };

     foreach (var row in data)
     {
         textBox1.AppendText(row.D2Yield.ToString() + Environment.NewLine);
     }
 }

The Model:

 public partial class YieldsTestWeekly
 {
     public Nullable<decimal> D2Yield { get; set; }
 }

Exception Details:

Message: Specified cast is not valid.
Source: Oracle.ManagedDataAccess
InnerException: null
TargetSite: {System.Decimal GetDecimal(Int32)}

StackTrace:

at Oracle.ManagedDataAccess.Client.OracleDataReader.GetDecimal(Int32 i)
at Oracle.ManagedDataAccess.Client.OracleDataReader.GetValue(Int32 i)
at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader 1.GetUntypedValueDefault(DbDataReader reader, Int32 ordinal)
at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader 1.GetValue(DbDataReader reader, Int32 ordinal)
at lambda_method(Closure , Shaper )
at System.Data.Entity.Core.Common.Internal.Materialization.Coordinator 1.ReadNextElement(Shaper shaper)
at System.Data.Entity.Core.Common.Internal.Materialization.Shaper 1.SimpleEnumerator.MoveNext()
at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
at Oracle_Example.Form1.button1_Click(Object sender, EventArgs e) in C:\Users\REMOVED\Desktop\Oracle Example\Oracle Example\Form1.cs:line 33
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at Oracle_Example.Program.Main() in C:\Users\REMOVED\Desktop\Oracle Example\Oracle Example\Program.cs:line 19
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

Update

I tried both of these. Neither work. And I tried them with really low numbers also.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Remove<DecimalPropertyConvention>();
    modelBuilder.Conventions.Add(new DecimalPropertyConvention(38, 18));
    base.OnModelCreating(modelBuilder);
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<YieldsTestWeekly>().Property(e => e.D2Yield).HasPrecision(38, 18);
    base.OnModelCreating(modelBuilder);
}

Update (new question)
Does anyone know if it is possible to intercept these objects and truncate them before they are placed into the model? I feel like I should be able to override something to make this happen, but don't even know where to start looking.

Best Answer

You would receive the same error using ADO .Net because the problem is in the ADO .Net provider (EF, during "materialization process" find that the target property is a decimal so it calls GetDecimal of the Oracle data reader).

To understand while it happens only sometimes, you could have a look to the GetDecimal implementation. Probably Oracle implicitly convert a number only if the conversion can happen without precision loosing; otherwise it does not convert the number and raise the error. Other providers simply does not convert not compatible types (so you get the error always not only on certain records).

The best solution is probably to map the field to a double and use it.
You can also convert the double field to a decimal (if you want, you can do it in the same class with a non mapped field). With this solution you won't be able to use the converted field in queries.

EDIT

I read the ADO.Net provider code and is quite strange.
First of all, EF calls GetValue and is the Oracle ADO provider that calls GetDecimal. In the Oracle provider, Get_everythingwithapoint calls an inner GetDecimal function. And I think that the problem is there so my solution won't work. I think that the only way is that Oracle fixes the ADO.Net provider.

Just in case I paste here some code

from DataReader

override public decimal GetDecimal(int i) {
    AssertReaderIsOpen();
    AssertReaderHasData(); 
    return _columnInfo[i].GetDecimal(_buffer);
}

from OracleColumn (the type of _columnInfo)

    internal decimal GetDecimal(NativeBuffer_RowBuffer buffer) {
        if (typeof(decimal) != _metaType.BaseType) { 
            throw ADP.InvalidCast();
        }
        if (IsDBNull(buffer)) {
            throw ADP.DataReaderNoData(); 
        }
        Debug.Assert(null == _longBuffer, "dangling long buffer?"); 

        decimal result = OracleNumber.MarshalToDecimal(buffer, _valueOffset, _connection);
        return result; 
    }

    internal double GetDouble(NativeBuffer_RowBuffer buffer) {
        if (typeof(decimal) != _metaType.BaseType) { 
            throw ADP.InvalidCast();
        } 
        if (IsDBNull(buffer)) { 
            throw ADP.DataReaderNoData();
        } 
        Debug.Assert(null == _longBuffer, "dangling long buffer?");

        decimal decimalValue = OracleNumber.MarshalToDecimal(buffer, _valueOffset, _connection);
        double result = (double)decimalValue; 
        return result;
    }

See that GetDouble is the same as GetDecimal

You can also have a look to OracleNumber.MarshalToDecimal but I think that you'll conclude that you won't see it work also if you use ADO.Net.

Best Answer

Related Solutions

Related Topic