PostgreSQL JSONB Array not mapped correctly

Is there an existing issue for this?

• I have searched the existing issues

Describe the bug

An array with the PostgreSQL-JSONB-Datatype does not get mapped correctly.

Further details are shown below...

Steps to reproduce

1. I have the following Entity-Classes:

public class Company
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string? Description { get; set; }
    // configured with JSONB-Datatype
    public List<Tag> Tags { get; set; } = new List<Tag>();
    // configured with JSONB-Datatype
    public CompanyDetails Details { get; set; } = new CompanyDetails();
}

public class Tag
{
    public string Name { get; set; } = string.Empty;
}

public class CompanyDetails
{
    public string Detail1 { get; set; } = string.Empty;

    public string Detail2 { get; set; } = string.Empty;

    public string Detail3 { get; set; } = string.Empty;
}

As you can see, the class Company contains a list of Tags, and Company_Details which both are stored as JSONB within the database.

When I run the following GraphQL-Query:

companies {
  id
  name
  details {
    detail1
    detail2
    detail3
  }
}

... I get the following output ...

{
  "data": {
    "companies": [
      {
        "id": 1,
        "name": "Company 1",
        "details": {
          "detail1": "Content1",
          "detail2": "Content2",
          "detail3": "Content3"
        }
      }
    ]
  }
}

... which is the expexted result.
The corresponding mapped SQL-statement shows, that the mapping to the JSONB-DB-Type works as expected;

SELECT c.id, c.name, c.details IS NOT NULL, c.details->>'Detail1', c.details->>'Detail2', c.details->>'Detail3'
FROM companies AS c

Now the problem

When running the following GraphQL-Query:

companies {
  id
  name
  details {
    detail1
    detail2
    detail3
  }
  tags {     # added
    name     # added
  }          # added
}

... I get the following error ...

{
  "errors": [
    {
      "message": "Unexpected Execution Error",
      "locations": [
        {
          "line": 2,
          "column": 3
        }
      ],
      "path": [
        "companies"
      ],
      "extensions": {
        "message": "The LINQ expression 'p1 => new Tag{ Name = p1.Name }\r\n' 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.",
        "stackTrace": "   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitLambda[T](Expression`1 lambdaExpression)\r\n   at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCall)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCall)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)\r\n   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)\r\n   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()\r\n   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)\r\n   at HotChocolate.Data.ToListMiddleware`1.InvokeAsync(IMiddlewareContext context)\r\n   at HotChocolate.Types.EntityFrameworkObjectFieldDescriptorExtensions.<>c__DisplayClass2_1`1.<<UseDbContext>b__4>d.MoveNext()\r\n--- End of stack trace from previous location ---\r\n   at HotChocolate.Types.EntityFrameworkObjectFieldDescriptorExtensions.<>c__DisplayClass2_1`1.<<UseDbContext>b__4>d.MoveNext()\r\n--- End of stack trace from previous location ---\r\n   at HotChocolate.Execution.Processing.Tasks.ResolverTask.ExecuteResolverPipelineAsync(CancellationToken cancellationToken)\r\n   at HotChocolate.Execution.Processing.Tasks.ResolverTask.TryExecuteAsync(CancellationToken cancellationToken)"
      }
    }
  ]
}

When adding the [UseProjection]-Attribute to the Tags-Property of the Company-class, I get the following result:

{
  "data": {
    "companies": [
      {
        "id": 1,
        "name": "Company 1",
        "details": {
          "detail1": "Content1",
          "detail2": "Content2",
          "detail3": "Content3"
        },
        "tags": []
      }
    ]
  }
}

... although the content of the tags-column in the database is:

[{ "Name": "Name hier" }]

As you can see in the corresponding SQL-statement the tags-column does not get selected:

SELECT c.id, c.name, c.details IS NOT NULL, c.details->>'Detail1', c.details->>'Detail2', c.details->>'Detail3'
FROM companies AS c

Do you have any ideas how to make this work or is it a bug of HC?

Kind regards,
Sebastian

Relevant log output

No response

Additional Context?

I use

• ASP.NET 6
• EFCore 6.0
• Npgsql 6.0
• HC 12.2.1
• PostgreSQL 12

Product

Hot Chocolate

Version

12.2.1

Start by reproducing the Company query with the JSONB Tags array and compare the generated SQL for Tags with the working Details projection. Trace the EF Core projection through the Hot Chocolate Entity Framework middleware and Npgsql translation shown in the stack trace. Done means the tags are selected from PostgreSQL, mapped into Tag objects, and returned without the translation error.