OData URL creates invalid SQL Query 42P01: missing FROM-clause entry for table "t"

[bmcdavidepi]

I’m having issues with a code base we recently upgraded to .NET 5 from .NET Core 2.2. We use OData as a backend to a React frontend which uses OData calls to load data. Any help is greatly appreciated.

Below is a sample OData call that is creating an invalid Postgres SQL statement:

/odata/properties?%24orderby=propertygroup/name&%24top=25&%24expand=propertyGroup(%24select%3Did%2Cname)%2CpropertyMappings%2CpropertyValues($count=true)&%24count=true&%24filter=contains(name%2C%20%27Suitable_For_Use_With%27)

Invalid SQL

      SELECT t1.id, t1.booleanlabel, t1.controltype, t1.createdby, t1.createdon, t1.defaultvalue, t1.description, t1.displayname, t1.displaysequence, t1.isexternalmanaged, t1.isgloballyrequired, t1.isincludetemplatespecificvalues, t1.issystemproperty, t1.modifiedby, t1.modifiedon, t1.name, t1.numbermaxvalue, t1.numberminvalue, t1.numberofdecimals, t1.propertygroupid, t1.showbothnames, t1.stringallowadhocvalues, t1.stringmaxlength, t1.stringminlength, t1.stringregex, t1.stringvaluebag, t1.tenantid, t1.type, t1.id0, t1.name0, t1.c, t1.c0, t3."ModelID", t3.id, t3.attributeincludeonproduct, t3.attributeiscomparable, t3.attributeisfilter, t3.attributeissearchable, t3.createdby, t3.createdon, t3.iscspecialfieldtype, t3.mappedtoiscfield, t3.modifiedby, t3.modifiedon, t3.propertyid, t3.specificationdescription, t3.specificationname, t3.specificationsortorder, t3.tenantid, t3."UseInstanceForProperties", t5."ModelID", t5.id, t5.createdby, t5.createdon, t5.displaysequence, t5.modifiedby, t5.modifiedon, t5.propertyid, t5.tenantid, t5.value, t5."UseInstanceForProperties"
      FROM (
          SELECT t0.id, t0.booleanlabel, t0.controltype, t0.createdby, t0.createdon, t0.defaultvalue, t0.description, t0.displayname, t0.displaysequence, t0.isexternalmanaged, t0.isgloballyrequired, t0.isincludetemplatespecificvalues, t0.issystemproperty, t0.modifiedby, t0.modifiedon, t0.name, t0.numbermaxvalue, t0.numberminvalue, t0.numberofdecimals, t0.propertygroupid, t0.showbothnames, t0.stringallowadhocvalues, t0.stringmaxlength, t0.stringminlength, t0.stringregex, t0.stringvaluebag, t0.tenantid, t0.type, t0.id0, t0.name0, (
              SELECT COUNT(*)
              FROM propertyvalue AS p
              WHERE (p.tenantid = @__ef_filter__p_3) AND (t0.id = p.propertyid)) AS c, FALSE AS c0
          FROM (
              SELECT p0.id, p0.booleanlabel, p0.controltype, p0.createdby, p0.createdon, p0.defaultvalue, p0.description, p0.displayname, p0.displaysequence, p0.isexternalmanaged, p0.isgloballyrequired, p0.isincludetemplatespecificvalues, p0.issystemproperty, p0.modifiedby, p0.modifiedon, p0.name, p0.numbermaxvalue, p0.numberminvalue, p0.numberofdecimals, p0.propertygroupid, p0.showbothnames, p0.stringallowadhocvalues, p0.stringmaxlength, p0.stringminlength, p0.stringregex, p0.stringvaluebag, p0.tenantid, p0.type, t.id AS id0, t.name AS name0
              FROM property AS p0
              INNER JOIN (
                  SELECT p1.id, p1.name
                  FROM propertygroup AS p1
                  WHERE p1.tenantid = @__ef_filter__p_1
              ) AS t ON p0.propertygroupid = t.id
              WHERE (p0.tenantid = @__ef_filter__p_0) AND ((@__TypedProperty_0 = '') OR (strpos(p0.name, @__TypedProperty_0) > 0))
              ORDER BY t.name, p0.id
              LIMIT @__TypedProperty_8
          ) AS t0
          ORDER BY t0.name0, t0.id
          LIMIT @__TypedProperty_9
      ) AS t1
      LEFT JOIN (
          SELECT t2."ModelID", t2.id, t2.attributeincludeonproduct, t2.attributeiscomparable, t2.attributeisfilter, t2.attributeissearchable, t2.createdby, t2.createdon, t2.iscspecialfieldtype, t2.mappedtoiscfield, t2.modifiedby, t2.modifiedon, t2.propertyid, t2.specificationdescription, t2.specificationname, t2.specificationsortorder, t2.tenantid, t2."UseInstanceForProperties"
          FROM (
              SELECT @__TypedProperty_4 AS "ModelID", p2.id, p2.attributeincludeonproduct, p2.attributeiscomparable, p2.attributeisfilter, p2.attributeissearchable, p2.createdby, p2.createdon, p2.iscspecialfieldtype, p2.mappedtoiscfield, p2.modifiedby, p2.modifiedon, p2.propertyid, p2.specificationdescription, p2.specificationname, p2.specificationsortorder, p2.tenantid, TRUE AS "UseInstanceForProperties", ROW_NUMBER() OVER(PARTITION BY p2.propertyid ORDER BY p2.id) AS row
              FROM propertymapping AS p2
              WHERE p2.tenantid = @__ef_filter__p_2
          ) AS t2
          WHERE t2.row <= @__TypedProperty_3
      ) AS t3 ON t1.id = t3.propertyid
      LEFT JOIN LATERAL (
          SELECT @__TypedProperty_7 AS "ModelID", t.id, t.createdby, t.createdon, t.displaysequence, t.modifiedby, t.modifiedon, t.propertyid, t.tenantid, t.value, TRUE AS "UseInstanceForProperties"
          FROM (
              SELECT p3.id, p3.createdby, p3.createdon, p3.displaysequence, p3.modifiedby, p3.modifiedon, p3.propertyid, p3.tenantid, p3.value
              FROM propertyvalue AS p3
              WHERE (p3.tenantid = @__ef_filter__p_3) AND (t1.id = p3.propertyid)
              ORDER BY p3.id
              LIMIT @__TypedProperty_5
          ) AS t4
          ORDER BY t.id
          LIMIT @__TypedProperty_6
      ) AS t5 ON TRUE
      ORDER BY t1.name0, t1.id, t1.id0, t3.propertyid, t3.id, t5.id

Stack trace

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
---> Npgsql.PostgresException (0x80004005): 42P01: missing FROM-clause entry for table "t"
   at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
  at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at Microsoft.AspNet.OData.Query.TruncatedCollection`1..ctor(IQueryable`1 source, Int32 pageSize, Boolean parameterize)
   at Microsoft.AspNet.OData.Query.ODataQueryOptions.LimitResults[T](IQueryable`1 queryable, Int32 limit, Boolean parameterize, Boolean& resultsLimited)
  Exception data:
    Severity: ERROR
    SqlState: 42P01
    MessageText: missing FROM-clause entry for table "t"
    Position: 3750
    File: parse_relation.c
    Line: 3239
    Routine: errorMissingRTE
   --- End of inner exception stack trace ---
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
   at Microsoft.AspNet.OData.Query.ODataQueryOptions.LimitResults(IQueryable queryable, Int32 limit, Boolean parameterize, Boolean& resultsLimited)
   at Microsoft.AspNet.OData.Query.ODataQueryOptions.ApplyPaging(IQueryable result, ODataQuerySettings querySettings)
   at Microsoft.AspNet.OData.Query.ODataQueryOptions.ApplyTo(IQueryable query, ODataQuerySettings querySettings)
   at Microsoft.AspNet.OData.EnableQueryAttribute.ApplyQuery(IQueryable queryable, ODataQueryOptions queryOptions)
   at Microsoft.AspNet.OData.EnableQueryAttribute.ExecuteQuery(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, Func`2 modelFunction, IWebApiRequestMessage request, Func`2 createQueryOptionFunction)
   at Microsoft.AspNet.OData.EnableQueryAttribute.OnActionExecuted(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, IWebApiRequestMessage request, Func`2 modelFunction, Func`2 createQueryOptionFunction, Action`1 createResponseAction, Action`3 createErrorAction)
   at Microsoft.AspNet.OData.EnableQueryAttribute.OnActionExecuted(ActionExecutedContext actionExecutedContext)
   at Microsoft.AspNetCore.Mvc.Filters.ActionFilterAttribute.OnActionExecutionAsync(ActionExecutingContext context, ActionExecutionDelegate next)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Insite.Pim.OData.Middleware.UseTenantInitialization.Invoke(HttpContext httpContext, IUnitOfWork unitOfWork, IApplicationUserProvider userProvider) in OData\Middleware\UseTenantInitialization.cs:line 40
   at Insite.Pim.OData.Middleware.UsePimAuthenticationMiddleware.Invoke(HttpContext httpContext, IApplicationUserProvider applicationUserProvider) in OData\Middleware\UsePimAuthentication.cs:line 83
  at Insite.Pim.OData.AppStart.ConfigureAuthentication.<>c__DisplayClass3_0.<<UsePimAuthentication>b__0>d.MoveNext() in OData\AppStart\ConfigureAuthentication.cs:line 100
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Insite.Pim.OData.Middleware.UseEndpointDebugging.Invoke(HttpContext httpContext) in OData\Middleware\UseEndpointDebugging.cs:line 52
   at Microsoft.AspNetCore.Builder.Extensions.MapWhenMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNet.OData.Batch.ODataBatchMiddleware.Invoke(HttpContext context)
   at Insite.Pim.OData.Middleware.UseForwardedForRequestHandler.Invoke(HttpContext httpContext) in OData\Middleware\UseForwardedForRequestHandler.cs:line 50
   at Serilog.AspNetCore.RequestLoggingMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

Include provider and version information

EF Core version: 5.0.5
Database provider: (Npgsql.EntityFrameworkCore.PostgreSQL Version="5.0.5.1")
OData: 7.5.8
Target framework: (NET 5.0)
Operating system: Windows 10
IDE: ( Visual Studio 2019 16.9.4)

[AndriySvyryd]

We think this might already be fixed. Can you try using 6.0.0-preview4 or a daily build?

[bmcdavidepi]

Hi @AndriySvyryd ,

I updated the followings packages to

<PackageReference Include="Microsoft.AspNetCore.DataProtection.EntityFrameworkCore" Version="6.0.0-preview.5.21264.5" />    
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.0-preview.5.21268.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.0-preview.5.21268.1">
    <PrivateAssets>all</PrivateAssets>
    <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
<PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="6.0.0-preview.5.21268.1" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="6.0.0-preview3" />

from

<PackageReference Include="Microsoft.AspNetCore.DataProtection.EntityFrameworkCore" Version="5.0.5" />    
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="5.0.5" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.5">
    <PrivateAssets>all</PrivateAssets>
    <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
<PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="5.0.5" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="5.0.5.1" />

After the package upgrade I had to change using statements in all EF designer files to

using Microsoft.EntityFrameworkCore.Metadata;

from

using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata;

And get the following when trying to start the application:

2021-05-19T20:07:15.889Z [FTL] [Microsoft.AspNetCore.Hosting.WebHost][1] Application startup exception
System.ArgumentNullException: Value cannot be null. (Parameter 'type')
   at System.Reflection.IntrospectionExtensions.GetTypeInfo(Type type)
   at Microsoft.AspNet.OData.DefaultContainerBuilder.BuildContainer()
   at Microsoft.AspNet.OData.PerRouteContainerBase.CreateODataRootContainer(Action`1 configureAction)
   at Microsoft.AspNet.OData.PerRouteContainerBase.CreateODataRootContainer(String routeName, Action`1 configureAction)
   at Microsoft.AspNet.OData.Extensions.ODataEndpointRouteBuilderExtensions.MapODataRoute(IEndpointRouteBuilder builder, String routeName, String routePrefix, Action`1 configureAction)
   at Microsoft.AspNet.OData.Extensions.ODataEndpointRouteBuilderExtensions.MapODataRoute(IEndpointRouteBuilder builder, String routeName, String routePrefix, IEdmModel model, ODataBatchHandler batchHandler)
   at Insite.Pim.OData.AppStart.ConfigureEndpointsAndMvc.<>c__DisplayClass2_0.<UseEndpoints>b__0(IEndpointRouteBuilder endpoints) in C:\_dev\repos\pim\Insite.Pim.OData\AppStart\ConfigureEndpointsAndMvc.cs:line 57
   at Microsoft.AspNetCore.Builder.EndpointRoutingApplicationBuilderExtensions.UseEndpoints(IApplicationBuilder builder, Action`1 configure)
   at Insite.Pim.OData.AppStart.ConfigureEndpointsAndMvc.UseEndpoints(IApplicationBuilder app, String pimHubPath, EnvironmentVariableProvider environmentVariableProvider) in C:\_dev\repos\pim\Insite.Pim.OData\AppStart\ConfigureEndpointsAndMvc.cs:line 50
   at Insite.Pim.OData.Startup.Configure(IApplicationBuilder app, IHostApplicationLifetime applicationLifetime) in C:\_dev\repos\pim\Insite.Pim.OData\Startup.cs:line 105
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder)
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.<Build>b__0(IApplicationBuilder builder)
   at Microsoft.AspNetCore.Hosting.ConventionBasedStartup.Configure(IApplicationBuilder app)
   at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<>c__DisplayClass0_0.<Configure>g__MiddlewareFilterBuilder|0(IApplicationBuilder builder)
   at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder app)
   at Microsoft.AspNetCore.Hosting.WebHost.BuildApplication()

Any suggestions?

[AndriySvyryd]

@bmcdavidepi You are using "Npgsql.EntityFrameworkCore.PostgreSQL" with version "6.0.0-preview3". Preview versions are not compatible, wait for "6.0.0-preview4" to be released or use "6.0.0-preview3" for all packages. The exception you posted comes from OData and is also likely caused by version mismatch.

@roji Is the using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata; change expected?

[roji]

@bmcdavidepi any chance you can post which type(s) required you to change the namespace to Npgsql.EntityFrameworkCore.PostgreSQL.Metadata?

[bmcdavidepi]

@roji, its the 'NpgsqlValueGenerationStrategy.SerialColumn' in the code below, which is in all of the migration designer files.

modelBuilder
                .HasAnnotation("Npgsql:PostgresExtension:citext", ",,")
                .HasAnnotation("Npgsql:PostgresExtension:uuid-ossp", ",,")
                .HasAnnotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn)
                .HasAnnotation("ProductVersion", "2.2.3-servicing-35854")
                .HasAnnotation("Relational:MaxIdentifierLength", 63);

[roji]

Thanks @bmcdavidepi. Looks like I moved this type in 5488caf054f3b56c2b8ab33b34221d6aeffa8619 (npgsql/efcore.pg#339); this happened back in 2.1 - are you sure you're upgrading from 2.2?

This probably wasn't the right thing to do, but it's been this way for a very long time now, with no particular problem (we generate the needed usings when scaffolding), and changing it now might cause the same problem in the other direction...

Any thoughts @AndriySvyryd? Do you think I should move this (and others) back into Microsoft.EntityFrameworkCore?

[bmcdavidepi]

@roji When I go to definition on that type this is what I see

#region Assembly Npgsql.EntityFrameworkCore.PostgreSQL, Version=5.0.5.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7
// C:\Users\{MyUserName}\.nuget\packages\npgsql.entityframeworkcore.postgresql\5.0.5\lib\netstandard2.1\Npgsql.EntityFrameworkCore.PostgreSQL.dll
#endregion

namespace Npgsql.EntityFrameworkCore.PostgreSQL.Metadata
{
    public enum NpgsqlValueGenerationStrategy
    {
        None = 0,
        SequenceHiLo = 1,
        SerialColumn = 2,
        IdentityAlwaysColumn = 3,
        IdentityByDefaultColumn = 4
    }
}

[bmcdavidepi]

@AndriySvyryd I changed the dependencies to this

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.0-preview.3.21201.13" />

Which does resolve the bad SQL query, could this change also be applied in a fix for the 5.x NuGet packages?

[AndriySvyryd]

@roji This is the relevant commit - npgsql/efcore.pg@926aa41

I think it should be reverted. At least until #18557 is implemented

[ajcvickers]

@bmcdavidepi We do not plan to bring the query fix to EF Core 5 due to the complexity/risk of the change. See release planning for more information on how we decide what to patch.

[ajcvickers]

@roji to take another look at the metadata change, since it seems to have been done very recently (Feb).

[roji]

@AndriySvyryd I see now, thanks for looking into this. So these were in Npgsql.EntityFrameworkCore.PostgreSQL since 2.1, and then moved to Microsoft.EntityFramework.Metadata for 6.0. to fix.

Since this latest change was done to fix a scaffolding issue (npgsql/efcore.pg#1613) (and since these ultimately should be under Microsoft.EntityFramework.Metadata, I'm not sure it's much better to change back... it indeed means that users upgrading may have to manually add a using to their migrations, but I'm not sure it's that bad.

We can discuss in triage/design.

[roji]

Note: see continued analysis into the Npgsql namespaces problem in #25046