Sometimes it generator wrong sql — oracle-tech

    Forum Stats

  • 3,716,134 Users
  • 2,242,961 Discussions
  • 7,845,841 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sometimes it generator wrong sql

3975927
3975927 Member Posts: 11
edited April 2019 in ODP.NET

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

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited April 2019

    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
    3975927 Member Posts: 11
    edited April 2019

    "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
    Alex Keh-Oracle Posts: 2,753 Employee
    edited April 2019

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

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited April 2019

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

  • 3975927
    3975927 Member Posts: 11
    edited April 2019

    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
    Alex Keh-Oracle Posts: 2,753 Employee
    edited April 2019

    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
    3975927 Member Posts: 11
    edited April 2019

    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
    Alex Keh-Oracle Posts: 2,753 Employee
    edited April 2019

    I also need your model.

Sign In or Register to comment.