Entity Framework SQLQuery Oracle specified method is not supported

Question

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" />

stackoverflow.com/questions/19233237/… - There is an answer on here with a suggestion about modifying mapping in the config file — Dominic Cotton
– Dominic Cotton, Commented Sep 20, 2019 at 14:57

Dai · Accepted Answer · 2019-12-12 11:03:46Z

1

The quick-and-dirty solution is to use a hidden Int32 property and expose it as a Boolean value.

See mapping private property entity framework code first and Entity Framework Many to many through containing object

class DbResult
{
   public string Name { get; set; }

   public DateTime SubPortfolioInstanceID { get; set; }

   [Column("Status")]
   private Int32 HiddenStatus { get; set; }

   public Boolean Status => this.HiddenStatus != 0;

 }

answered Dec 12, 2019 at 11:03

Dai

158k31 gold badges314 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Entity Framework SQLQuery Oracle specified method is not supported

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related