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

Question

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

worthy7 opened this issue 6 years ago · 17 comments

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)

Answer 1 · 2019-04-23T01:56:30.000Z

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)

Answer 2 · 2019-04-23T06:08:03.000Z

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

Answer 3 · 2019-04-23T06:41:39.000Z

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

Answer 4 · 2019-04-23T11:48:25.000Z

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

Answer 5 · 2019-04-23T12:56:46.000Z

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.

Answer 6 · 2019-04-27T11:52:20.000Z

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.

Answer 7 · 2019-04-27T11:56:06.000Z

@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

Answer 8 · 2021-09-14T18:41:16.000Z

 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.

Answer 9 · 2021-09-14T19:10:52.000Z

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

Answer 10 · 2021-09-14T22:00:25.000Z

After some more research, I genuninely think that the HistoryContext that is passed in for this use case (Update-Database from $InitialDatabase) is - for some reason - just unable to execute SQL when inside the CommandTreeInterceptor.

So doing ExecuteSql needs to be avoided when it is a HistoryContext and inside an Interceptor . I wrote a very basic test to prove this and it seems to be the case. The error has nothing to do with Oracle or the SchemaName.

Answer 11 · 2021-09-17T14:09:10.000Z

Hello @worthy7 ,

The v3.2.1 has been released.

We removed the HistoryContext as you recommended: b3eda0e#diff-869e28a3333ee87fcff15f7c5e4b43c96e2df8c612af52e05c387183f0f6fd74R21

Let me know if that's exactly what you were looking for.

Best Regards,

Jon

Answer 12 · 2021-09-22T04:31:43.000Z

I think this has fixed it, if new issues start popping up it might be because there is some use case where people are using filters on this HistoryContext and it's broken (since this fix disables it).

Answer 13 · 2022-01-25T01:08:01.000Z

I have found that it is causing issues with ABP (aspnetboilerplate/aspnetboilerplate#6368), and its dangerous to allow it to have bad side-affects like this without proper testing. So at least make this an option, or just revert it.

Since I am such a niche case (using Oracle 11g, old EF6 etc) I think you can just leave this as unsupported and close.
I leave this project soon anyway.

Answer 14 · 2022-01-25T01:20:07.000Z

Thank you for letting me know,

We will look at it probably tomorrow.

Best Regards,

Jon

Answer 15 · 2022-01-25T11:01:34.000Z

TLDR:

Remove this change filtering out the HistoryContext case.
Get the OracleVersion using this code instead: (please test/read it, I am way out of my depth here)

        private string GetOracleVersion(Database database)
        {
            var originalstate = database.Connection.State;
            if (originalstate == System.Data.ConnectionState.Closed)
                database.Connection.Open();

            var cmd = database.Connection.CreateCommand();
            cmd.CommandText = "select version from product_component_version where product like '%Database%'";

            List<List<object>> items = new List<List<object>>();
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                var item = new List<Object>();
                items.Add(item);

                for (int i = 0; i < reader.FieldCount; i++)
                    item.Add(reader[i]);
            }
           // this should ALWAYS be just one row, one field.
            var version = items.Select(d => d.Select(v => (string)v)).First().First();

            if (originalstate == System.Data.ConnectionState.Closed) database.Connection.Close();
            return version;
        }

Explaination:

In normal usage, SOME TIMES the interceptionContext.DbContexts; has 2 contexts and the first is a HistoryContext, so the current solution is actually going to be skipping datafilters sometimes when it shouldn't. HistoryContext is sometimes passed in, even when doing normal queries etc.
The situation where the bad HistoryContext is passed in, is hard to isolate.
There is some other strange error happening (on 3.2.0 too), I believe it's to do with when 2 queries are run at the same time, and SqlQuery does not like that, so throws a A second operation started on this context before a previous asynchronous operation completed. Use 'await' to ensure that any asynchronous operations have completed before calling another method on this context. Any instance members are not guaranteed to be thread safe.
I do not know the specifics of why this is happening, but I saw this warning from MS, perhaps it is related.
https://docs.microsoft.com/en-us/ef/core/logging-events-diagnostics/interceptors

The code I posted above in place of SqlQuery seems to solve both this problem, and the original issue with the duff HistoryContext.

I built my own interceptor, and ran the same statements, and then when it fails, I just ran the command a different way to try and analyze exactly WHY the data cannot be parsed into a string, and as far as I can tell, I just guess that SqlQuery is looking at the bad schema name or something, whereas CreateCommand doesn't?

Some proof:
Please look at this picture. It is a debug of the exact moment that the normal SqlQuery command has failed, and I have instead caught the exception, ran some other way of doing the query, and shown that it gets the version perfectly fine.

Answer 16 · 2022-01-26T14:12:05.000Z

Hello @worthy7 ,

The v3.2.4 has been released: https://github.com/zzzprojects/EntityFramework.DynamicFilters/releases/tag/3.2.4

Since it was too much dangerous (we don't know all side impacts), we choose to add an option so you can add your own code about if the DynamicFilterInterceptor should be ignored or not.

DynamicFilterManager.ShouldIgnoreDynamicFilterInterceptor = (interceptionContext) => {  /* your code that return a bool value */  };

Let me know if everything is perfect for you with this change.

You can now choose on your side whenever you want it to be ignored.

Best Regards,

Jon

Is this library useful to you? Please help us by becoming a sponsor to keep it alive and supported.

Answer 17 · 2022-01-27T01:13:47.000Z

Hello @worthy7 ,

The v3.2.4 has been released: https://github.com/zzzprojects/EntityFramework.DynamicFilters/releases/tag/3.2.4

Since it was too much dangerous (we don't know all side impacts), we choose to add an option so you can add your own code about if the DynamicFilterInterceptor should be ignored or not.
DynamicFilterManager.ShouldIgnoreDynamicFilterInterceptor = (interceptionContext) => {  /* your code that return a bool value */  };
Let me know if everything is perfect for you with this change.

You can now choose on your side whenever you want it to be ignored.

Best Regards,

Jon

Is this library useful to you? Please help us by becoming a sponsor to keep it alive and supported.

Thank you. This does make it easier, and I believe I have identified the source of the issue better.

It is simply that the connection is closed, and you are trying to run the SqlQuery (to get the oracle version) on a closed connection.

interceptionContext.DbContexts.First().Database.Connection.State == System.Data.ConnectionState.Closed