[Bug]: 500 InvalidOperationException on cached REST read when view/entity returns empty result

[ehalsey]

What happens

A REST read on an entity that has caching enabled returns HTTP 500 UnexpectedError whenever the underlying query returns zero rows. The SQL executes successfully — the failure is in DAB's cache serialization path, where a JsonElement is written after its backing JsonDocument has been disposed.

Inserting a row makes the endpoint return 200 again; deleting all rows brings the 500 back. Disabling cache on the entity also avoids it.

Environment

• DAB versions reproduced on: 1.7.83-rc and 1.7.92 (latest stable, 2026-03-27)
• Backend: SQL Server 2022, Linux container (mcr.microsoft.com/mssql/server:2022-latest)
• Deployment: mcr.microsoft.com/azure-databases/data-api-builder:1.7.92 in Docker Compose
• Entity source: a view (dbo.v_Estimates) — same class of view-backed entities that work fine when they contain data.

Stack trace (from DAB 1.7.92 on empty view)

fail: Azure.DataApiBuilder.Service.Controllers.RestController[0]
      ... Internal server error occured during REST request processing.
      System.InvalidOperationException: Operation is not valid due to the current state of the object.
         at System.Text.Json.JsonElement.CheckValidInstance()
         at System.Text.Json.JsonElement.WriteTo(Utf8JsonWriter writer)
         at System.Text.Json.Serialization.Converters.JsonElementConverter.Write(Utf8JsonWriter writer, JsonElement value, JsonSerializerOptions options)
         at System.Text.Json.Serialization.Converters.NullableConverter`1.Write(Utf8JsonWriter writer, Nullable`1 value, JsonSerializerOptions options)
         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.Serialize(Utf8JsonWriter writer, T& rootValue, Object rootValueBoxed)
         at System.Text.Json.JsonSerializer.WriteBytes[TValue](TValue& value, JsonTypeInfo`1 jsonTypeInfo)
         at System.Text.Json.JsonSerializer.SerializeToUtf8Bytes[TValue](TValue value, JsonSerializerOptions options)
         at Azure.DataApiBuilder.Core.Resolvers.SqlQueryEngine.ParseResultIntoJsonDocument(Nullable`1 result) in /_/src/Core/Resolvers/SqlQueryEngine.cs:line 446
         at Azure.DataApiBuilder.Core.Resolvers.SqlQueryEngine.GetResultInCacheScenario(RuntimeConfig runtimeConfig, SqlQueryStructure structure, String queryString, String dataSourceName, IQueryExecutor queryExecutor, EntityCacheLevel cacheEntryLevel) in /_/src/Core/Resolvers/SqlQueryEngine.cs:line 440
         at Azure.DataApiBuilder.Core.Resolvers.SqlQueryEngine.ExecuteAsync(SqlQueryStructure structure, String dataSourceName, Boolean isMultipleCreateOperation) in /_/src/Core/Resolvers/SqlQueryEngine.cs:line 339
         at Azure.DataApiBuilder.Core.Resolvers.SqlQueryEngine.ExecuteAsync(FindRequestContext context) in /_/src/Core/Resolvers/SqlQueryEngine.cs:line 192
         at Azure.DataApiBuilder.Core.Services.RestService.DispatchQuery(RestRequestContext context, DatabaseType databaseType) in /_/src/Core/Services/RestService.cs:line 220
         at Azure.DataApiBuilder.Core.Services.RestService.ExecuteAsync(String entityName, EntityActionOperation operationType, String primaryKeyRoute) in /_/src/Core/Services/RestService.cs:line 195
         at Azure.DataApiBuilder.Service.Controllers.RestController.HandleOperation(String route, EntityActionOperation operationType) in /_/src/Service/Controllers/RestController.cs:line 239

JsonElement.CheckValidInstance() throws because the JsonDocument backing the cached element was disposed before ParseResultIntoJsonDocument serialized it.

Suspected code path

src/Core/Resolvers/SqlQueryEngine.cs (today on main):

default:
    result = await _cache.GetOrSetAsync<JsonElement>(
        queryExecutor,
        queryMetadata,
        cacheEntryTtl: runtimeConfig.GetEntityCacheEntryTtl(entityName: structure.EntityName),
        cacheEntryLevel);
    return ParseResultIntoJsonDocument(result);

private static JsonDocument? ParseResultIntoJsonDocument(JsonElement? result)
{
    byte[] jsonBytes = JsonSerializer.SerializeToUtf8Bytes(result);
    return JsonDocument.Parse(jsonBytes);
}

The cache stores/returns a JsonElement, but JsonElement is just a cheap handle into a JsonDocument. If the producing JsonDocument is disposed (or was never materialized because the SQL returned no rows and the "empty" JsonElement is default(JsonElement)), serializing it later via SerializeToUtf8Bytes throws InvalidOperationException.

Empty results are the deterministic trigger here; non-empty results survive because the JsonDocument has real bytes that presumably live long enough in the cache.

Related-looking prior fix (same shape of bug for a different code path): #2865 "Fix Stored Procedure execution by ensuring JsonDocuments are cleaned up after request" shipped in 1.7.90, but does not cover the empty-read cache path.

Minimal repro

1. Set up any SQL-backed entity (table or view) with caching enabled:

   "myentity": {
     "source": { "object": "dbo.MyView", "type": "view", "key-fields": ["Id"] },
     "permissions": [{ "role": "anonymous", "actions": ["read"] }],
     "cache": { "enabled": true, "ttl-seconds": 5 }
   }

2. Ensure the view/table returns zero rows.
3. GET /api/myentity → HTTP 500 UnexpectedError.
4. Insert any row into the underlying table and repeat → HTTP 200 with data.
5. Delete all rows and repeat → back to HTTP 500.

Expected behavior

GET /api/myentity should return {"value":[]} with HTTP 200 regardless of whether the cache is enabled or disabled, and regardless of whether the underlying query is empty.

Workaround

Disable caching on the affected entity:

"cache": { "enabled": false }

Non-empty cached entities in the same deployment keep working normally, so teams often won't notice until a specific entity drains to zero rows (new install, empty tenant, all data archived, etc.).

Suggested fix directions

• In ParseResultIntoJsonDocument, guard against default(JsonElement) / disposed values and return null (or an empty JSON array) on cache miss + empty result.
• Or materialize the cached value into owned bytes (e.g. cache byte[] / a cloned JsonDocument) rather than a JsonElement whose lifetime is tied to the original JsonDocument.
• Or ensure the JsonDocument produced on an empty result stays alive for the duration of the cache entry's TTL.

Happy to test a fix build against our repro if helpful.