Enhanced Update (patch) query

From: Davide:

https://github.com/Azure/data-api-builder/blob/90e1bb077986a354c64d7b8013bc484ae5d7d2a8/src/Core/Resolvers/SqlMutationEngine.cs#L133
Implementing an Implicit Transaction using Transaction Scope - .NET Framework | Microsoft Learn

 DECLARE @ROWS_TO_UPDATE int;

 SET @ROWS_TO_UPDATE = (SELECT COUNT(*) as cnt_rows_to_update FROM [dbo].[todo] WHERE [dbo].[todo].[id] = @param0);
 SELECT COUNT(*) as cnt_rows_to_update FROM [dbo].[todo] WHERE [dbo].[todo].[id] = @param0; 

 IF @ROWS_TO_UPDATE = 1 BEGIN

 UPDATE [dbo].[todo] SET [dbo].[todo].[position] = @param1, [dbo].[todo].[owner_id] = @param2

 OUTPUT Inserted.[id] AS [id], Inserted.[title] AS [title], Inserted.[completed] AS [completed], Inserted.[owner_id] AS [owner_id], Inserted.[position] AS [order]

 WHERE [dbo].[todo].[id] = @param0;

 END

Which I think can be improved in two ways:

There are two SELECT COUNT(*) operations done. The second could just be a SELECT @ROWS_TO_UPDATE, right?
I think I have already mentioned this but let’s make sure the whole operation is within a transaction with a SERIALIZABLE transaction level as otherwise it is not guaranteed that the set of data on which the UPDATE operates is the same of the one that was seen by the SELECT COUNT. Even better, I think, we could just use the READ COMMITTED transaction level the default) and then use an UPDLOCK hint:

SET XACT_ABORT ON

BEGIN TRAN

DECLARE @ROWS_TO_UPDATE int;

SET @ROWS_TO_UPDATE = (SELECT COUNT(*) as cnt_rows_to_update FROM [dbo].[todo] WITH (UPDLOCK) WHERE [dbo].[todo].[id] = @param0);

SELECT @ROWS_TO_UPDATE AS cnt_rows_to_update;

IF @ROWS_TO_UPDATE = 1 BEGIN
 UPDATE [dbo].[todo] SET [dbo].[todo].[position] = @param1, [dbo].[todo].[owner_id] = @param2

 OUTPUT Inserted.[id] AS [id], Inserted.[title] AS [title], Inserted.[completed] AS [completed], Inserted.[owner_id] AS [owner_id], Inserted.[position] AS [order]

 WHERE [dbo].[todo].[id] = @param0;
END
COMMIT TRAN

リンク先の行にある src/Core/Resolvers/SqlMutationEngine.cs から始め、更新ミューテーション用の SQL がどのように組み立てられているかを追跡してください。ここに示されている生成済み SQL と照らし合わせて、提案されている count query とトランザクションの動作を確認してください。更新操作が意図した行数とトランザクションセマンティクスを一貫して使用し、既存のミューテーションテストが適用される箇所にカバレッジが追加されていれば完了です。