[Bug]: Nested entities in GraphQL query may cause System.Collections.Generic.KeyNotFoundException: The given key 'key' was not present in the dictionary.

[marcusrangell]

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

• I agree to follow this project's Code of Conduct

[seantleonard]

Hi @marcusrangell thank you for reporting and for including all the repro details. We'll take a look.

[SX-Johan-Karlsson]

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

[davormaricdev]

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

[SX-Johan-Karlsson]

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": "*"
            }
          ]
        }
      ]

[davormaricdev]

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

[ehalsey]

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

[hgrimesncf]

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

[julianadormon]

Ditto. Same I cannot upgrade past DAB 0.10.23

[harsh3105]

Ditto. We are also running into the same issue. Can't move to Prod, with this issue still pending....

[harsh3105]

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
          }
        }
      }
    }
  }
}

[julianadormon]

Is anyone on the DAB team going to at least look at this?