Error 'The database returned the empty string when a JSON object was expected' while all values are not empty

[MaikelOrisha]

Bug description

We are encountering the following exception when querying a JSON-owned property:

The database returned the empty string when a JSON object was expected

What makes this issue confusing is that when we manually run the SQL query generated by EF Core, we do not see any empty values in the column that is mapped to the owned JSON property.

Additionally, we are observing some unexpected behavior:

• Querying the first 10 records triggers the exception, but querying the first 5 and next 5 records separately works fine.
• When we remove AsSplitQuery(), the query seems to work as expected.
• When we do not use the async version of the ToList method, it also works without issue.

I have spent several hours trying to create a minimal reproduction, but unfortunately, I was not able to reproduce the issue in a clean project. This makes it harder to pinpoint, but I am happy to provide more context, details, or even set up a screen share session if that helps in identifying the root cause.

Your code

var items = await _tenantDbContext.Items
    .AsSplitQuery() // <-- When we remove this line, it works.
    .Include(x => x.Attributes) // <-- When we remove this line, it works.
    .Include(x => x.Images)
    .OrderBy(e => e.ItemCode)
    .Skip(request.Skip)
    .Take(request.Take)
    .ToListAsync(cancellationToken); // <-- When we use the 'ToList', it works.

public class Item
{
    public required string ItemCode { get; set; }
    // (...)
    public List<ItemAttribute> Attributes { get; set; } = [];
    // (...)
}

public abstract class ItemAttribute
{
    public required string ItemCode { get; set; }
    public required string AttributeKey { get; set; }
    public int Order { get; set; }

    // (...)

    public class Checkbox : ItemAttribute
    {
        public bool Value { get; set; }
    }

    public class LocaleText : ItemAttribute
    {
        public LocaleValue<string> Value { get; set; } = new(); // <-- This is a owned complex object mapped to a JSON column.
    }

    public class Number : ItemAttribute
    {
        public double Value { get; set; }
    }

    public class Text : ItemAttribute
    {
        public required string Value { get; set; }
    }
}

Stack traces

System.InvalidOperationException: The database returned the empty string when a JSON object was expected.
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerOwnedJsonTypeMapping.CreateUtf8Stream(String json)
   at lambda_method2363(Closure, QueryContext, DbDataReader, ResultContext, SplitQueryResultCoordinator)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.PopulateSplitIncludeCollectionAsync[TIncludingEntity,TIncludedEntity](Int32 collectionId, RelationalQueryContext queryContext, IExecutionStrategy executionStrategy, RelationalCommandResolver relationalCommandResolver, IReadOnlyList`1 readerColumns, Boolean detailedErrorsEnabled, SplitQueryResultCoordinator resultCoordinator, Func`3 childIdentifier, IReadOnlyList`1 identifierValueComparers, Func`5 innerShaper, Func`4 relatedDataLoaders, INavigationBase inverseNavigation, Action`2 fixup, Boolean trackingQuery)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.TaskAwaiter(Func`1[] taskFactories)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at OrishaPlatform.Application.Items.Queries.GetAll.ItemGetAllQueryHandler.Handle(ItemGetAllQuery request, CancellationToken cancellationToken)
   at OrishaPlatform.Application.Common.Behavior.ValidationBehavior`2.Handle(TRequest request, RequestHandlerDelegate`1 next, CancellationToken cancellationToken)
   at OrishaPlatform.Application.Common.Behavior.Cache.CacheBehavior`2.Handle(TRequest request, RequestHandlerDelegate`1 next, CancellationToken cancellationToken)
   at OrishaPlatform.Application.Common.Behavior.Authorize.AuthorizeBehavior`2.Handle(TRequest request, RequestHandlerDelegate`1 next, CancellationToken cancellationToken)
   at OrishaPlatform.UI.PublicApi.Modules.Items.ItemsController.GetItems(List`1 filters, Int32 skip, Int32 take, CancellationToken cancellationToken) in C:\Work\Repositories\OrishaPlatform\src\OrishaPlatform.UI.PublicApi\Modules\Items\ItemsController.cs:line 40
   at lambda_method616(Closure, Object)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   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>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ExceptionContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   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 Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

Verbose output

EF Core version

9.0.7

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

net9.0

Operating system

No response

IDE

No response

[AndriySvyryd]

Unfortunately, we can't investigate this until you are able to provide a small runnable repro project.