Efcore.pg: Support value converters with generic Lists

[suadev]

After upgrading to 5.0.1, some query service tests got failed. I put 2 sample below.

Exception: "Couldn't find array or element type mapping in ArrayAnyAllExpression"

I guess the root cause is 'Contains' but not sure about it.

        var query = _folioTransactions.Where(x => x.Type == FolioTransactionType.Charge &&
                                                      x.PostingDate >= startDate && x.PostingDate <= endDate &&
                                                      typeList.Contains(x.Revenue.Type));

query.Where(x => x.Folio.ProfileId == profileId).SumAsync(x => x.GrossBalance); // here

DbSet.AsNoTracking()
                .Where(s => activityTypes.Contains(s.ActivityType) && s.Culture == baseLang)
                .ToListAsync();

Previous versions;

"Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.1.4"
"Microsoft.EntityFrameworkCore" Version="3.1.7"

Stack Trace:

"{\r\n \"errorId\": \"c1af4766-2bea-4cb0-989f-c831686bc6f8\",\r\n \"statusCode\": 500,\r\n \"errors\": [\r\n \"Couldn't find array or element type mapping in ArrayAnyAllExpression\",\r\n \" at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.ApplyTypeMappingsOnItemAndArray(SqlExpression itemExpression, SqlExpression arrayExpression)\r\n at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.ApplyTypeMappingOnAny(PostgresAnyExpression postgresAnyExpression)\r\n at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.ApplyTypeMapping(SqlExpression sqlExpression, RelationalTypeMapping typeMapping)\r\n at Microsoft.EntityFrameworkCore.Query.SqlExpressionFactory.ApplyDefaultTypeMapping(SqlExpression sqlExpression)\r\n at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.Any(SqlExpression item, SqlExpression array, PostgresAnyOperatorType operatorType)\r\n at Npgsql.EntityFrameworkCore.PostgreSQL.Query.ExpressionTranslators.Internal.NpgsqlArrayTranslator.g__TranslateCommon|7_1(SqlExpression arrayOrList, IReadOnlyList1 arguments, <>c__DisplayClass7_0& )\\r\\n at Npgsql.EntityFrameworkCore.PostgreSQL.Query.ExpressionTranslators.Internal.NpgsqlArrayTranslator.Translate(SqlExpression instance, MethodInfo method, IReadOnlyList1 arguments, IDiagnosticsLogger1 logger)\\r\\n at Microsoft.EntityFrameworkCore.Query.RelationalMethodCallTranslatorProvider.<>c__DisplayClass4_0.<Translate>b__3(IMethodCallTranslator t)\\r\\n at System.Linq.Enumerable.SelectEnumerableIterator2.MoveNext()\r\n at System.Linq.Enumerable.TryGetFirstTSource\r\n at System.Linq.Enumerable.FirstOrDefaultTSource\r\n at Microsoft.EntityFrameworkCore.Query.RelationalMethodCallTranslatorProvider.Translate(IModel model, SqlExpression instance, MethodInfo method, IReadOnlyList1 arguments, IDiagnosticsLogger1 logger)\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.VisitBinary(BinaryExpression binaryExpression)\r\n at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)\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.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate)\r\n at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorTResult\r\n at Microsoft.EntityFrameworkCore.Storage.Database.CompileQueryTResult\r\n at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCoreTResult\r\n at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.<ExecuteAsync>b__0()\\r\\n at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsyncTResult\r\n at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsyncTResult\r\n at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetAsyncEnumerator(CancellationToken cancellationToken)\\r\\n at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable1.GetAsyncEnumerator()\r\n at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsyncTSource in C:\\source\\Pms.Backend\\src\\Pms.Core\\Domain\\Folio\\FolioActivityDomainService.cs:line 89\r\n at Pms.Core.Domain.Folio.FolioActivityDomainService.GetTransactionActivities(Guid transactionId, Guid userId, Guid propertyId) in C:\\source\\Pms.Backend\\src\\Pms.Core\\Domain\\Folio\\FolioActivityDomainService.cs:line 52\r\n at Pms.Core.Application.Folio.Queries.GetTransactionActivitiesQueryHandler.Handle(GetTransactionActivitiesQuery query, CancellationToken cancellationToken) in C:\\source\\Pms.Backend\\src\\Pms.Core\\Application\\Folio\\Queries\\GetTransactionActivitiesQueryHandler.cs:line 26\r\n at MediatR.Pipeline.RequestExceptionProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate1 next)\r\n at MediatR.Pipeline.RequestExceptionProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate1 next)\r\n at MediatR.Pipeline.RequestExceptionActionProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate1 next)\r\n at MediatR.Pipeline.RequestExceptionActionProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate1 next)\r\n at MediatR.Pipeline.RequestPostProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate1 next)\r\n at MediatR.Pipeline.RequestPreProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate1 next)\r\n at Infrastructure.StartupConfiguration.MediatR.RequestLoggerBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate1 next) in C:\\source\\Pms.Backend\\src\\Infrastructure\\StartupConfiguration\\MediatR\\RequestLoggerBehavior.cs:line 24\r\n at Pms.Api.Controllers.FoliosController.GetChargeActivities(Guid id, Guid chargeId) in C:\\source\\Pms.Backend\\src\\Pms.Api\\Controllers\\FoliosController.cs:line 200\r\n at lambda_method5930(Closure , Object )\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)\r\n at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)\r\n at Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult)\r\n at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)\r\n at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)\r\n at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)\r\n at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)\r\n at Infrastructure.Middleware.GlobalExceptionHandlerMiddleware.Invoke(HttpContext context, IResourceRepository resources) in C:\\source\\Pms.Backend\\src\\Infrastructure\\Middleware\\GlobalExceptionHandlerMiddleware.cs:line 34\"\r\n ]\r\n}"

[suadev]

Maybe duplication of https://github.com/npgsql/efcore.pg/issues/1559

As @roji said, should be fixed in 5.0.1, so there must be something else.

[roji]

@suadev can you please post a minimal, runnable code sample? It's really difficult to investigate from partial fragments such as the above - I need to see the types of the properties being accessed in the query, etc.

[suadev]

I was afraid of that :) But, guess what happened? I downgraded to 5.0.0-rc2 and it worked.

So maybe you can figure it out by comparing the versions?

Sorry, since this is a large scale app, it's too hard to create a minimal sample.

But if you can't find the root, I am here ready to help.

[roji]

@suadev I can't really compare anything to 5.0.0-rc2, because I don't have actual code to compare :)

Can you at least try extracting specifically the queries you mentioned above, creating the minimal model around them, and see if that reproduces the issue? I'd have to do the same on my side, except I don't even have your model...

[Kampfmoehre]

I have the same problem with a query that worked in 5.0.1:

List<Guid> orgaIds = await _organisationService.GetOrganisationIdsByCustomer(customerId);
return await Context.Project.Where(p => orgaIds.Contains(p.OrganisationId)).ToListAsync(cancellationToken);

In my case the list to compare only included one item with Guid cdaac8b3-57ff-471d-a75e-c262e9d2d89f (just test data from my development db). This is the stacktrace:

System.InvalidOperationException: Couldn't find array or element type mapping in ArrayAnyAllExpression
at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.ApplyTypeMappingsOnItemAndArray(SqlExpression itemExpression, SqlExpression arrayExpression)
at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.ApplyTypeMappingOnAny(PostgresAnyExpression postgresAnyExpression)
at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.ApplyTypeMapping(SqlExpression sqlExpression, RelationalTypeMapping typeMapping)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionFactory.ApplyDefaultTypeMapping(SqlExpression sqlExpression)
at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.Any(SqlExpression item, SqlExpression array, PostgresAnyOperatorType operatorType)
at Npgsql.EntityFrameworkCore.PostgreSQL.Query.ExpressionTranslators.Internal.NpgsqlArrayTranslator.g__TranslateCommon|7_1(SqlExpression arrayOrList, IReadOnlyList1 arguments, <>c__DisplayClass7_0& ) at Npgsql.EntityFrameworkCore.PostgreSQL.Query.ExpressionTranslators.Internal.NpgsqlArrayTranslator.Translate(SqlExpression instance, MethodInfo method, IReadOnlyList1 arguments, IDiagnosticsLogger1 logger) at Microsoft.EntityFrameworkCore.Query.RelationalMethodCallTranslatorProvider.<>c__DisplayClass4_0.<Translate>b__3(IMethodCallTranslator t) at System.Linq.Enumerable.SelectEnumerableIterator2.MoveNext()
at System.Linq.Enumerable.TryGetFirstTSource
at System.Linq.Enumerable.FirstOrDefaultTSource
at Microsoft.EntityFrameworkCore.Query.RelationalMethodCallTranslatorProvider.Translate(IModel model, SqlExpression instance, MethodInfo method, IReadOnlyList1 arguments, IDiagnosticsLogger1 logger)
at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCall)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression)
at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorTResult
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQueryTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCoreTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.<ExecuteAsync>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsyncTResult
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsyncTResult
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetAsyncEnumerator(CancellationToken cancellationToken) at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable1.GetAsyncEnumerator()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsyncTSource
at

The Model code is:

public partial class Project
  {
    public Project()
    {
    }

    public Guid Id { get; set; }

    public Guid OrganisationId { get; set; }
  }

[roji]

@Kampfmoehre as above, I need a runnable code sample - the fragments aren't enough to investigate. I tried with the below code and everything seems to be working fine.

Attempted repro

```c#
await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

List guids = new List { Guid.NewGuid() };
_ = await ctx.Blogs.Where(b => guids.Contains(b.SomeGuid)).ToListAsync();

public class BlogContext : DbContext
{
public DbSet Blogs { get; set; }

static ILoggerFactory ContextLoggerFactory
    => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseNpgsql(@"Host=localhost;Username=test;Password=test")
        .EnableSensitiveDataLogging()
        .UseLoggerFactory(ContextLoggerFactory);

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
}

}

public class Blog
{
public int Id { get; set; }
public Guid SomeGuid { get; set; }
}
```

[Kampfmoehre]

@roji I have created a quick repo which shows the error when run against my Database. Unfortunately I cannot expose my DB for you to test but I am working on adding seed data so you can test against a local DB. Until then can you try it against your own DB?

https://github.com/droidsolutions/npgsql_test

[roji]

@Kampfmoehre thanks for the repro, I can see the error happening. The reason is that you're mapping the OrganisationId property (which is a Guid) to a database character varying. If you map to a uuid instead, the code works.

I'll investigate why this is happening.

[roji]

@suadev can you please confirm whether you're also using a value converter?

[suadev]

Yes, There is an enum-to-string conversion for both cases.

   builder.Property(p => p.Type).HasConversion<string>();

   builder.Property(fa => fa.ActivityType).HasConversion<string>();

[Kampfmoehre]

@roji Thanks for the quick response. The column is indeed character varying but the content is always a UUID, I tried to set an explicit conversion via

        entity.Property(e => e.OrganisationId)
                  .IsRequired()
                  .HasColumnName("organisationId")
                  .HasColumnType("character varying")
                  .HasConversion(p => p.ToString(), p => (Guid)Guid.Parse(p));

but i still get the error. Is there anything else I can do to make that work? Unfortunately I cannot change the type of the column (the database is originally created and used by a NodeJS application).

[roji]

This started failing in 5.0.1 because of #1589, which improved support for generic List, but left out full support for value conversions. I've just submitted #1610 to fix this.

You can switch to using an array instead of a List for the meantime, or go back to 5.0.0 (for EFCore.PG only - EF Core can stay at 5.0.1) until 5.0.2 is released with the fix.