Exception with PGSQL text array & select

[sherlock1982]

Assemblies affected

ASP.NET Core OData 8.2.2

Describe the bug

I'm using OData + EF 7 + NPGSQL. I have a field in DB which has a type text[]. When I try to use $select and include these field I got the following exception:

System.InvalidOperationException: The LINQ expression '$it => $it' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitLambda[T](Expression`1 lambdaExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCall)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.System.Collections.IEnumerable.GetEnumerator()
   at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSetAsync(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInlineAsync(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectAsync(Object graph, Type type, ODataMessageWriter messageWriter, ODataSerializerContext writeContext)
   at Microsoft.AspNetCore.OData.Formatter.ODataOutputFormatterHelper.WriteToStreamAsync(Type type, Object value, IEdmModel model, ODataVersion version, Uri baseAddress, MediaTypeHeaderValue contentType, HttpRequest request, IHeaderDictionary requestHeaders, IODataSerializerProvider serializerProvider)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeResultAsync>g__Logged|22_0(ResourceInvoker invoker, IActionResult result)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|30_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at ManagementConsoleJS.Middleware.ErrorLoggingMiddleware.Invoke(HttpContext context) in C:\root\Sources\Projects\Interface\src\ManagementConsoleJS\Middleware\ErrorLoggingMiddleware.cs:line 20
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at ManagementConsoleJS.Middleware.FixODataMiddlewareForNginx.Invoke(HttpContext context) in C:\root\Sources\Projects\Interface\src\ManagementConsoleJS\Middleware\FixODataMiddlewareForNginx.cs:line 55
   at ManagementConsoleJS.Middleware.BlacklistMiddleware.Invoke(HttpContext context) in C:\root\Sources\Projects\Interface\src\ManagementConsoleJS\Middleware\BlacklistMiddleware.cs:line 30
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

Reproduce steps The simplest set of steps to reproduce the issue. If possible, reference a commit that demonstrates the issue.

Data Model Please share your Data model, for example, your C# class.

[DataContract(Name = "EventLog")]
public class XEventLog
{
    [DataMember]
    [Key]
    [Column("ideventlog")]
    public int? Id { get; set; }
    [DataMember]
    [Column("params")]
    public string[]? Params { get; set; }
}

EDM (CSDL) Model

        var eventLog = builder.EntityType<XEventLog>();
        eventLog.Name = "EventLog";
        builder.EntitySet<XEventLog>("EventLogs");

Additional context

Note that if i directly call queryOptions.ApplyTo(service.Request(), AllowedQueryOptions.Select) it works fine. This is more/less ok - in this case i still got all fields from DB but OData will filter them for the HTTP.

I understand it might be NPGSQL issue. But at least you maybe can tell me where to dig it.

[xuzhg]

@sherlock1982 Did you try 8.2.3 version? and Can you share the linq expression for enumerable when breaks at 'WriteResourceSetAsync'?

[ghost]

@xuzhg I have the same problem. My data model is slightly different, but also contains a string array that leads to this exception. I have a breakpoint inside WriteResourceSetAsync() and have dumped the enumerable.Expression.DebugView if that helps:

.Call System.Linq.Queryable.Take(
    .Call System.Linq.Queryable.Select(
        .Call System.Linq.Queryable.OrderBy(
            .Extension<Microsoft.EntityFrameworkCore.Query.EntityQueryRootExpression>,
            '(.Lambda #Lambda1<System.Func`2[odata_mid.Models.StnHnrarray,System.Int32]>)),
        '(.Lambda #Lambda2<System.Func`2[odata_mid.Models.StnHnrarray,Microsoft.AspNetCore.OData.Query.Wrapper.SelectSome`1[odata_mid.Models.StnHnrarray]]>))
    ,
    .Constant<Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]>(Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]).TypedProperty)

.Lambda #Lambda1<System.Func`2[odata_mid.Models.StnHnrarray,System.Int32]>(odata_mid.Models.StnHnrarray $$it) {
    $$it.Id
}

.Lambda #Lambda2<System.Func`2[odata_mid.Models.StnHnrarray,Microsoft.AspNetCore.OData.Query.Wrapper.SelectSome`1[odata_mid.Models.StnHnrarray]]>(odata_mid.Models.StnHnrarray $$it)
{
    .New Microsoft.AspNetCore.OData.Query.Wrapper.SelectSome`1[odata_mid.Models.StnHnrarray](){
        Model = .Constant<Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[Microsoft.OData.Edm.IEdmModel]>(Microsoft.AspNetCore.OData.Query.Container.LinqParameterContainer+TypedLinqParameterContainer`1[Microsoft.OData.Edm.IEdmModel]).TypedProperty,
        Container = .New Microsoft.AspNetCore.OData.Query.Container.PropertyContainer+NamedPropertyWithNext2`1[System.String](){
            Name = "Plz",
            Value = $$it.Plz,
            Next0 = .New Microsoft.AspNetCore.OData.Query.Container.NamedProperty`1[System.String](){
                Name = "Ort",
                Value = $$it.Ort
            },
            Next1 = .New Microsoft.AspNetCore.OData.Query.Container.NamedProperty`1[System.Collections.Generic.IEnumerable`1[System.String]]()
            {
                Name = "HnrArray",
                Value = .Call System.Linq.Enumerable.Select(
                    $$it.HnrArray,
                    .Lambda #Lambda3<System.Func`2[System.String,System.String]>)
            },
            Next2 = .New Microsoft.AspNetCore.OData.Query.Container.AutoSelectedNamedProperty`1[System.Nullable`1[System.Int32]](){
                Name = "Id",
                Value = (System.Nullable`1[System.Int32])$$it.Id
            }
        }
    }
}

.Lambda #Lambda3<System.Func`2[System.String,System.String]>(System.String $$it) {
    $$it
}

The array is called HnrArray and the class itself is odata_mid.Models.StnHnrarray. Is that the data you need? I can share a complete sample app if necessary, just a PG table has to be setup and the connstring configured.

Thomas