Sometimes it generator wrong sql

3975927

I Use Oracle.EntityFrameworkCore(2.18.0-beta3) in .NET Core 2.2.

This is my dbcontext configuration.

builder.UseOracle(connectionString, opt => {

opt.UseOracleSQLCompatibility("11");

});

Sometimes the SQL generated with EF  will be wrong.

2019-04-16 10:51:05.178 +08:00 [Error] Failed executing DbCommand ("93"ms) [Parameters=[":clientId_0='?' (Size = 200)"], CommandType='Text', CommandTimeout='0']"
 ""SELECT 
\"x.AllowedGrantTypes\".\"Id\", 
\"x.AllowedGrantTypes\".\"ClientId\", 
\"x.AllowedGrantTypes\".\"GrantType\" 
FROM \"ClientGrantTypes\" \"x.AllowedGrantTypes\" 
 INNER JOIN (     
     Select K0 \"Id\" 
     from( SELECT \"x0\".\"Id\" K0 
          FROM \"Clients\" \"m1\"     
     WHERE (\"m1\".\"ClientId\" = :clientId_0)
ORDER BY \"m1\".\"Id\" NULLS FIRST) \"m1\"
where rownum <= 1 ) \"t\" ON (\"x.AllowedGrantTypes\".\"ClientId\" = \"t\".\"Id\") ORDER BY \"t\".\"Id\" NULLS FIRST" 
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "x0"."Id": 标识符无效   
 at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)    
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
 at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)    
 at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)   
 at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)

2019-04-16 10:51:05.319 +08:00 [Error] An unhandled exception has occurred while executing the request.Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "x0"."Id": 标识符无效   at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)   at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)   at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()   at Microsoft.EntityFrameworkCore.Query.Internal.QueryBuffer.IncludeCollection[TEntity,TRelated,TElement](Int32 includeId, INavigation navigation, INavigation inverseNavigation, IEntityType targetEntityType, IClrCollectionAccessor clrCollectionAccessor, IClrPropertySetter inverseClrPropertySetter, Boolean tracking, TEntity entity, Func`1 relatedEntitiesFactory, Func`3 joinPredicate)   at lambda_method(Closure , QueryContext , Client , Object[] )   at Microsoft.EntityFrameworkCore.Query.Internal.IncludeCompiler._Include[TEntity](QueryContext queryContext, TEntity entity, Object[] included, Action`3 fixup)   at lambda_method(Closure , Client )   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()   at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable`1 source, Boolean& found)   at lambda_method(Closure )   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ResultEnumerable`1.GetEnumerator()   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()   at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable`1 source, Boolean& found)   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass15_1`1.<CompileQueryCore>b__0(QueryContext qc)   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)   at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)   at IdentityServer4.EntityFramework.Stores.ClientStore.FindClientByIdAsync(String clientId)   at IdentityServer4.Stores.ValidatingClientStore`1.FindClientByIdAsync(String clientId)   at IdentityServer4.Stores.IClientStoreExtensions.FindEnabledClientByIdAsync(IClientStore store, String clientId)   at IdentityServer4.Validation.ClientSecretValidator.ValidateAsync(HttpContext context)   at IdentityServer4.Endpoints.TokenEndpoint.ProcessTokenRequestAsync(HttpContext context)   at IdentityServer4.Endpoints.TokenEndpoint.ProcessAsync(HttpContext context)   at IdentityServer4.Hosting.IdentityServerMiddleware.Invoke(HttpContext context, IEndpointRouter router, IUserSession session, IEventService events)   at IdentityServer4.Hosting.IdentityServerMiddleware.Invoke(HttpContext context, IEndpointRouter router, IUserSession session, IEventService events)   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)   at IdentityServer4.Hosting.BaseUrlMiddleware.Invoke(HttpContext context)   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

The linq is:
var client = _context.Clients                 <br/>                     .Include(x =&gt; x.AllowedGrantTypes)                <br/>                     .Include(x =&gt; x.RedirectUris)<br/>           .Include(x =&gt; x.PostLogoutRedirectUris)  <br/>           .Include(x =&gt; x.AllowedScopes)                <br/>                     .Include(x =&gt; x.ClientSecrets)   <br/>           .Include(x =&gt; x.Claims)  <br/>           .Include(x =&gt; x.IdentityProviderRestrictions)  <br/>           .Include(x =&gt; x.AllowedCorsOrigins)     <br/>           .Include(x =&gt; x.Properties)  <br/>           .AsNoTracking()        <br/>             .FirstOrDefault(x =&gt; x.ClientId == clientId);

I use identityserver 4 , the link : https://github.com/IdentityServer/IdentityServer4.EntityFramework.Storage/blob/dev/src/Stores/ClientStore.cs

Alex Keh-Oracle

Does the "xo"."Id" column actually exist in your schema? If not, what should the query actually be? I'm not familiar with the Identity Server project.

FYI: It seems another user has identified a similar issue to what you've found:

https://github.com/IdentityServer/IdentityServer4/issues/3190

3975927

"x0" is the alias which is not found. The realation sql is

SELECT \"x0\".\"Id\" K0     FROM \"Clients\" \"m1\"   WHERE ....

The table Clients renames “m1",but the select expression used "x0".

Alex Keh-Oracle

Can you provide the schema objects that need to be created? Ideally, we'd like a simple test case.

Alex Keh-Oracle

Also, I'm curious by what you mean by "sometimes it happens". Does this error happen every time you execute the same LINQ?

3975927

I think it happens when i exexute the same linq.The point is “the same linq”， but  it generate different sql: one is right,one is error.

I found it always happend at   first time   the  ef  used.

Here is the repo:https://github.com/362486122/test

Alex Keh-Oracle

I reviewed your repo code, but didn't see the model nor the LINQ you used.

When the LINQ executes the second time correctly, what is the actual SQL used?

3975927

I update the repo code.Now it has the LINQ.

The wrong SQL:

SELECT "x.AllowedGrantTypes"."Id",

    "x.AllowedGrantTypes"."ClientId",

    "x.AllowedGrantTypes"."GrantType"

    FROM "ClientGrantTypes" "x.AllowedGrantTypes"

   INNER JOIN (Select   K0 "Id" from(  

                    SELECT "x0"."Id" K0     FROM "Clients" "x0"     WHERE ("m1"."ClientId" = :clientId_0)     ORDER BY "m1"."Id" NULLS FIRST     ) "m1"     where rownum <= 1 ) "t" ON ("x.AllowedGrantTypes"."ClientId" = "t"."Id") ORDER BY "t"."Id" NULLS FIRST

Look at the red place,it's wrong.

LINQ is:

_context.Clients

                .Include(x => x.AllowedGrantTypes)

                .Include(x => x.RedirectUris)

                .Include(x => x.PostLogoutRedirectUris)

                .Include(x => x.AllowedScopes)

                .Include(x => x.ClientSecrets)

                .Include(x => x.Claims)

                .Include(x => x.IdentityProviderRestrictions)

                .Include(x => x.AllowedCorsOrigins)

                .Include(x => x.Properties)

                .AsNoTracking()

                .FirstOrDefault(x => x.ClientId == "IoTClient");

Alex Keh-Oracle

I also need your model.