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.ObjectQuery
1.<>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:
- Setting the Precision on specific Member that is causing the problem in edmx file to 28,
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>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!
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.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
