I'm trying to retrieve list of DbResult
from an oracle database table, with the code below by Execute Raw SQL Queries in Entity Framework 6.
var query = repository.DbContext.Database.SqlQuery<DbResult>("SELECT NAME as Name,SUB_PORTFOLIO_INSTANCE_ID as SubPortfolioInstanceID,STATUS as Status FROM DGARSMART.T_SC_SERVICE");
List<DbResult> serviceList = query.ToList();
This query throws an exception:
Specified method is not supported.
I think It's because of query trying to parse STATUS
column(type of number(1,0) in Oracle) to bool Status property belongs to DbResult
. How can I achieve parse that query to my DbResult
class?
By the way if I remove STATUS
statement from query it retrives data and parse to DbResult
object successfully.
class DbResult
{
public string Name { get; set; }
public DateTime SubPortfolioInstanceID { get; set; }
public bool Status { get; set; }
}
Stack Trace:
at Oracle.ManagedDataAccess.Client.OracleDataReader.GetBoolean(Int32 i)
at System.Data.Entity.Core.Objects.Internal.ShapedBufferedDataRecord.ReadBool(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.ObjectContext.ExecuteStoreQueryInternal[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass24`1.<ExecuteStoreQueryReliably>b__23()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass24`1.<ExecuteStoreQueryReliably>b__22()
at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliably[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery[TElement](String commandText, ExecutionOptions executionOptions, Object[] parameters)
at System.Data.Entity.Internal.InternalContext.<>c__DisplayClasse`1.<ExecuteSqlQuery>b__d()
at System.Lazy`1.CreateValue()
at System.Lazy`1.LazyInitValue()
at System.Lazy`1.get_Value()
at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
I'm using these packages:
<package id="EntityFramework" version="6.0.0" targetFramework="net45" />
<package id="Oracle.ManagedDataAccess" version="12.2.1100" targetFramework="net45" />
<package id="Oracle.ManagedDataAccess.EntityFramework" version="12.2.20190115" targetFramework="net45" />