Azure
[Bug]: Nested entities in GraphQL query may cause System.Collections.Generic.KeyNotFoundException: The given key 'key' was not present in the dictionary.
What happened?
This is the scenario. A SQL database with a Person table. A Person may have 0:M Addresses. Each Address has an AddressType. A Person may have 0:M PhoneNumbers. Each PhoneNumber has a PhoneNumberType.
When querying using GraphQL, we can query for Person and related Addresses and PhoneNumbers. However, we can only add the related AddressType/PhoneNumberType to the first related entity in the query. Retrieving a Person with Addresses and PhoneNumbers as well as the related AddressType and PhoneNumberType entities will fail, nullifying the latter of the two.
Here is a short example that shows the issue.
T-SQL statements to create tables and example data
-- Table to store person data
CREATE TABLE Person (
PersonID INT IDENTITY(1,1) PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50)
);
-- Table to store address types (e.g., Home, Work, etc.)
CREATE TABLE AddressType (
AddressTypeID INT IDENTITY(1,1) PRIMARY KEY,
TypeName NVARCHAR(50) NOT NULL
);
-- Table to store phone number types (e.g., Mobile, Home, Work)
CREATE TABLE PhoneNumberType (
PhoneNumberTypeID INT IDENTITY(1,1) PRIMARY KEY,
TypeName NVARCHAR(50) NOT NULL
);
-- Table to store addresses, which reference a person and an address type
CREATE TABLE Address (
AddressID INT IDENTITY(1,1) PRIMARY KEY,
PersonID INT,
AddressTypeID INT,
StreetAddress NVARCHAR(100),
City NVARCHAR(50),
State NVARCHAR(50),
PostalCode NVARCHAR(10),
CONSTRAINT FK_Person_Address FOREIGN KEY (PersonID) REFERENCES Person(PersonID) ON DELETE CASCADE,
CONSTRAINT FK_AddressType_Address FOREIGN KEY (AddressTypeID) REFERENCES AddressType(AddressTypeID)
);
-- Table to store phone numbers, which reference a person and a phone number type
CREATE TABLE PhoneNumber (
PhoneNumberID INT IDENTITY(1,1) PRIMARY KEY,
PersonID INT,
PhoneNumberTypeID INT,
PhoneNumber NVARCHAR(20),
CONSTRAINT FK_Person_PhoneNumber FOREIGN KEY (PersonID) REFERENCES Person(PersonID) ON DELETE CASCADE,
CONSTRAINT FK_PhoneNumberType_PhoneNumber FOREIGN KEY (PhoneNumberTypeID) REFERENCES PhoneNumberType(PhoneNumberTypeID)
);
-- Insert some sample data into AddressType
INSERT INTO AddressType (TypeName) VALUES ('Home'), ('Work');
-- Insert some sample data into PhoneNumberType
INSERT INTO PhoneNumberType (TypeName) VALUES ('Mobile'), ('Home'), ('Work');
-- Insert two persons
INSERT INTO Person (FirstName, LastName)
VALUES
('John', 'Doe'),
('Jane', 'Smith');
-- Insert addresses for the persons (assuming PersonID 1 for John Doe, 2 for Jane Smith)
INSERT INTO Address (PersonID, AddressTypeID, StreetAddress, City, State, PostalCode)
VALUES
(1, 1, '123 Main St', 'New York', 'NY', '10001'), -- John Doe's Home Address
(1, 2, '456 Work Ave', 'New York', 'NY', '10002'), -- John Doe's Work Address
(2, 1, '789 Elm St', 'Los Angeles', 'CA', '90001'); -- Jane Smith's Home Address
-- Insert phone numbers for the persons
INSERT INTO PhoneNumber (PersonID, PhoneNumberTypeID, PhoneNumber)
VALUES
(1, 1, '123-456-7890'), -- John Doe's Mobile Number
(1, 3, '111-222-3333'), -- John Doe's Work Number
(2, 2, '987-654-3210'); -- Jane Smith's Home Number
dab-config.json
{
"$schema": "https://github.com/Azure/data-api-builder/releases/latest/download/dab.draft.schema.json",
"data-source": {
"database-type": "mssql",
"connection-string": "Server=tcp:localhost,1433;Initial Catalog=dab-test;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Trusted_Connection=True",
"options": {
"set-session-context": false
}
},
"runtime": {
"rest": {
"enabled": true,
"path": "/api",
"request-body-strict": true
},
"graphql": {
"enabled": true,
"path": "/graphql",
"allow-introspection": true
},
"host": {
"cors": {
"origins": [],
"allow-credentials": false
},
"authentication": {
"provider": "Simulator"
},
"mode": "development"
}
},
"entities": {
"PhoneNumberType": {
"source": {
"object": "dbo.PhoneNumberType",
"type": "table"
},
"graphql": {
"enabled": true,
"type": {
"singular": "PhoneNumberType",
"plural": "PhoneNumberTypes"
}
},
"rest": {
"enabled": true
},
"permissions": [
{
"role": "Authenticated",
"actions": [
{
"action": "*"
}
]
}
]
},
"AddressType": {
"source": {
"object": "dbo.AddressType",
"type": "table"
},
"graphql": {
"enabled": true,
"type": {
"singular": "AddressType",
"plural": "AddressTypes"
}
},
"rest": {
"enabled": true
},
"permissions": [
{
"role": "Authenticated",
"actions": [
{
"action": "*"
}
]
}
]
},
"Address": {
"source": {
"object": "dbo.Address",
"type": "table"
},
"graphql": {
"enabled": true,
"type": {
"singular": "Address",
"plural": "Addresses"
}
},
"rest": {
"enabled": true
},
"permissions": [
{
"role": "Authenticated",
"actions": [
{
"action": "*"
}
]
}
],
"relationships": {
"AddressType": {
"cardinality": "one",
"target.entity": "AddressType"
}
}
},
"PhoneNumber": {
"source": {
"object": "dbo.PhoneNumber",
"type": "table"
},
"graphql": {
"enabled": true,
"type": {
"singular": "PhoneNumber",
"plural": "PhoneNumbers"
}
},
"rest": {
"enabled": true
},
"permissions": [
{
"role": "Authenticated",
"actions": [
{
"action": "*"
}
]
}
],
"relationships": {
"PhoneNumberType": {
"cardinality": "one",
"target.entity": "PhoneNumberType"
}
}
},
"Person": {
"source": {
"object": "dbo.Person",
"type": "table",
"key-fields": [
"PersonID"
]
},
"graphql": {
"enabled": true,
"type": {
"singular": "Person",
"plural": "Persons"
}
},
"rest": {
"enabled": true
},
"permissions": [
{
"role": "Authenticated",
"actions": [
{
"action": "*"
}
]
}
],
"relationships": {
"addresses": {
"cardinality": "many",
"target.entity": "Address"
},
"phoneNumbers": {
"cardinality": "many",
"target.entity": "PhoneNumber"
}
}
}
}
}
GraphQL query that will throw an error.
query {
persons {
items {
PersonID
FirstName
LastName
addresses {
items {
AddressID
City
AddressType {
AddressTypeID
TypeName
}
}
}
phoneNumbers {
items {
PhoneNumberID
PhoneNumber
PhoneNumberType {
PhoneNumberTypeID
TypeName
}
}
}
}
}
}
Result from query. Note that PhoneNumberType is null.
{
"errors": [
{
"message": "The given key 'PhoneNumberType' was not present in the dictionary.",
"locations": [
{
"line": 21,
"column": 11
}
],
"path": [
"persons",
"items",
0,
"phoneNumbers",
"items",
1,
"PhoneNumberType"
]
},
{
"message": "The given key 'PhoneNumberType' was not present in the dictionary.",
"locations": [
{
"line": 21,
"column": 11
}
],
"path": [
"persons",
"items",
0,
"phoneNumbers",
"items",
0,
"PhoneNumberType"
]
},
{
"message": "The given key 'PhoneNumberType' was not present in the dictionary.",
"locations": [
{
"line": 21,
"column": 11
}
],
"path": [
"persons",
"items",
1,
"phoneNumbers",
"items",
0,
"PhoneNumberType"
]
}
],
"data": {
"persons": {
"items": [
{
"PersonID": 1,
"FirstName": "John",
"LastName": "Doe",
"addresses": {
"items": [
{
"AddressID": 1,
"City": "New York",
"AddressType": {
"AddressTypeID": 1,
"TypeName": "Home"
}
},
{
"AddressID": 2,
"City": "New York",
"AddressType": {
"AddressTypeID": 2,
"TypeName": "Work"
}
}
]
},
"phoneNumbers": {
"items": [
{
"PhoneNumberID": 1,
"PhoneNumber": "123-456-7890",
"PhoneNumberType": null
},
{
"PhoneNumberID": 2,
"PhoneNumber": "111-222-3333",
"PhoneNumberType": null
}
]
}
},
{
"PersonID": 2,
"FirstName": "Jane",
"LastName": "Smith",
"addresses": {
"items": [
{
"AddressID": 3,
"City": "Los Angeles",
"AddressType": {
"AddressTypeID": 1,
"TypeName": "Home"
}
}
]
},
"phoneNumbers": {
"items": [
{
"PhoneNumberID": 3,
"PhoneNumber": "987-654-3210",
"PhoneNumberType": null
}
]
}
}
]
}
}
}
Removing the AddressType from the query above will still throw an error, unless the entire phoneNumbers is moved above addresses. Then the query will execute successfully. It is still not possible to retrieve both PhoneNumberType and AddressType at the same time. The nested entity always has to be at the top of the query.
Partially working query
query {
persons {
items {
PersonID
FirstName
LastName
phoneNumbers {
items {
PhoneNumberID
PhoneNumber
PhoneNumberType {
PhoneNumberTypeID
TypeName
}
}
}
addresses {
items {
AddressID
City
# Missing the AddressType entity here
}
}
}
}
}
Result from query
{
"data": {
"persons": {
"items": [
{
"PersonID": 1,
"FirstName": "John",
"LastName": "Doe",
"phoneNumbers": {
"items": [
{
"PhoneNumberID": 1,
"PhoneNumber": "123-456-7890",
"PhoneNumberType": {
"PhoneNumberTypeID": 1,
"TypeName": "Mobile"
}
},
{
"PhoneNumberID": 2,
"PhoneNumber": "111-222-3333",
"PhoneNumberType": {
"PhoneNumberTypeID": 3,
"TypeName": "Work"
}
}
]
},
"addresses": {
"items": [
{
"AddressID": 1,
"City": "New York"
},
{
"AddressID": 2,
"City": "New York"
}
]
}
},
{
"PersonID": 2,
"FirstName": "Jane",
"LastName": "Smith",
"phoneNumbers": {
"items": [
{
"PhoneNumberID": 3,
"PhoneNumber": "987-654-3210",
"PhoneNumberType": {
"PhoneNumberTypeID": 2,
"TypeName": "Home"
}
}
]
},
"addresses": {
"items": [
{
"AddressID": 3,
"City": "Los Angeles"
}
]
}
}
]
}
}
}
Version
1.2.10
What database are you using?
Azure SQL
What hosting model are you using?
Local (including CLI), Container Apps
Which API approach are you accessing DAB through?
GraphQL
Relevant log output
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[0]
Executing endpoint 'Hot Chocolate GraphQL Pipeline'
dbug: Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
05349757-8e14-4d94-bb2e-8d4fbc6647a9 Executing query: SELECT TOP 100 [table0].[PersonID] AS [PersonID], [table0].[FirstName] AS [FirstName], [table0].[LastName] AS [LastName], JSON_QUERY (COALESCE([table1_subq].[data], '[]')) AS [addresses], JSON_QUERY (COALESCE([table8_subq].[data], '[]')) AS [phoneNumbers] FROM [dbo].[Person] AS [table0] OUTER APPLY (SELECT TOP 100 [table1].[AddressID] AS [AddressID], [table1].[City] AS [City], JSON_QUERY ([table2_subq].[data]) AS [AddressType] FROM [dbo].[Address] AS [table1] OUTER APPLY (SELECT TOP 1 [table2].[AddressTypeID] AS [AddressTypeID], [table2].[TypeName] AS [TypeName] FROM [dbo].[AddressType] AS [table2] WHERE [table1].[AddressTypeID] = [table2].[AddressTypeID] ORDER BY [table2].[AddressTypeID] ASC FOR JSON PATH, INCLUDE_NULL_VALUES,WITHOUT_ARRAY_WRAPPER) AS [table2_subq]([data]) WHERE [table1].[PersonID] = [table0].[PersonID] ORDER BY [table1].[AddressID] ASC FOR JSON PATH, INCLUDE_NULL_VALUES) AS [table1_subq]([data]) OUTER APPLY (SELECT TOP 100 [table8].[PhoneNumberID] AS [PhoneNumberID], [table8].[PhoneNumber] AS [PhoneNumber], JSON_QUERY ([table9_subq].[data]) AS [PhoneNumberType] FROM [dbo].[PhoneNumber] AS [table8] OUTER APPLY (SELECT TOP 1 [table9].[PhoneNumberTypeID] AS [PhoneNumberTypeID], [table9].[TypeName] AS [TypeName] FROM [dbo].[PhoneNumberType] AS [table9] WHERE [table8].[PhoneNumberTypeID] = [table9].[PhoneNumberTypeID] ORDER BY [table9].[PhoneNumberTypeID] ASC FOR JSON PATH, INCLUDE_NULL_VALUES,WITHOUT_ARRAY_WRAPPER) AS [table9_subq]([data]) WHERE [table8].[PersonID] = [table0].[PersonID] ORDER BY [table8].[PhoneNumberID] ASC FOR JSON PATH, INCLUDE_NULL_VALUES) AS [table8_subq]([data]) WHERE 1 = 1 ORDER BY [table0].[PersonID] ASC FOR JSON PATH, INCLUDE_NULL_VALUES
fail: Azure.DataApiBuilder.Service.Startup[0]
A GraphQL request execution error occurred.
System.Collections.Generic.KeyNotFoundException: The given key 'PhoneNumberType' was not present in the dictionary.
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at Azure.DataApiBuilder.Core.Resolvers.SqlQueryEngine.ResolveObject(JsonElement element, IObjectField fieldSchema, IMetadata& metadata) in /_/src/Core/Resolvers/SqlQueryEngine.cs:line 221
at Azure.DataApiBuilder.Service.Services.ExecutionHelper.ExecuteObjectField(IPureResolverContext context) in /_/src/Core/Services/ExecutionHelper.cs:line 203
at ResolverTypeInterceptor.<>c__DisplayClass5_0.<.ctor>b__3(IPureResolverContext ctx) in /_/src/Core/Services/ResolverTypeInterceptor.cs:line 36
at HotChocolate.Types.Helpers.FieldMiddlewareCompiler.<>c__DisplayClass9_0.<<CreateResolverMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at HotChocolate.AspNetCore.Authorization.AuthorizeMiddleware.InvokeAsync(IDirectiveContext context)
at HotChocolate.Utilities.MiddlewareCompiler`1.ExpressionHelper.AwaitTaskHelper(Task task)
at HotChocolate.Execution.Processing.Tasks.ResolverTask.ExecuteResolverPipelineAsync(CancellationToken cancellationToken)
at HotChocolate.Execution.Processing.Tasks.ResolverTask.TryExecuteAsync(CancellationToken cancellationToken)
Code of Conduct
- [X] I agree to follow this project's Code of Conduct
Hi @marcusrangell thank you for reporting and for including all the repro details. We'll take a look.
I can confirm the same issue and wondering about the current status and if there is any solution in progress? The problem seems to be in Azure.DataApiBuilder.Core.Resolvers. SqlQueryEngine.ResolveObject. The above commit should only be seen as a potential temporary solution to the problem."
Can confirm the issue happening in my local environment, running dab with SWA Cli 1.1.4 and 2.0.1. Error log
fail: Azure.DataApiBuilder.Service.Startup[0]
A GraphQL request execution error occurred.
System.Collections.Generic.KeyNotFoundException: The given key 'behaviours' was not present in the dictionary.
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at Azure.DataApiBuilder.Core.Resolvers.SqlQueryEngine.ResolveObject(JsonElement element, IObjectField fieldSchema, IMetadata& metadata) in /_/src/Core/Resolvers/SqlQueryEngine.cs:line 232
at Azure.DataApiBuilder.Service.Services.ExecutionHelper.ExecuteObjectField(IPureResolverContext context) in /_/src/Core/Services/ExecutionHelper.cs:line 207
at HotChocolate.Resolvers.FieldResolverDelegates.<>c__DisplayClass0_0.<.ctor>b__0(IResolverContext context)
at HotChocolate.Types.Helpers.FieldMiddlewareCompiler.<>c__DisplayClass9_0.<<CreateResolverMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at HotChocolate.AspNetCore.Authorization.AuthorizeMiddleware.InvokeAsync(IDirectiveContext context)
at HotChocolate.Utilities.MiddlewareCompiler`1.ExpressionHelper.AwaitTaskHelper(Task task)
at HotChocolate.Execution.Processing.Tasks.ResolverTask.ExecuteResolverPipelineAsync(CancellationToken cancellationToken)
at HotChocolate.Execution.Processing.Tasks.ResolverTask.TryExecuteAsync(CancellationToken cancellationToken)
The issue occurred when introducing RBAC and sending X-MS-API-ROLE header
From the example, if authenticated is changed to anonymous for all entities in dab-config.json it will work. So, it looks like the problem is related to permission handling.
"permissions": [
{
"role": " anonymous",
"actions": [
{
"action": "*"
}
]
}
]
Hi @marcusrangell thank you for reporting and for including all the repro details. We'll take a look.
@seantleonard Any updates on this? This is preventing us applying RBAC security principles
I'm running into the same issue. Fortunately for us we are still in dev but this will be critical soon. We can work around it in dev by removing RBAC from the entities and setting * for anonymous like the example above by @SX-Johan-Karlsson
Ditto. Running into the same issue. We finally decided to upgrade from DAB 0.10.23. ANY version higher than this results in this bug for me. So I can't upgrade...
Ditto. We are also running into the same issue. Can't move to Prod, with this issue still pending....
Can you try this Query once, moving at that level works, but not sure if we have multiple of these case how will it react then.
query {
persons {
items {
PersonID
FirstName
LastName
phoneNumbers {
items {
PhoneNumberID
PhoneNumber
PhoneNumberType {
PhoneNumberTypeID
TypeName
}
}
}
addresses {
items {
AddressID
City
AddressType {
AddressTypeID
TypeName
}
}
}
}
}
}
In case anyone is in an urgent rush like I was, we moved to applying security principals at the database level instead
https://learn.microsoft.com/en-us/azure/data-api-builder/how-to-row-level-security
Let me restate.
Related #2623
The core scenario:
You have this data model:
-
Person- 0:M
Addresses(each with anAddressType) - 0:M
PhoneNumbers(each with aPhoneNumberType)
- 0:M
You want to query all of this in a single GraphQL query:
query {
persons {
items {
FirstName
LastName
phoneNumbers {
items {
PhoneNumber
PhoneNumberType {
TypeName
}
}
}
addresses {
items {
City
AddressType {
TypeName
}
}
}
}
}
}
The issue:
-
Only one nested navigation object resolves correctly.
- If
PhoneNumberTypeis queried first,AddressTypereturnsnull. - If
AddressTypeis queried first,PhoneNumberTypereturnsnull.
- If
-
Reordering fields changes the result, revealing internal state conflicts.
-
The DAB runtime throws:
System.Collections.Generic.KeyNotFoundException: The given key 'PhoneNumberType' was not present in the dictionary.
Likely cause:
ResolveObject in SqlQueryEngine uses a shared dictionary to map nested results, but that dictionary is incorrectly scoped or overwritten when two sibling navigation properties contain their own nested lookups. This causes the second one to fail when DAB tries to resolve the field by key.
RBAC makes it worse:
-
When RBAC is enabled, the error is more common and persistent.
-
Switching to:
"permissions": [ { "role": "anonymous", "actions": [{ "action": "*" }] } ]resolves the issue temporarily, implying that per-role metadata resolution fails to merge nested relationships correctly.
Current workarounds:
- Remove RBAC from affected entities (not viable for prod).
- Flatten your schema using views (e.g., materialize relationships).
- Query one nested structure at a time (e.g., fetch phoneNumbers with types, then addresses with types).
- Apply RBAC at the database level using row-level security (RLS), as noted [here](https://learn.microsoft.com/en-us/azure/data-api-builder/how-to-row-level-security).
Impact:
- It prevents multi-relationship object graphs with navigation joins from working in GraphQL.
- The issue breaks expectations around how GraphQL should behave in normalized schemas.
Suggestions:
- Fix how
ResolveObjectstores intermediate lookups during GraphQL result construction. - Scope dictionaries per field tree path to avoid key conflicts.
- Add test cases covering multiple navigation branches with nested joins under RBAC and non-RBAC conditions.
