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]

[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

[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.

[zN3utr4l]

Sorry for insisting but I don't understand if you don't consider this error a bug.

As I understand it, @roji doesn't consider it a bug, but in my opinion it's because, as I have repeated many times, you have to look at the db with PgAdmin, if you look at what it saves in the JsonBFields field you would immediately notice the problem.

[roji]

@zN3utr4l you originally reported an error with Invalid token type: 'StartArray'.; I asked you to provide a repro for that, but I haven't seen one yet.

In this comment of yours you asked a completely unrelated question regarding the ToString() behavior when using Castles.Proxies - both @ajcvickers and I consider this to be by design (no bug).

Now, if you want us to look into the original bug (Invalid token type: 'StartArray'.), you're going to have to provide an actual repro - I need to be able to see the bug happening here. Otherwise I have to assume that you maybe have bad/malformed JSON data in your actual database, and EF is erroring on that.

[zN3utr4l]

ToString() when using Castles.Proxies was a question, not a bug report.

you have to look at the db with PgAdmin, if you look at what it saves in the JsonBFields field you would immediately notice the problem.

But did you check this after running my repro?

The error Invalid token type: 'StartArray'.; it is generated by the fact that the Lists of List in the jsonb field are not saved correctly and some middleware that I had in the app (not in the repro) was unable to serialize.

look, let's do something, I'll open another issue where I don't refer to Invalid token type: 'StartArray'. But only to the fact that it is not saved correctly.

[roji]

it is generated by the fact that the Lists of List in the jsonb field are not saved correctly and some middleware that I had in the app (not in the repro) was unable to serialize.
[...]
I'll open another issue where I don't refer to Invalid token type: 'StartArray'. But only to the fact that it is not saved correctly.

From the above it sounds like you have some invalid JSON in your database, which wasn't generated by EF; if that's the case, then EF is behaving correctly.

If you believe EF itself is the one generating invalid JSON in SaveChanges, please submit a repro to show that.

[zN3utr4l]

Look, I don't know if I don't know how to explain myself or there is some other communication problem, the repro I made already shows this error.

Follow these simple steps.

1. Launch the app console.
2. Look at the output and keep the last id in mind, see if it reads well.
   
3. Relaunch the app console.
4. Note that the previous id which was read correctly is now completely empty.
   
5. Open PgAdmin.
6. Note that, as in the images I sent you, the json does not actually contain the data I wrote in the app console, but contains, I'm not sure, information regarding the first list.
   

Please do exactly as I wrote, because I've been repeating it for 10 comments and I don't think you've done it, otherwise you would have already noticed that something is wrong.

[roji]

@zN3utr4l thanks, I finally managed to "Capacity: 4" in the database, which is definitely a bug; I'm pretty sure that List<List<X>> isn't currently supported by EF - but even if that's the case, it should be erroring in validation and not saving "Capacity: 4" to the database. I've opened dotnet/efcore#33913 on the EF side to discuss this, and will close this issue here (as this isn't a PostgreSQL-specific problem) - please follow that issue.

A couple comments on the above issue... First, see dotnet/efcore#33913 for what a minimal repro looks like - it's a console program, with just the lines needed to actually reproduce the issue; the repros you've been providing consist of projects with many files, needlessly using ASP.NET (rather than a simple console program), etc. That's fine, but open source maintainers (like myself) need to go through dozens of issues each day, and the more minimal/tight the repo, the more you're helping us understand what's going on.

In addition, this issue started with a non-reproducible "Invalid token type: 'StartArray'", continued to an unrelated question on the ToString() representation with Castle.Proxies, and finally ended up with the actual issue. Again, that's absolutely OK - but it doesn't help me remember what this issue is about when I revisit it and re-read it from the top. I advise making an effort to keep issues very focused, dealing with a single problem, and containing a small, completely clear code repro; that saves everyone's time and helps maintainers manage their repos.