npgsql
Arrays of type with value converter fail in query generation
Considering the following enum
MyEnum.cs
public enum MyEnum
{
Case1,
Case2,
Case3,
}
public static class MyEnumExtensions
{
public static string GetString(this MyEnum value) => value switch
{
MyEnum.Case1 => "case_1",
MyEnum.Case2 => "case_2",
MyEnum.Case3 => "case_3",
_ => throw new ArgumentOutOfRangeException(nameof(value), value, null),
};
public static MyEnum GetMyEnum(this string value) => value switch
{
"case_1" => MyEnum.Case1,
"case_2" => MyEnum.Case2,
"case_3" => MyEnum.Case3,
_ => throw new ArgumentOutOfRangeException(nameof(value), value, null),
};
}
contained in the following model
MyModel.cs
[Table("my_model")]
public sealed class MyModel
{
[Key]
[Column("id")]
public long Id { get; set; }
[Column("my_enum")]
[StringLength(16)]
public MyEnum MyEnum { get; set; }
[Column("my_enums", TypeName = "varchar(16)[]")]
public MyEnum[] MyEnums { get; set; } = [];
}
configured using the following db context
ApplicationDbContext.cs
public sealed class ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : DbContext(options)
{
public DbSet<MyModel> MyModels { get; init; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
var dataSource = new NpgsqlDataSourceBuilder("Host=127.0.0.1:5432;Database=test;Username=postgres;Password=MySecretPwd");
optionsBuilder.UseNpgsql(dataSource.Build())
.EnableDetailedErrors()
.EnableSensitiveDataLogging();
}
private sealed class MyEnumValueConverter() : ValueConverter<MyEnum, string>(
e => e.GetString(),
s => s.GetMyEnum()
);
private sealed class MyEnumsValueConverter() : ValueConverter<MyEnum[], string[]>(
enums => enums.Select(e => e.GetString()).ToArray(),
strings => strings.Select(s => s.GetMyEnum()).ToArray()
);
protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
base.ConfigureConventions(configurationBuilder);
configurationBuilder.Properties<MyEnum>().HaveConversion<MyEnumValueConverter>();
configurationBuilder.Properties<MyEnum[]>().HaveConversion<MyEnumsValueConverter>();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<MyModel>(entity =>
{
entity.Property(e => e.MyEnum)
.HasConversion<MyEnumValueConverter>();
entity.Property(e => e.MyEnums)
.HasConversion<MyEnumsValueConverter>(ValueComparer.CreateDefault<MyEnum[]>(true));
entity.HasData(new MyModel
{
Id = 1,
MyEnum = MyEnum.Case3,
MyEnums = [MyEnum.Case1, MyEnum.Case2],
});
});
}
}
finally queried using e.g. the following controller
MyModelController.cs
[ApiController]
[Route("[controller]")]
public sealed class MyModelController(ApplicationDbContext dbContext) : ControllerBase
{
[HttpGet]
public IAsyncEnumerable<MyModel> Get([FromQuery] string? enumValue, [FromQuery] bool checkArray = false)
{
if (enumValue is null) return dbContext.MyModels.AsAsyncEnumerable();
var myEnum = enumValue.GetMyEnum();
return checkArray
? dbContext.MyModels.Where(m => m.MyEnums.Contains(myEnum)).AsAsyncEnumerable()
: dbContext.MyModels.Where(m => m.MyEnum == myEnum).AsAsyncEnumerable();
}
}
fails when generating the query for the MyEnums.Contains with the following exception
Exception Backtrace
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
An unhandled exception has occurred while executing the request.
System.InvalidOperationException: The LINQ expression 'DbSet<MyModel>()
.Where(m => m.MyEnums
.Contains(__myEnum_0))' could not be translated. Additional information: Translation of method 'System.Linq.Enumerable.Contains' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. 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.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
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__DisplayClass12_0`1.<ExecuteAsync>b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
at System.Text.Json.Serialization.Converters.IAsyncEnumerableOfTConverter`2.OnWriteResume(Utf8JsonWriter writer, TAsyncEnumerable value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.Serialization.JsonCollectionConverter`2.OnTryWrite(Utf8JsonWriter writer, TCollection value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.Serialization.JsonConverter`1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.Serialization.JsonConverter`1.WriteCore(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.Serialization.Metadata.JsonTypeInfo`1.SerializeAsync(Stream utf8Json, T rootValue, CancellationToken cancellationToken, Object rootValueBoxed)
at System.Text.Json.Serialization.Metadata.JsonTypeInfo`1.SerializeAsync(Stream utf8Json, T rootValue, CancellationToken cancellationToken, Object rootValueBoxed)
at System.Text.Json.Serialization.Metadata.JsonTypeInfo`1.SerializeAsync(Stream utf8Json, T rootValue, CancellationToken cancellationToken, Object rootValueBoxed)
at System.Text.Json.Serialization.Metadata.JsonTypeInfo`1.SerializeAsync(Stream utf8Json, T rootValue, CancellationToken cancellationToken, Object rootValueBoxed)
at Microsoft.AspNetCore.Mvc.Formatters.SystemTextJsonOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
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.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
I've tried both, configuring the value converter in the conventions and on the property directly (both are present in the sample code above).
I've also tried leaving away the value converter for the array type, which results in a query being generated that incorrectly uses the case name instead of the string value specified by the value converter:
Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (17ms) [Parameters=[@__myEnum_0='Case2' (Nullable = false)], CommandType='Text', CommandTimeout='30']
SELECT m.id, m.my_enum, m.my_enums
FROM my_model AS m
WHERE m.my_enums @> ARRAY[@__myEnum_0]::varchar(16)[]
Note that the non-array field MyEnum works like a charm:
Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (2ms) [Parameters=[@__myEnum_0='case_3' (Nullable = false)], CommandType='Text', CommandTimeout='30']
SELECT m.id, m.my_enum, m.my_enums
FROM my_model AS m
WHERE m.my_enum = @__myEnum_0
Am I missing anything here? To be honest, I would have expected that defining a value converter for a given type would also convert (Postgres) arrays of that type. But I could also live with having to define separate value converters for arrays (and then possibly also lists) if that worked.
To be honest, I would have expected that defining a value converter for a given type would also convert (Postgres) arrays of that type.
You should indeed be defining a value converter on the element type of MyEnums, rather than a value converter for the entire array; once you do the latter, EF has no way of knowing what the actual internal representation is that's returned by your value converter, and so is unable to translate queries over it.
Try the following:
modelBuilder.Entity<MyModel>().PrimitiveCollection(b => b. MyEnums).ElementType().HasConversion(typeof(MyEnumValueConverter));
Let me know if that solves it for you. Unfortunately the EF docs are quite behind on these details...
@roji Thanks for the quick reply!
Try the following:
modelBuilder.Entity<MyModel>().PrimitiveCollection(b => b.MyEnums).ElementType().HasConversion(typeof(MyEnumValueConverter));
Thanks for the hint here - I really haven't seen the PrimitiveCollection API anywhere yet.
Unfortunately, it still doesn't work and it still uses the enum case name in the query:
Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (15ms) [Parameters=[@__myEnum_0='Case2' (Nullable = false)], CommandType='Text', CommandTimeout='30']
SELECT m.id, m.my_enum, m.my_enums
FROM my_model AS m
WHERE m.my_enums @> ARRAY[@__myEnum_0]::varchar(16)[]
Here's the db context code I'm now using:
ApplicationDbContext.cs
public sealed class ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : DbContext(options)
{
public DbSet<MyModel> MyModels { get; init; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
var dataSource = new NpgsqlDataSourceBuilder("Host=127.0.0.1:5432;Database=test;Username=postgres;Password=MySecretPwd");
optionsBuilder.UseNpgsql(dataSource.Build())
.EnableDetailedErrors()
.EnableSensitiveDataLogging();
}
private sealed class MyEnumValueConverter() : ValueConverter<MyEnum, string>(
e => e.GetString(),
s => s.GetMyEnum()
);
protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
base.ConfigureConventions(configurationBuilder);
configurationBuilder.Properties<MyEnum>().HaveConversion<MyEnumValueConverter>();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<MyModel>(entity =>
{
entity.Property(e => e.MyEnum)
.HasConversion<MyEnumValueConverter>();
entity.PrimitiveCollection(e => e.MyEnums)
.ElementType()
.HasConversion<MyEnumValueConverter>();
entity.HasData(new MyModel
{
Id = 1,
MyEnum = MyEnum.Case3,
MyEnums = [MyEnum.Case1, MyEnum.Case2],
});
});
}
}
Btw. is this primitive collection configuration also possible via conventions? In my original project we're configuring these enums via an attribute where we then configure the enums with that attribute in the ConfigureConventions method.
I got this working using the PrimitiveCollection trick :)
EF support enums as strings. I got it working using
modelBuilder.Entity<MyModel>().PrimitiveCollection(b => b.MyEnums).ElementType().HasConversion<string>();
I tested this query and it worked!
_context.Foos.Where(x => x.MyEnums.Contains(MyEnum.Foo))
Thank you!
It seems to work with the default EnumToStringConverter (which is what HasConversion<string>() uses according to the docs). But custom converters seem to be ignored at least partially. My HasData call seems to use it, but the query generated by the controller call doesn't.
I've taken another look at this, and I can't reproduce the problem; the code below seems to work well with EF 8.0.10:
SELECT m."Id", m.my_enums
FROM "MyModels" AS m
WHERE m.my_enums @> ARRAY[@__myEnum_0]::varchar(16)[]
It's possible that a bug has been fixed since this issue was filed, or that the code sample below isn't the right one - it's always difficult to try to reconstruct a repro from a bunch of snippets as above (please always post a minimal, runnable code sample, such as a console program).
In any case, as this open has been open for a while and there's no repro, I'll go ahead and close the issue. If you're still encountering an issue, please tweak the code below to show it failing or post a similar repro.
Full repro
await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();
var myEnum = MyEnum.Case2;
_ = await context.MyModels.Where(m => m.MyEnums.Contains(myEnum)).ToListAsync();
public class BlogContext : DbContext
{
public DbSet<MyModel> MyModels { get; init; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseNpgsql("Host=localhost;Username=test;Password=test")
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<MyModel>(entity =>
{
entity.PrimitiveCollection(e => e.MyEnums)
.ElementType()
.HasConversion<MyEnumValueConverter>();
entity.HasData(new MyModel
{
Id = 1,
MyEnums = [MyEnum.Case1, MyEnum.Case2],
});
});
}
private sealed class MyEnumValueConverter()
: ValueConverter<MyEnum, string>(e => e.GetString(), s => s.GetMyEnum()
);
}
public class MyModel
{
public int Id { get; set; }
[Column("my_enums", TypeName = "varchar(16)[]")]
public MyEnum[] MyEnums { get; set; } = [];
}
public enum MyEnum { Case1, Case2, Case3 }
public static class MyEnumExtensions
{
public static string GetString(this MyEnum value) => value switch
{
MyEnum.Case1 => "case_1",
MyEnum.Case2 => "case_2",
MyEnum.Case3 => "case_3",
_ => throw new ArgumentOutOfRangeException(nameof(value), value, null),
};
public static MyEnum GetMyEnum(this string value) => value switch
{
"case_1" => MyEnum.Case1,
"case_2" => MyEnum.Case2,
"case_3" => MyEnum.Case3,
_ => throw new ArgumentOutOfRangeException(nameof(value), value, null),
};
}