SqlQuery cannot be used inside of HistoryContext within a DbCommandtreeInterceptor, when using -SourceMigration:0

[worthy7]

Description

Ef.Df causes scripted migrations on Oracle using -SourceMigration: 0' to crash because it cannot get the oracle version because it is given the dbo` schema name instead of whatever the user set it as (due to a ... bug(?) in EF)

Exception

PM> Update-Database -ConfigurationTypeName OracleEFAttributesTest.Migrations.OracleMigrations.Configuration -verbose -script -sourceMigration 0
Using StartUp project 'OracleEFAttributesTest'.
Using NuGet project 'OracleEFAttributesTest'.
Target database is: '' (DataSource: test, Provider: Oracle.ManagedDataAccess.Client, Origin: Configuration).
System.Data.Entity.Core.EntityCommandCompilationException: An error occurred while preparing the command definition. See the inner exception for details. ---> System.Data.Entity.Core.EntityCommandExecutionException: The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types.
   at System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.CreateColumnMapFromReaderAndType(DbDataReader storeDataReader, EdmType edmType, EntitySet entitySet, Dictionary`2 renameList)
   at System.Data.Entity.Core.Objects.ObjectContext.InternalTranslate[TElement](DbDataReader reader, String entitySetName, MergeOption mergeOption, Boolean streaming, EntitySet& entitySet, TypeUsage& edmType)
   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__DisplayClass69`1.<ExecuteStoreQueryReliably>b__68()
   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__DisplayClass69`1.<ExecuteStoreQueryReliably>b__67()
   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__DisplayClass14`1.<ExecuteSqlQuery>b__13()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
   at EntityFramework.DynamicFilters.DynamicFilterExtensions.<>c__DisplayClass65_0.<OracleVersion>b__0(String k)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
   at EntityFramework.DynamicFilters.DynamicFilterExtensions.OracleVersion(DbContext context)
   at EntityFramework.DynamicFilters.DynamicFilterQueryVisitorCSpace.DoesNotSupportElementMethod(DbContext context)
   at EntityFramework.DynamicFilters.DynamicFilterInterceptor.TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
   at System.Data.Entity.Infrastructure.Interception.DbCommandTreeDispatcher.<Created>b__0(IDbCommandTreeInterceptor i, DbCommandTreeInterceptionContext c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TInterceptionContext,TResult](TResult result, TInterceptionContext interceptionContext, Action`2 intercept)
   at System.Data.Entity.Infrastructure.Interception.DbCommandTreeDispatcher.Created(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory)
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory)
   at System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.CreateCommandDefinition(ObjectContext context, DbQueryCommandTree tree)
   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Boolean streaming, Span span, IEnumerable`1 compiledQueryParameters, AliasGenerator aliasGenerator)
   at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__0[TResult](IEnumerable`1 sequence)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 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 System.Data.Entity.Migrations.History.HistoryRepository.<CreateDiscoveryQueryTrees>d__2a.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at System.Data.Entity.Migrations.Infrastructure.MigratorScriptingDecorator.ScriptUpdate(String sourceMigration, String targetMigration)
   at System.Data.Entity.Migrations.Design.ToolingFacade.ScriptUpdateRunner.RunCore()
   at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run()
An error occurred while preparing the command definition. See the inner exception for details.

• EF version:6.2
• EF DynamicFilters version: 3
• Database Provider: Oracle

This is from migration 0 to 1 (initial to first). The DefaultSchema is set to dbo, which it should not be.

A run of the code from migrations null to 2, working normal run of the code has the correct oracle schema name applied: (null means get the current database version, which is 1 in this case)

[worthy7]

Test cases for migrations (basically Update-Database --script source/target):
ScriptUpdate(0, 2): DoesNotSupportElementMethod called? Yes DefaultSchema: dbo (wrong)

ScriptUpdate(1, 2): DoesNotSupportElementMethod called? No

ScriptUpdate(null, 2): DoesNotSupportElementMethod called? Yes DefaultSchema: TEST (correct)

[worthy7]

Reported to EF too - this is a problem with an initial HistoryContext being created with the dbo schema, which then triggers the DbCommandTreeInterceptors with a mismatched schemaname + context, which is why DF breaks - it checks the oracle connection string, sees it, then tries to use the dbo name to query the oracle version with.
dotnet/ef6#790

[worthy7]

@JonathanMagnan If you could suggest an ideal way to bypass this problem for now I can PR it

[JonathanMagnan]

Hello @worthy7 ,

I will make my developer look at it this week.

However, I'm not sure yet if there is something we can do or not on our side.

Best Regards,

Jonathan

---

Performance Libraries
context.BulkInsert(list, options => options.BatchSize = 1000);
Entity Framework Extensions • Entity Framework Classic • Bulk Operations • Dapper Plus

Runtime Evaluation
Eval.Execute("x + y", new {x = 1, y = 2}); // return 3
C# Eval Function • SQL Eval Function

[worthy7]

The only thing I can think of to generate a full migration sql script is to point it to a blank database temporarily and use the "null" for source - which should theoretically do the same as 0.
I appreciate you looking into it, thanks.

[JonathanMagnan]

Hello @worthy7 ,

My developer takes some time to look at it but he is not sure if that's a bug caused by EF DynamicFilters or an Oracle bug.

I mean looking at all your message, you seem to already have found the root of the problem and the project you reported here: dotnet/ef6#790, it looks more like a general issue in Oracle.

[worthy7]

@JonathanMagnan As I said it is specifically a bug caused be EF, nothing to do with Oracle.
It is because EF creates a bad HistoryContext which doesn't use the schema name, and when it does this and runs a query (which doesn't affect normal EF) it triggers EF to try and look for the Oracle version.

I think you can just add an if statement in DF to ignore when the passed in context is a HistoryContext

[worthy7]

 public class DbCommandTreeInterceptor : IDbCommandTreeInterceptor
    {
        public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
        {
            if (interceptionContext.DbContexts.First() is HistoryContext) return;
           
            ...

        }
    }

Can you add some code like this to your interceptors without disruption functionality? There is no way DynamicFilters would ever be needed on the history context right.

[JonathanMagnan]

Hello @worthy7 ,

We will look at it

In the worst-case scenario, we will add a global option to allow you to enable this code, so we will keep being backward compatible.

Best Regards,

Jon