[Bug]: 500 `InvalidOperationException` on cached REST read when result is empty

[JerryNixon]

Environment

• DAB: Microsoft.DataApiBuilder 2.0.9+17ae3aa8414514918b627c777026efbc135a57e9
• Database: Azure SQL (mssql)
• API: REST, host.mode: development, anonymous read
• Also reported failing on: 1.7.92, 2.0.8 (a commenter notes 1.7.93 worked)

Summary

When entity/runtime caching is enabled and a REST read returns zero rows, DAB returns HTTP 500 with System.InvalidOperationException during response serialization. With caching disabled, the identical requests correctly return 200 {"value":[]}. The failure is in the cache code path, not the query itself.

Repro

Minimal config — caching enabled globally and on the entity:

{
  "runtime": {
    "rest": { "enabled": true, "path": "/api" },
    "cache": { "enabled": true, "ttl-seconds": 5 }
  },
  "entities": {
    "Todo": {
      "source": { "object": "dbo.Todos", "type": "table" },
      "rest": { "enabled": true },
      "cache": { "enabled": true, "ttl-seconds": 5 },
      "permissions": [ { "role": "anonymous", "actions": [ { "action": "read" } ] } ]
    }
  }
}

Requests that produce an empty result set:

GET /api/Todo?$filter=Id eq -99999     # list read, zero matches
GET /api/Todo/Id/-99999                # by-PK read, no row

Both return HTTP 500. (An empty base table / a view that returns no rows reproduces identically.)

Expected

200 with an empty payload, matching the non-cached behavior:

{ "value": [] }

Actual

500 with:

{ "error": { "code": "UnexpectedError", "message": "While processing your request the server ran into an unexpected error.", "status": 500 } }

Server-side exception:

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(...)
   at System.Text.Json.Serialization.Converters.NullableConverter`1.Write(...)
   ...
   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(...) in /_/src/Core/Services/RestService.cs:line 238
   ...
   at Azure.DataApiBuilder.Service.Controllers.RestController.HandleOperation(...) in /_/src/Service/Controllers/RestController.cs:line 278

Root cause

The failure is in the cache read path: SqlQueryEngine.GetResultInCacheScenario → SqlQueryEngine.ParseResultIntoJsonDocument(JsonElement? result) at src/Core/Resolvers/SqlQueryEngine.cs:440–446.

When the DB returns no rows, the result value is a default/Undefined JsonElement? (no backing JsonDocument). ParseResultIntoJsonDocument then calls JsonSerializer.SerializeToUtf8Bytes(...) on it, which invokes JsonElement.WriteTo → CheckValidInstance(), and that throws InvalidOperationException because the JsonElement has no valid backing document.

The non-cached path does not hit this: it detects the empty result (IQueryExecutor logs Did not return any rows in the JSON result.) and returns an empty value collection. The cache scenario is missing that empty/null guard before serialization.

Notes from instrumentation (FusionCache debug logs):

• The exception fires on both cache miss (first request) and cache hit, because GetResultInCacheScenario is used whenever caching is enabled — the empty value is even serialized/stored and then fails again on read-back.
• Applies to both list (FOR JSON PATH) and by-PK (FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) reads.

Control (caching disabled)

Same two requests plus a populated control, cache off:

GET /api/Todo?$filter=Id eq -99999  -> 200 {"value":[]}
GET /api/Todo/Id/-99999             -> 200 {"value":[]}
GET /api/Todo                       -> 200 {"value":[ ...rows... ]}

Server log for the empty cases: Did not return any rows in the JSON result. then HTTP ... 200. This isolates the defect to the caching path.

Suggested fix direction

In GetResultInCacheScenario / ParseResultIntoJsonDocument, guard the null/empty (JsonValueKind.Undefined) case before SerializeToUtf8Bytes, returning the same empty result the non-cached path produces (empty array for list, no-row/404-style handling for by-PK). A regression test with caching enabled against a zero-row read (both list and PK) would cover this.

[simonsabin]

This applies to GraphQL as well