List Of List to JsonB .Net 8

[zN3utr4l]

I'm migrating a .net 6 project to .net 8.

I have this entities:

public class Entity
{
    public List<List<SubEntity>>? SubEntities{ get; set; }
}

public class SubEntity
{
    public required string Key { get; set; }

    public required string Value { get; set; }
}

in .net 6 by setting HasColumnType('jsonb') everything is ok, now with .net 8 (https://www.npgsql.org/efcore/mapping/json.html#tojson-owned-entity-mapping) doing this:

public class EntityConfiguration : IEntityTypeConfiguration<Entity>
{
    public void Configure(EntityTypeBuilder<Entity> builder)
    {
        builder.OwnsMany(x => x.SubEntities, r => { r.ToJson(); });
    }
}

This error comes up: System.InvalidOperationException: Invalid token type: 'StartArray'.

2024-05-16 15:43:05.4282||ERROR|TrackMiddleware.cs|NLogAppender|Exception: System.InvalidOperationException: Invalid token type: 'StartArray'.
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.IncludeJsonEntityCollection[TIncludingEntity,TIncludedCollectionElement](QueryContext queryContext, Object[] keyPropertyValues, JsonReaderData jsonReaderData, TIncludingEntity entity, Func`4 innerShaper, Action`1 getOrCreateCollectionObject, Action`2 fixup, Boolean trackingQuery)
   at lambda_method718(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1.AddRange(IEnumerable`1 collection)
   at MyProj.PostgreSQL.Repository.Entity.EntityRepository.GetTopFavoriteEntity(Int32 max) in G:\MyProj\MyProj.Cloud\MyProj.PostgreSQL\Repository\Entity\EntityRepository.cs:line 34
   at MyProj.Web.Controllers.DashboardController.Index() in G:\MyProj\MyProj.Cloud\MyProj.Web\Controllers\DashboardController.cs:line 23
   at lambda_method698(Closure, Object, Object[])
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location ---
   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|7_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at MyProj.Web.Middleware.TrackMiddleware.Invoke(HttpContext context, IUnitOfWork unitOfWork) in G:\MyProj\MyProj.Cloud\MyProj.Web\Middleware\TrackMiddleware.cs:line 67 StackTrace:    at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.IncludeJsonEntityCollection[TIncludingEntity,TIncludedCollectionElement](QueryContext queryContext, Object[] keyPropertyValues, JsonReaderData jsonReaderData, TIncludingEntity entity, Func`4 innerShaper, Action`1 getOrCreateCollectionObject, Action`2 fixup, Boolean trackingQuery)
   at lambda_method718(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1.AddRange(IEnumerable`1 collection)
   at MyProj.PostgreSQL.Repository.Entity.EntityRepository.GetTopFavoriteEntity(Int32 max) in G:\MyProj\MyProj.Cloud\MyProj.PostgreSQL\Repository\Entity\EntityRepository.cs:line 34
   at MyProj.Web.Controllers.DashboardController.Index() in G:\MyProj\MyProj.Cloud\MyProj.Web\Controllers\DashboardController.cs:line 23
   at lambda_method698(Closure, Object, Object[])
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location ---
   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|7_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at MyProj.Web.Middleware.TrackMiddleware.Invoke(HttpContext context, IUnitOfWork unitOfWork) in G:\MyProj\MyProj.Cloud\MyProj.Web\Middleware\TrackMiddleware.cs:line 67 | G:\MyProj\MyProj.Cloud\MyProj.Web\Middleware\TrackMiddleware.cs:Invoke:127|

This is what the contents of the jsonb column look like when created with .net 6:

[
   [
      {
         "Key":"Test",
         "Value":"0"
      },
      {
         "Key":"Test2",
         "Value":"0"
      }
   ],
   [
      {
         "Key":"Test",
         "Value":"650"
      },
      {
         "Key":"Test2",
         "Value":"150"
      }
   ]
]

[roji]

@zN3utr4l which exact version of EF and EFCore.PG are you using? Can you make sure that it's the latest patch version for both?

If you're still getting the error, please post a minimal, runnable console program that shows the error happening - I can investigate at that point.

[zN3utr4l]

Minimal Repo: https://github.com/zN3utr4l/ErrorNpgsql-ListOfLists-Jsonb/tree/main

Yes, I'm using the latest version, specifically these are the dependencies:

<ItemGroup>
  <PackageReference Include="EFCore.NamingConventions" Version="8.0.3" />
  <PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="8.0.5" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.5">
    <PrivateAssets>all</PrivateAssets>
    <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    <TreatAsUsed>true</TreatAsUsed>
  </PackageReference>
  <PackageReference Include="Microsoft.EntityFrameworkCore.Proxies" Version="8.0.5" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="8.0.5">
    <PrivateAssets>all</PrivateAssets>
    <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    <TreatAsUsed>true</TreatAsUsed>
  </PackageReference>
  <PackageReference Include="NLog.Database" Version="5.3.2">
    <TreatAsUsed>true</TreatAsUsed>
  </PackageReference>
  <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="8.0.4" />
</ItemGroup>

[roji]

I ran your code, accessed the endpoint (/error-jsonb) and got an empty array, as expected (the database is empty). I then added the following to the handler:

context.Add(new MyEntity
{
    JsonbFields = [
        [new() { Key = "key1", Value = "value1"}],
        [new() { Key = "key2", Value = "value2"}]
    ]
});
context.SaveChanges();

After accessing the endpoint again, I get the expected output:

[{"id":1,"jsonbFields":[[{"key":"key1","value":"value1"}],[{"key":"key2","value":"value2"}]]}]

It's very likely to you have some data in your database that's somehow malformed and causing this; it's possible the EF's JSON deserializer (which is used when you map via owned entities) is stricter in some way, which that data used to work but doesn't any more.

Long story short, you'll have to track down the offending column data. If it looks like correct JSON, please post it here so I can investigate.

[zN3utr4l]

This is what comes back if it adds what you did.

app.MapGet("/error-jsonb", ([FromServices] CustomDbContext context) =>
{
    context.Add(new MyEntity
    {
        JsonbFields = [
        [new() { Key = "key1", Value = "value1"}],
        [new() { Key = "key2", Value = "value2"}]
    ]
    });

    context.SaveChanges();

    return context.MyEntity.ToList();
}).WithName("ErroJsonb");

Sorry, in DB it looks like in the photo, but you're right, the EF mapping comes back to me correctly: [{"id":3,"jsonbFields":[[{"key":"key1","value":"value1"}],[{"key":"key2","value":"value2"}]]}]

Why isn't it written in plain text in DB?

[zN3utr4l]

I changed the endpoint like this, to make the list of lists look like the json I sent initially, but now the entity I inserted before seems to no longer be serialized.

app.MapGet("/error-jsonb", ([FromServices] CustomDbContext context) =>
{
    MyEntity a = new()
    {
        JsonbFields = [
            [new() { Key = "key1", Value = "value1"}, new() { Key = "key2", Value = "value2"}],
            [new() { Key = "key1", Value = "value1"}, new() { Key = "key2", Value = "value2" }]
        ]
    };

    context.Add(a);

    context.SaveChanges();

    return context.MyEntity.ToList();
}).WithName("ErroJsonb");

[
   {
      "id":3,
      "jsonbFields":[
         [

         ],
         [

         ]
      ]
   },
   {
      "id":4,
      "jsonbFields":[
         [
            {
               "key":"key1",
               "value":"value1"
            },
            {
               "key":"key2",
               "value":"value2"
            }
         ],
         [
            {
               "key":"key1",
               "value":"value1"
            },
            {
               "key":"key2",
               "value":"value2"
            }
         ]
      ]
   }
]

Every time I rerun the program, the entity inserted in the last execution is no longer mapped and is empty as in the json sent above

[roji]

@zN3utr4l it really would be easiest if you could submit a simple console program - just a single page of code - which shows the exception happening. I'm spending a lot of time trying to understand all the snippets and screenshots above.

[zN3utr4l]

I put the ErrorNpgsqlJsonb project (https://github.com/zN3utr4l/ErrorNpgsql-ListOfLists-Jsonb), which is a console app, all in one file, hope it's fine now.

run the app console a first time and it should print the entities it created, but if you relaunch the app console a second time, you will see how the old created entities are not printed, they appear to be of type: Castle.Proxies.List1Proxy instead of System.Collections.Generic.List1[ErrorNpgsqlJsonb.JsonbField]

[zN3utr4l]

Any updates?

[roji]

I've taken another look, and I can't see the original error you reported above (Invalid token type: 'StartArray'.), are you now reporting a new problem? Is the original error gone? If so, it's better to not mix two problems in the same issue, but to open a new issue instead.

In any case, regarding the ToString() printing Castle.Proxies... - that's very likely by design; when you use LazyLoadingProxies, you're opting in to receiving proxies instead of your original entity types, and that's what the ToString() is showing. This happens only in the 2nd run since in the 1st run, the original entity type is still tracked by the context (from when you added it), and so that's returned. The 2nd time, a proxy is returned instead (see below for a simplified one-run repro where everything is in the same program).

/cc @ajcvickers for this possibly interesting "inconsistent" behavior; but to me everything seems like it's working as expected.

Simplified code sample, with a single run

```c#

[zN3utr4l]

Above I inserted the screen of PGAdmin with what is inserted in the JsonbFields field, I don't think it's correct, it is inserting the information of the first nested list i think, and not the content of the json.

[
  {
    "Capacity": 4
  },
  {
    "Capacity": 4
  }
]

This is the plain text that is in db, it's not an object, it's exactly how you read it.

From this it seems clear to me that something is wrong. As you can see, however, in the object above (which I manually inserted directly with PgAdmin) the plain text is correct.

This means that EF is writing to db wrong.

In fact, when I go to get the entities I get an empty object back, even though I had inserted the JsonbFields correctly

[ajcvickers]

@roji What you describe is by-design.