[Bug]: Getting error Cannot perform INSERT and could not find "SomeEntity" with primary key "PK" to perform UPDATE on

[yorek]

What happened?

If I try to use PUT or PATCH on a table like the following:

drop table if exists dbo.some_entity;
create table dbo.some_entity
(
    id int not null identity primary key,
    some_string varchar(50) not null,
    some_other_string varchar(50) null,
    some_number int not null default (42),
)

and I use the PUT or PATCH to try to create a new item, for example:

PUT https://localhost:5001/api/some-entities/id/3
Content-Type: application/json

{  
  "some_string": "Just another test",
  "some_number": 357
}

I get the following error:

{ "error": { "code": "EntityNotFound", "message": "Cannot perform INSERT and could not find SomeEntity with primary key \u003Cid: 12\u003E to perform UPDATE on.", "status": 404 } }

The entity configuration is the following:

"SomeEntity": {
      "source": {
        "object": "dbo.some_entity",
        "type": "table"
      },
      "rest": {
        "path": "some-entities"
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [ "*" ]
        }
      ]
    }

Version

Microsoft.DataApiBuilder 0.9.5-rc+73a02dcd3b85a73ac36a5675432a45b7a3cc4a7d

What database are you using?

Azure SQL

What hosting model are you using?

Local (including CLI)

Which API approach are you accessing DAB through?

REST

Relevant log output

Request starting HTTP/1.1 PUT https://localhost:5001/api/some-entities/id/3 application/json 67
dbug: Azure.DataApiBuilder.Core.AuthenticationHelpers.ClientRoleHeaderAuthenticationMiddleware[0]
      95a22bc4-c7f6-4cc1-9f7a-44d056c31500 Request authentication state: Authenticated.
dbug: Azure.DataApiBuilder.Core.AuthenticationHelpers.ClientRoleHeaderAuthenticationMiddleware[0]
      95a22bc4-c7f6-4cc1-9f7a-44d056c31500 The request will be executed in the context of the role: Authenticated
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[0]
      Executing endpoint 'Azure.DataApiBuilder.Service.Controllers.RestController.Upsert (Azure.DataApiBuilder.Service)'
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[3]
      Route matched with {action = "Upsert", controller = "Rest"}. Executing controller action with signature System.Threading.Tasks.Task`1[Microsoft.AspNetCore.Mvc.IActionResult] Upsert(System.String) on controller Azure.DataApiBuilder.Service.Controllers.RestController (Azure.DataApiBuilder.Service).     
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[1]
      Executing action method Azure.DataApiBuilder.Service.Controllers.RestController.Upsert (Azure.DataApiBuilder.Service) - Validation state: Valid     
dbug: Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
      95a22bc4-c7f6-4cc1-9f7a-44d056c31500 Executing query: DECLARE @ROWS_TO_UPDATE int;SET @ROWS_TO_UPDATE = (SELECT COUNT(*) as cnt_rows_to_update FROM [dbo].[some_entity] WHERE [dbo].[some_entity].[id] = @param0); SELECT COUNT(*) as cnt_rows_to_update FROM [dbo].[some_entity] WHERE [dbo].[some_entity].[id] = @param0;IF @ROWS_TO_UPDATE = 1 BEGIN UPDATE [dbo].[some_entity] SET [dbo].[some_entity].[some_string] = @param1, [dbo].[some_entity].[some_number] = @param2, [dbo].[some_entity].[some_other_string] = @param3 OUTPUT Inserted.[id] AS [id], Inserted.[some_string] AS [some_string], Inserted.[some_other_string] AS [some_other_string], Inserted.[some_number] AS [some_number] WHERE [dbo].[some_entity].[id] = @param0;END
fail: Azure.DataApiBuilder.Service.Controllers.RestController[0]
      95a22bc4-c7f6-4cc1-9f7a-44d056c31500 Error handling REST request.
      Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Cannot perform INSERT and could not find SomeEntity with primary key <id: 3> to perform UPDATE on.
         at Azure.DataApiBuilder.Core.Resolvers.MsSqlQueryExecutor.GetMultipleResultSetsIfAnyAsync(DbDataReader dbDataReader, List`1 args)
         at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAgainstDbAsync[TResult](TConnection conn, String sqltext, IDictionary`2 parameters, Func`3 dataReaderHandler, HttpContext httpContext, List`1 args)
         at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.<>c__DisplayClass19_0`1.<<ExecuteQueryAsync>b__0>d.MoveNext()
      --- End of stack trace from previous location ---
         at Polly.Retry.AsyncRetryEngine.ImplementationAsync[TResult](Func`3 action, Context context, CancellationToken cancellationToken, ExceptionPredicates shouldRetryExceptionPredicates, ResultPredicates`1 shouldRetryResultPredicates, Func`5 onRetryAsync, Int32 permittedRetryCount, IEnumerable`1 sleepDurationsEnumerable, Func`4 sleepDurationProvider, Boolean continueOnCapturedContext)
         at Polly.AsyncPolicy.ExecuteAsync[TResult](Func`3 action, Context context, CancellationToken cancellationToken, Boolean continueOnCapturedContext)
         at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAsync[TResult](String sqltext, IDictionary`2 parameters, Func`3 dataReaderHandler, HttpContext httpContext, List`1 args, String dataSourceName)
         at Azure.DataApiBuilder.Core.Resolvers.SqlMutationEngine.PerformUpsertOperation(IDictionary`2 parameters, RestRequestContext context)
         at Azure.DataApiBuilder.Core.Resolvers.SqlMutationEngine.ExecuteAsync(RestRequestContext context)
         at Azure.DataApiBuilder.Core.Services.RestService.ExecuteAsync(String entityName, EntityActionOperation operationType, String primaryKeyRoute)   
         at Azure.DataApiBuilder.Service.Controllers.RestController.HandleOperation(String route, EntityActionOperation operationType)
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[2]
      Executed action method Azure.DataApiBuilder.Service.Controllers.RestController.Upsert (Azure.DataApiBuilder.Service), returned result Microsoft.AspNetCore.Mvc.JsonResult in 87.2955ms.
info: Microsoft.AspNetCore.Mvc.Infrastructure.SystemTextJsonResultExecutor[1]
      Executing JsonResult, writing value of type '<>f__AnonymousType0`1[[<>f__AnonymousType1`3[[System.String, System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.String, System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.Int32, System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]], Azure.DataApiBuilder.Service, Version=0.9.5.0, Culture=neutral, PublicKeyToken=null]]'.
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[2]
      Executed action Azure.DataApiBuilder.Service.Controllers.RestController.Upsert (Azure.DataApiBuilder.Service) in 120.2469ms
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[1]
      Executed endpoint 'Azure.DataApiBuilder.Service.Controllers.RestController.Upsert (Azure.DataApiBuilder.Service)'
info: Microsoft.AspNetCore.Hosting.Diagnostics[2]
      Request finished HTTP/1.1 PUT https://localhost:5001/api/some-entities/id/3 application/json 67 - 404 - application/json;+charset=utf-8 176.8940ms

Code of Conduct

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

[seantleonard]

This looks like improved error message to indicate the limitation. You're trying to PUT/PATCH into a table with an auto-generated primary key (indicated by identity per (adding this doc for others) https://learn.microsoft.com/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver16)

improved error would be to remove stack trace and report explicitly:

"No record to update. Can't insert new record in table with autogenerated primary key when the request includes an explicit primary key value"