Unable to execute plain query with EF6 ExecuteStoreQuery
Posted: Fri 04 Oct 2013 15:09
Greetings,
We just upgraded from EF 4.1 to EF 6 and have made it through most of the hurdles in the upgrade. We are using dotConnect 7.9.333.0. One of the last blocking issues that we have not yet been able to resolve is some code which executes plain ol' queries to return results as well as obtaining sequence values.
Current code that was working with EF 4 goes something like the below.
DbContext helper class:
Implementing code snippets:
The RecordYearInfo class above is simply:
Here is the exception that occurs:
I believe the DbSpatialServices exception is a red herring because of the inner exception shown above:
I've been playing with this for awhile with no success. First, is this the correct way to execute and obtain results from a simple query and map them onto a simple model? I know there are more complicated way to do it, but this worked quite easily in the past and we were very happy with it. In particular, it makes getting sequence numbers easy.
The exception I see feels a little more like a bug in the underlying provider rather than a misuse, but I am also wrong quite often.
I can solve some of the plain text SQL queries by simply creating a database view and converting the code accordingly, but the sequence is a little more of a challenge. In either case, I don't want to convert working, tested code if there is a simple solution to once again be able to run text queries in EF6.
Thank you in advance for any insight!
We just upgraded from EF 4.1 to EF 6 and have made it through most of the hurdles in the upgrade. We are using dotConnect 7.9.333.0. One of the last blocking issues that we have not yet been able to resolve is some code which executes plain ol' queries to return results as well as obtaining sequence values.
Current code that was working with EF 4 goes something like the below.
DbContext helper class:
Code: Select all
private DbContext context;
public EntityFrameworkRepository(DbContext context)
{
Verify.IsNotNull(context);
this.context = context;
}
public ObjectContext ObjectContext
{
get { return ((IObjectContextAdapter)context).ObjectContext; }
}
public IList Query<TElement>(string sqlText)
{
var result = ObjectContext.ExecuteStoreQuery<TElement>(sqlText);
return result.ToList();
}
Code: Select all
var sequence = (long)Repository.Query<long>("SELECT " + sequenceName + ".NEXTVAL FROM DUAL")[0];
// Repository is the instance of the DbContext helper above
var results = Repository.Query<RecordYearInfo>(@"
SELECT
Record_Year AS Year,
count(1) AS NumberOfRecords
FROM ScanMigrationRow
WHERE MigrationDate IS NULL
GROUP BY Record_Year");
Code: Select all
public class RecordYearInfo
{
public int Year { get; set; }
public int NumberOfRecords { get; set; }
}
Code: Select all
An exception occurred: The provider did not return a 'DbSpatialServices' instance. In order to use the 'DbGeography' or 'DbGeometry' spatial types the EF provider being used must support spatial types and all prerequisites for the provider must be installed. See http://go.microsoft.com/fwlink/?LinkId=287183 for details.
System.Data.Entity.Core.ProviderIncompatibleException: The provider did not return a 'DbSpatialServices' instance. In order to use the 'DbGeography' or 'DbGeometry' spatial types the EF provider being used must support spatial types and all prerequisites for the provider must be installed. See http://go.microsoft.com/fwlink/?LinkId=287183 for details. ---> System.InvalidCastException: Unable to cast object of type 'Devart.Data.Oracle.OracleDataReader' to type 'Devart.Common.Entity.cb'.
at Devart.Common.Entity.EntitySpatialDataReader..ctor(DbDataReader A_0, EntitySpatialServices A_1, String A_2)
at Devart.Common.Entity.EntityDefaultValueSpatialServices.CreateSpatialDataReader(DbDataReader fromReader, String manifestToken)
at System.Data.Entity.Core.Common.DbProviderServices.GetSpatialDataReader(DbDataReader fromReader, String manifestToken)
--- End of inner exception stack trace ---
at System.Data.Entity.Core.Common.DbProviderServices.GetSpatialDataReader(DbDataReader fromReader, String manifestToken)
at System.Data.Entity.Core.Objects.Internal.BufferedDataReader.ReadMetadata(String providerManifestToken, DbProviderServices providerSerivces, DbDataReader reader)
at System.Data.Entity.Core.Objects.Internal.BufferedDataReader.Initialize(String providerManifestToken, DbProviderServices providerSerivces)
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__DisplayClass63`1.<ExecuteStoreQueryReliably>b__62()
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__DisplayClass63`1.<ExecuteStoreQueryReliably>b__61()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery[TElement](String commandText, Object[] parameters)
Code: Select all
System.InvalidCastException: Unable to cast object of type 'Devart.Data.Oracle.OracleDataReader' to type 'Devart.Common.Entity.cb'.
at Devart.Common.Entity.EntitySpatialDataReader..ctor(DbDataReader A_0, EntitySpatialServices A_1, String A_2)
at Devart.Common.Entity.EntityDefaultValueSpatialServices.CreateSpatialDataReader(DbDataReader fromReader, String manifestToken)
at System.Data.Entity.Core.Common.DbProviderServices.GetSpatialDataReader(DbDataReader fromReader, String manifestToken)
The exception I see feels a little more like a bug in the underlying provider rather than a misuse, but I am also wrong quite often.
I can solve some of the plain text SQL queries by simply creating a database view and converting the code accordingly, but the sequence is a little more of a challenge. In either case, I don't want to convert working, tested code if there is a simple solution to once again be able to run text queries in EF6.Thank you in advance for any insight!