The specified type 'System.String' must be a non-interface reference type to be used as an entity type.

[dremlin2000]

Hi there, I am upgrading a .net project from EF Core 6 to version 8 and got a breaking change related to JSON column types.

public class User 
{
  public string[] Roles { get; private set; } = [];
}

In the current version I have been using

HasColumnType("jsonb")

for entity configuration which translates Roles column to JSON.

However, now if I try to use

OwnsMany(x => x.Roles, navigationBuilder => navigationBuilder.ToJson("Roles"));

then I get a following exception

Microsoft.EntityFrameworkCore.Design.OperationException: Unable to create a 'DbContext' of type ''. The exception 'The specified type 'System.String' must be a non-interface reference type to be used as an entity type.' was thrown while attempting to create an instance. For the different patterns supported at design time, see https://go.microsoft.com/fwlink/?linkid=851728_

Does it mean that EF 8 does not support JSON string arrays?

[roji]

EF's ToJson() is about mapping entity types, i.e. .NET types, and not primitives (like a string). So you cannot use ToJson() to map a string array.

You can continue mapping the property via HasColumnType("jsonb") as before, but consider mapping to a PostgreSQL array instead - this is the natural (and efficient) way to represent an array of strings in PostgreSQL (so the column type would be text[] instead of jsonb).

Note: it should still be possible to use EF's new primitive collection support to map to jsonb (including full querying capabilities), but this isn't supported at the moment. Opened #3122 to track.

[roji]

I'll go ahead and close this as the question has been answered, but if you need further clarifications don't hesitate to post back here.

[dremlin2000]

Hi @roji, Really appreciate your quick reply.

I totally understand that using Postgres array type column is a better approach but since the system I am upgrading to is in production, it seems that the only way to complete the upgrade and use toJson is to migrate JSON array columns with primitive types in the database to Postgres array type which is not a simple task and requires a manual data migration.

The other problem is that if I keep using HasColumnType("jsonb") as I currently do with EF 6 then EF 8 cannot translate such collection functions as Contains, Any etc. when I query database. From my point of view it is a breaking change which does not allow me to easily upgrade my projects.

[roji]

it seems that the only way to complete the upgrade and use toJson is to migrate JSON array columns with primitive types in the database to Postgres array type which is not a simple task and requires a manual data migration.

So again, ToJson() does not (and will not) work with primitive collections (e.g. string[]), it's not meant for that. There's no reason to try to "upgrade" to ToJson() - you can keep using the older technique with HasColumnType("jsonb") with EF Core 8.0. Whether you migrate from JSON arrays to PG arrays is a different question, and you can deal with that after upgrading from EF 6.0 to 8.0 - the two shouldn't be related.

From my point of view it is a breaking change which does not allow me to easily upgrade my projects.

Can you post a minimal, runnable code sample which worked with EF 6 but fails with EF 8?

[dremlin2000]

Probably I know what the problem I have with HasColumnType("jsonb") in EF 8.

I use a custom ValueConverter which converts all jsonb columns to string type.

Later when I try to query database EF 8 cannot call collection functions for string type.

User entity

public class User
{
  public Guid Id { get; set; }
  public string Name { get; set; }
  public List<string> Roles { get; set; } = [];
}

Custom MapJsonb helper function.

public static PropertyBuilder<TProperty> MapJsonb<TProperty>(this PropertyBuilder<TProperty> propertyBuilder, JsonSerializerSettings jsonSerializerSettings = null)
  {
    var converter = new ValueConverter<TProperty, string>(
      v => Serializer.Serialize(v, jsonSerializerSettings),
      v => Serializer.Deserialize<TProperty>(v, jsonSerializerSettings));

    var comparer = new ValueComparer<TProperty>(
      (l, r) => Serializer.Serialize<TProperty>(l, jsonSerializerSettings) == Serializer.Serialize<TProperty>(r, jsonSerializerSettings),
      v => v == null ? 0 : Serializer.Serialize<TProperty>(v, jsonSerializerSettings).GetHashCode(),
      v => Serializer.Deserialize<TProperty>(Serializer.Serialize<TProperty>(v, jsonSerializerSettings), jsonSerializerSettings));

    propertyBuilder.HasConversion(converter);
    propertyBuilder.Metadata.SetValueConverter(converter);
    propertyBuilder.Metadata.SetValueComparer(comparer);
    propertyBuilder.HasColumnType("jsonb");

    return propertyBuilder;
  }

User model definition.

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
      modelBuilder.Entity<User>(entity =>
        {
            entity.HasKey(x => x.Id);
            entity.Property(x => x.Roles).MapJsonb();
        });
    }

As a result of the custom value converter I get this.

 [DbContext(typeof(MyDbContext))]
    partial class MyDbContextModelSnapshot : ModelSnapshot
    {
        protected override void BuildModel(ModelBuilder modelBuilder)
        {
#pragma warning disable 612, 618
            modelBuilder
                .HasAnnotation("ProductVersion", "8.0.2")
                .HasAnnotation("Relational:MaxIdentifierLength", 63);

            NpgsqlModelBuilderExtensions.UseIdentityByDefaultColumns(modelBuilder);

            modelBuilder.Entity("Ef8UpgradeApi.Entities.User", b =>
                {
                    b.Property<Guid>("Id")
                        .ValueGeneratedOnAdd()
                        .HasColumnType("uuid");

                    b.Property<string>("Name")
                        .IsRequired()
                        .HasColumnType("text");

                    b.Property<string>("Roles")
                        .IsRequired()
                        .HasColumnType("jsonb");

                    b.HasKey("Id");

                    b.ToTable("Users");
                });
#pragma warning restore 612, 618
        }
    }

However, if I don't use my converter then I get b.Property<List<string>>("Roles") instead of b.Property<string>("Roles").

 b.Property<List<string>>("Roles")
                        .IsRequired()
                        .HasColumnType("jsonb");

[roji]

@dremlin2000 I'm a bit lost...

However, if I don't use my converter then I get b.Property<List>("Roles") instead of b.Property("Roles").

Why is that a problem? OTOH I'm not sure why you'd need a value converter here, given that you can map List<string> directly to jsonb without a value converter.

In any case, above you indicate that there was some breaking change, i.e. that some code that worked in EF6 doesn't work in EF8:

The other problem is that if I keep using HasColumnType("jsonb") as I currently do with EF 6 then EF 8 cannot translate such collection functions as Contains, Any etc. when I query database. From my point of view it is a breaking change which does not allow me to easily upgrade my projects

Is that really the case? If so, then can you please post a minimal, runnable code sample as I asked above?

To summarize, at the moment I'm not sure exactly what you're asking or what problems you're running into...

[dremlin2000]

@roji,

Why is that a problem? OTOH I'm not sure why you'd need a value converter here, given that you can map List directly to jsonb without a value converter.

It is a problem because EF 8 cannot translate collection functions to SQL statement. I am unsure the reason why the value converter is used since the code was added a long time back by the other developer. It works in EF6 as is but not anymore in EF8.

What I mean here is that the problem is in the custom value converter rather that EF8 itself.

[roji]

As I asked several times before, can you please post a code sample of what works in EF6 but not in EF8? Querying over a value-converted List shouldn't have worked in EF6 either, as far as I'm aware.

[dremlin2000]

@roji, you can use the code snippets with MapJsonb from my message above.

As I already noted if I run the code below with EF6 then it works but it does not with EF8.

await dbContext.Users
      .Where(x=> x.Roles.Contains("MyRole"))
      .ToArrayAsync()

I get the following exception when I run it with EF8.

System.InvalidOperationException: The LINQ expression 'DbSet<User>()
    .Where(u => u.Roles
        .Contains("MyRole"))' 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.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToArrayAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

[roji]

@dremlin2000 above you've posted various incomplete snippets - I've asked for a minimal, runnable code sample which I can simply run, and which shows the code working on EF 6 and not on 8. You're basically asking me to do work, and piece together a repro from your remarks and snippets, which includes quite a bit of guesswork on my side (which takes time).

I've tried to do this, and have not been able to reproduce the problem: when a string[] property is value-converted to a string and mapped to jsonb, querying over it with Contains fails for me (with EF 6). Please take a look at my attempt below, and tweak it as needed to show it working in EF6 but failing in EF8.

Attempted repro

```c# await using var context = new BlogContext(); await context.Database.EnsureDeletedAsync(); await context.Database.EnsureCreatedAsync(); _ = await context.Users.Where(x => x.Roles.Contains("MyRole")).ToListAsync(); public class BlogContext : DbContext { public DbSet Users { get; set; } 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() .Property(u => u.Roles) .HasColumnType("jsonb") .HasConversion( r => JsonSerializer.Serialize(r, JsonSerializerOptions.Default), s => JsonSerializer.Deserialize(s, JsonSerializerOptions.Default)); } } public class User { public int Id { get; set; } public string[] Roles { get; set; } } ```

In the future, when posting an issue, please always include a minimal, runnable console program that shows what you're doing. This saves everyone a lot of time.

[choby]

I encountered the same problem. When I map List<string>to the jsonb field in ef8, some places work but some places don't work. I need to modify List<string>to IEnumerable<string>, and I can't find the rule of such modification.

[roji]

@choby as above, we need a minimal, runnable sample to be able to help here.

[choby]

@choby as above, we need a minimal, runnable sample to be able to help here.

it's here : https://github.com/choby/ReproduceStringListMapToJsonbInEF8

[roji]

@choby that looks like your whole solution - can you please post a minimal sample, ideally just a small console program that shows the problem happening?

[choby]

@choby that looks like your whole solution - can you please post a minimal sample, ideally just a small console program that shows the problem happening?

I think it's small and simple enough. It only contains the dependent environment and required code to reproduce the problem.

[roji]

@choby there are 7 projects in that solution - I don't even know where to look for your problematic query... you're asking me to spend my time to understand your solution and narrow it down - can you help out by just distilling the problem to a minimal console program? I have tons of issues to investigate from users, and this adds more work to my plate.

[saragulov]

Hi, I think I know what the problem might be here.

In EF8: When I use Data Annotation attribute [Column(TypeName="jsonb")] for arrays (for example, string[]) it works fine. When I use Fluent API HasColumnType("jsonb") npgsql thinks that it is array field in the database and uses functions like unnest in sql.

But both cases worked fine in EF6.

using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Npgsql;

NpgsqlConnection.GlobalTypeMapper.EnableDynamicJson();

var serviceProvider = new ServiceCollection()
    .AddDbContext<AppDbContext>((_, builder) => builder
    .UseNpgsql("Host=localhost;Port=5432;Database=example;Username=postgres;Password=postgres;"))
    .BuildServiceProvider();

var dbContext = serviceProvider.GetRequiredService<AppDbContext>();
dbContext.Database.EnsureCreated();

// error here: function unnest(jsonb) does not exist (sql contains unnest(tags) to get first tag)
_ = dbContext.Entities.Select(x => x.Tags.FirstOrDefault()).ToList();

public class Entity
{
    public Guid Id { get; set; }

    //[Column(TypeName = "jsonb")] // Works fine with this!
    public string[] Tags { get; set; } = [];
}

public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public virtual DbSet<Entity> Entities { get; set; } = null!;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("example");

        modelBuilder.Entity<Entity>(entity =>
        {
            entity.ToTable("entities");

            entity.Property(e => e.Id)
                .HasColumnName("id");

            entity.Property(e => e.Tags)
                .HasColumnName("tags")
                .HasColumnType("jsonb");
        });
    }
}