Unable to execute plain query with EF6 ExecuteStoreQuery

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Hegg
Posts: 14
Joined: Thu 26 Apr 2012 21:14

Unable to execute plain query with EF6 ExecuteStoreQuery

Post by Hegg » 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:

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();
        }
Implementing code snippets:

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");
The RecordYearInfo class above is simply:

Code: Select all

    public class RecordYearInfo
    {
        public int Year { get; set; }
        public int NumberOfRecords { get; set; }
    }
Here is the exception that occurs:

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)
I believe the DbSpatialServices exception is a red herring because of the inner exception shown above:

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)
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!

Hegg
Posts: 14
Joined: Thu 26 Apr 2012 21:14

Re: Unable to execute plain query with EF6 ExecuteStoreQuery

Post by Hegg » Fri 04 Oct 2013 20:27

In doing some additional work, I've come across a similar issue with database initialization. This is the code:

Code: Select all

context.Database.Initialize(true);
And here is the exception:

Code: Select all

[NullReferenceException: Object reference not set to an instance of an object.]
   Devart.Common.Entity.er.b(ReferentialConstraint A_0) +92
   Devart.Common.Entity.er.c(ReferentialConstraint A_0) +93
   Devart.Common.Entity.er.a(ReferentialConstraint A_0, EntitySet A_1, EntitySet A_2) +162
   Devart.Data.Oracle.Entity.a0.a(ReferentialConstraint A_0, EntitySet A_1, EntitySet A_2) +89
   Devart.Common.Entity.er.a(AssociationSet A_0) +209
   Devart.Common.Entity.er.t() +1411
   Devart.Common.Entity.er.u() +66
   Devart.Common.Entity.er.am() +60
   Devart.Data.Oracle.Entity.OracleEntityProviderServices.DbCreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection) +208
   System.Data.Entity.Core.Common.DbProviderServices.CreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection) +150
   System.Data.Entity.Core.Objects.ObjectContext.CreateDatabase() +329
   System.Data.Entity.Internal.DatabaseOperations.Create(ObjectContext objectContext) +57
   System.Data.Entity.Internal.DatabaseCreator.CreateDatabase(InternalContext internalContext, Func`3 createMigrator, ObjectContext objectContext) +578
   System.Data.Entity.Internal.InternalContext.CreateDatabase(ObjectContext objectContext) +226
   System.Data.Entity.Database.Create(Boolean skipExistsCheck) +393
   System.Data.Entity.Database.Create() +41
   System.Data.Entity.DropCreateDatabaseAlways`1.InitializeDatabase(TContext context) +701
   System.Data.Entity.Internal.<>c__DisplayClasse`1.<CreateInitializationAction>b__d() +221
   System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) +115
   System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() +738
   System.Data.Entity.Database.Initialize(Boolean force) +107
Could these be related? Doesn't seem like it, but thought I'd paste it just in case.

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Unable to execute plain query with EF6 ExecuteStoreQuery

Post by MariiaI » Mon 07 Oct 2013 12:00

Thank you for the report. We have reproduced the issue with performing SQL queries via the ExecuteStoreQuery, SqlQuery methods. We will investigate it and inform you about the results as soon as possible.

As for the "Object reference not set to an instance of an object" exception, we couldn't reproduce it. Please provide us with a test project, so that we are able to reproduce and investigate this issue in more details.

Hegg
Posts: 14
Joined: Thu 26 Apr 2012 21:14

Re: Unable to execute plain query with EF6 ExecuteStoreQuery

Post by Hegg » Mon 07 Oct 2013 14:49

Thank you for the reply. The more I looked into the second post, the more I believe it is completely unrelated. Sorry for the misleading information. My suspicion is that it has to do with something specific in one of my model relationships.

Is there a way that you can provide me some information about what your code is doing on these lines so that I can have a hint as to what to look for?

Code: Select all

   Devart.Common.Entity.er.b(ReferentialConstraint A_0) +92
   Devart.Common.Entity.er.c(ReferentialConstraint A_0) +93
   Devart.Common.Entity.er.a(ReferentialConstraint A_0, EntitySet A_1, EntitySet A_2) +162
   Devart.Data.Oracle.Entity.a0.a(ReferentialConstraint A_0, EntitySet A_1, EntitySet A_2) +89
It's a project with 1,000,000+ lines of code so dissecting and sending a small test project is difficult. It's also difficult to delete relationships one at a time to troubleshoot since that requires extensive code changes just to get it to recompile. If there's any clue you can provide based on your own source then that would help tremendously.

Thank you again, and I look forward to a resolution on the primary issue for this thread.

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Unable to execute plain query with EF6 ExecuteStoreQuery

Post by MariiaI » Tue 08 Oct 2013 12:45

Unfortunately, this information about the error is not enough to reproduce this issue. It's not necessary to send us your entire project. Please send us the model you are working with (the entity classes and the context class).

Hegg
Posts: 14
Joined: Thu 26 Apr 2012 21:14

Re: Unable to execute plain query with EF6 ExecuteStoreQuery

Post by Hegg » Tue 15 Oct 2013 14:30

Good morning,

I am just following up on the progress with the ExecuteStoreQuery() issue you were able to replicate.

Thanks!

Jeff

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Unable to execute plain query with EF6 ExecuteStoreQuery

Post by MariiaI » Wed 16 Oct 2013 05:40

The bug with performing queries via the ExecuteStoreQuery, SqlQuery methods in Entity Framework 6 is fixed. The fix will be included in the next build of dotConnect for Oracle. We will inform you when it is available for download.

Hegg
Posts: 14
Joined: Thu 26 Apr 2012 21:14

Re: Unable to execute plain query with EF6 ExecuteStoreQuery

Post by Hegg » Wed 16 Oct 2013 14:07

Excellent, thank you for the great news! I look forward to the next release!

dumian
Posts: 13
Joined: Sat 09 Jun 2012 19:10
Contact:

Re: Unable to execute plain query with EF6 ExecuteStoreQuery

Post by dumian » Wed 16 Oct 2013 15:50

Hi,

I have the same problem, is somehow frustating that after update to EF 5 we get inot an EF bug that was fix in EF 6 and now we are facing the this one.
Is any workaround or the an known release date for next version to plan this show stopper in our product cycle ?

Thank you,
Cosmin

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Unable to execute plain query with EF6 ExecuteStoreQuery

Post by MariiaI » Thu 17 Oct 2013 08:49

We are going to release the new version of dotConnect for Oracle today or tomorrow.
We will post here when it is available for download.

dumian
Posts: 13
Joined: Sat 09 Jun 2012 19:10
Contact:

Re: Unable to execute plain query with EF6 ExecuteStoreQuery

Post by dumian » Thu 17 Oct 2013 10:22

Super, looking forward to it.
regards,
johannes

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Unable to execute plain query with EF6 ExecuteStoreQuery

Post by MariiaI » Fri 18 Oct 2013 05:15

New version of dotConnect for Oracle 8.0 is released!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=28131.

Post Reply