Oracle ManagedDataAccess GetDecimal specified cast is not valid

c# entity-framework entity-framework-6 oracle oracle-manageddataaccess

Question

I am using EF6 Oracle.ManagedDataAccess v18.3.0, database first concept (edmx). The problem is that Oracle table stores Number that has higher precision (38 digits), that the default Decimal C# data type to which the oracle Number is mapped. Decimal has precision max 28-29 digits. Because of that OracleManagedDataAccess provider throws exeption below:

at Oracle.ManagedDataAccess.Client.OracleDataReader.GetDecimal(Int32 i) at System.Data.Entity.Core.Objects.Internal.ShapedBufferedDataRecord.ReadDecimal(DbDataReader reader, Int32 ordinal) at System.Data.Entity.Core.Objects.Internal.ShapedBufferedDataRecord.Initialize(DbDataReader reader, DbSpatialDataReader spatialDataReader, Type[] columnTypes, Boolean[] nullableColumns) at System.Data.Entity.Core.Objects.Internal.ShapedBufferedDataRecord.Initialize(String providerManifestToken, DbProviderServices providerServices, DbDataReader reader, Type[] columnTypes, Boolean[] nullableColumns) at System.Data.Entity.Core.Objects.Internal.BufferedDataReader.Initialize(String providerManifestToken, DbProviderServices providerServices, Type[] columnTypes, Boolean[] nullableColumns) at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues) at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClassb.<GetResults>b__a() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClassb.<GetResults>b__9() at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.Core.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) at System.Data.Entity.Core.Objects.ObjectQuery1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0() at System.Lazy1.CreateValue() at System.Lazy1.LazyInitValue() at System.Lazy1.get_Value() at System.Data.Entity.Internal.LazyEnumerator1.MoveNext() at System.Linq.Enumerable.First[TSource](IEnumerable1 source) at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.b__0[TResult](IEnumerable1 sequence) at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable1 query, Expression queryRoot) at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression) at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression) at System.Linq.Queryable.First[TSource](IQueryable`1 source) at TransakcijskiCertifikatGOTS.Program.Main(String[] args) in C:\Users\sl0gas\Desktop\JiraRepo\Proizvodnja\TransportniCertifikatiGOTS\TransakcijskiCertifikatGOTS\TransakcijskiCertifikatGOTS\Program.cs:line 34

Is there any known workaround to solve this problem? So far I have tried this:

  1. Setting the Precision on specific Member that is causing the problem in edmx file to 28,
  2. Changing the App.config to below configuration and trying different options

      <edmMappings>
    <edmNumberMapping>
      <add NETType="bool" MinPrecision="1" MaxPrecision="1" DBType="Number" />
      <add NETType="int16" MinPrecision="2" MaxPrecision="5" DBType="Number" />
      <add NETType="int32" MinPrecision="6" MaxPrecision="10" DBType="Number" />
      <add NETType="int64" MinPrecision="11" MaxPrecision="19" DBType="Number" />
      <add NETType="Decimal" MinPrecision="20" MaxPrecision="28" DBType="Number" />
      <add NETType="String" MinPrecision="29" MaxPrecision="38" DBType="Number" />
    </edmNumberMapping>
    

  3. Changing the default cast from Decimal to string so I could then manually convert to Decimal or double, but the provider throws not valid cast exception.

I am using LINQ syntax to query the database!

1
0
2/5/2020 11:47:33 AM

Popular Answer

I had this issue with a function call as well as a table. For the table column I changed the .NET type to double and handled the conversion - so far I haven't had any precision issues.

My workaround for a function call is as follows:

var command = "some_oracle_function(:first_parameter)";

var xx = context.Database.SqlQuery<string>(command, firstParameter).SingleOrDefault();
var oracleDecimal = new OracleDecimal(xx);
return oracleDecimal.IsNull ? 0 : OracleDecimal.SetPrecision(oracleDecimal, 28).Value

I force the response as a string and generate a new OracleDecimal - this allows the large overflowing decimal number - then i cast it to a handleable size within the .NET framework and return the valid decimal value. This has worked without issue for me for some time.

0
2/5/2020 11:42:19 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow