.NET Core web API + Dapper – Retries

Recovering from transient failures with Polly

It’s not uncommon for SQL Azure servers and databases to suffer from “transient failures”. In application logs I have seen these occur during scale up/down events, periods where my application’s performance has been temporarily impacted (but its throughput has not changed), which I assume has been some load balancing going on in the background and when network connectivity has been a bit flakey.

Microsoft has published guidance for building Microservices applications, troubleshooting common AzureSQL errors and improving the resilience of ADO.Net connections which cover different approaches in depth.

For many years I used the Microsoft Enterprise Library Transient Fault Handling Application Block (TOPAZ), then upgraded to the .Net Core Version built by Mo Chavoshi both of which have been retired.

Now I’m using The Polly Project which builds on the concepts of TOPAZ but has been thoroughly re-engineered with lots of extensibility, an active community and modern codebase. Inspired by Ben Hyrman and several other developers I have built a minimalist wrapper for the Dapper Async methods which detects transient errors using the same approach as the Entity Framework Core library.

public static Task<int> ExecuteWithRetryAsync(
			  this IDbConnection connection,
			  string sql,
			  object param = null,
			  IDbTransaction transaction = null,
			  int? commandTimeout = null,
			  CommandType? commandType = null) => RetryPolicy.ExecuteAsync(() => connection.ExecuteAsync(sql, param, transaction, commandTimeout, commandType));

I did think about retry functionality for async methods which returned object/dynamic but have only implemented strongly typed ones for the initial version.

[HttpGet]
public async Task<ActionResult<IAsyncEnumerable<Model.StockItemListDtoV1>>> Get()
{
	IEnumerable<Model.StockItemListDtoV1> response = null;

	try
	{
		using (SqlConnection db = new SqlConnection(this.connectionString))
		{
			response = await db.QueryWithRetryAsync<Model.StockItemListDtoV1>(sql: @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM [Warehouse].[StockItems]", commandType: CommandType.Text);
		}
	}
	catch (SqlException ex)
	{
		logger.LogError(ex, "Retrieving list of StockItems");

		return this.StatusCode(StatusCodes.Status500InternalServerError);
	}

	return this.Ok(response);
}

I have struggled to get reproduceable transient failures without pausing execution in the Visual Studio debugger and tinkering with variables or scaling up/down my databases (limit to how often this can be done) or unplugging the network cable at the wrong time.

.NET Core web API + Dapper – History

System Versioned Temporal tables looking up and listing

This StockItemsHistoryController has methods for retrieving a list of StockItems at a point in time specified by an optional query string parameter (if no value is provided the current time is assumed). To show how a temporal query can span multiple tables I included the [Purchasing].[suppliers] table which is also versioned.

http://localhost:36739/api/StockItemsHistory

[HttpGet]
public async Task<ActionResult<IAsyncEnumerable<Model.StockItemsHistoryListDtoV1>>> Get([FromQuery]DateTime? asAt)
{
	IEnumerable<Model.StockItemsHistoryListDtoV1> response = null;

	if (!asAt.HasValue)
	{
		asAt = DateTime.UtcNow;
	}

	try
	{
		using (SqlConnection db = new SqlConnection(this.connectionString))
		{
			response = await db.QueryAsync<Model.StockItemsHistoryListDtoV1>(sql: "[warehouse].[StockItemsHistoryStockItemsListAsAtV1]", param: new { asAt }, commandType: CommandType.StoredProcedure);
		}
	}
	catch (SqlException ex)
	{
		logger.LogError(ex, "Retrieving list of StockItems");

		return this.StatusCode(StatusCodes.Status500InternalServerError);
	}

	return this.Ok(response);
}
ALTER PROCEDURE [Warehouse].[StockItemsHistoryStockItemsListAsAtV1]
		@AsAt DATETIME2(7)
AS
BEGIN
	SELECT [StockItems].[StockItemID] as "ID"
		,[StockItems].[StockItemName] as "Name" 
		,[StockItems].[UnitPrice]
		,[StockItems].[RecommendedRetailPrice] 
		,[StockItems].[TaxRate]
		,[StockItems].[CustomFields]
		,[Suppliers].[SupplierID]
		,[Suppliers].[SupplierName]
	FROM [Warehouse].[StockItems] FOR SYSTEM_TIME AS OF @AsAt as StockItems
		INNER JOIN [Purchasing].[Suppliers] FOR SYSTEM_TIME AS OF @AsAt as Suppliers ON (StockItems.SupplierID = [Suppliers].SupplierID)
END

The query also returns the custom fields (often what was changed in StockItem history), the supplier Id and Supplier name.

The detailed history of a StockItem can be queried to illustrate how the _Archive(history) table works

localhost:36739/api/StockItemsHistory/64/history

ALTER PROCEDURE [Warehouse].[StockItemsHistoryStockItemHistoryListV1]
		@StockItemID int 
AS
BEGIN
	SELECT[StockItems_Archive].[StockItemID] as "ID"
		,[StockItems_Archive].[StockItemName] as "Name"
		,[StockItems_Archive].[UnitPrice]
		,[StockItems_Archive].[RecommendedRetailPrice]
		,[StockItems_Archive].[TaxRate]
		,[StockItems_Archive].[CustomFields]
		,[StockItems_Archive].[ValidFrom]
		,[StockItems_Archive].[ValidTo]
	FROM [Warehouse].[StockItems_Archive]
	WHERE [StockItems_Archive].[StockItemID] = @StockItemId
	ORDER BY [ValidFrom] DESC
END
[HttpGet("{id}/history")]
public async Task<ActionResult<IEnumerable<Model.StockItemHistoryListDtoV1>>> GetHistory([Range(1, int.MaxValue, ErrorMessage = "Stock item id must greater than 0")] int id)
{
    IEnumerable<Model.StockItemHistoryListDtoV1> response = null;

    try
    {
        using (SqlConnection db = new SqlConnection(this.connectionString))
        {
            response = await db.QueryAsync<Model.StockItemHistoryListDtoV1>(sql: "[Warehouse].[StockItemsHistoryStockItemHistoryListV1]", param: new { StockItemID = id }, commandType: CommandType.StoredProcedure);
            if (response == default)
            {
                logger.LogInformation("StockItem:{0} not found", id);

                return this.NotFound($"StockItem:{id} not found");
           }
       }
   }
   catch (SqlException ex)
   {
        logger.LogError(ex, "Retrieving up a StockItem with Id:{0}", id);

        return this.StatusCode(StatusCodes.Status500InternalServerError);
     }

    return this.Ok(response);
}

The state of a StockItem plus the associated Supplier and PackageTypes tables can also be queried at a point in time (if no value is provided the current time is assumed).

http://localhost:36739/api/StockItemsHistory/64?AsAt=2021-06-18T01:21:07.0121476

[HttpGet("{id}")]
public async Task<ActionResult<Model.StockItemGetDtoV1>> Get([Range(1, int.MaxValue, ErrorMessage = "Stock item id must greater than 0")] int id, [FromQuery] DateTime? asAt)
{
	Model.StockItemGetDtoV1 response = null;

	if ( !asAt.HasValue)
	{
		asAt = DateTime.UtcNow; 
	}

	try
	{
		using (SqlConnection db = new SqlConnection(this.connectionString))
		{
			response = await db.QuerySingleOrDefaultAsync<Model.StockItemGetDtoV1>(sql: "[Warehouse].[StockItemsHistoryStockItemLookupAsAtV1]", param: new { asAt, stockItemID=id }, commandType: CommandType.StoredProcedure);
			if (response == default)
			{
				logger.LogInformation("StockItem:{0} not found", id);

				return this.NotFound($"StockItem:{id} not found");
			}
		}
	}
	catch (SqlException ex)
	{
		logger.LogError(ex, "Retrieving StockItem with Id:{0}", id);

		return this.StatusCode(StatusCodes.Status500InternalServerError);
	}

	return this.Ok(response);
}
ALTER PROCEDURE [Warehouse].[StockItemsHistoryStockItemLookupAsAtV1]
		@StockItemID int, 
		@AsAt DATETIME2(7)
AS
BEGIN
	SELECT[StockItem].[StockItemID] as "ID"
		,[StockItem].[StockItemName] as "Name" 
		,[StockItem].[UnitPrice]
		,[StockItem].[RecommendedRetailPrice] 
		,[StockItem].[TaxRate]
		,[StockItem].[typicalWeightPerUnit] 
		,[StockItem].[QuantityPerOuter]
		,[UnitPackage].[PackageTypeName] as "unitPackageName"
		,[OuterPackage].[PackageTypeName] as "outerPackageName"
		,[Supplier].[SupplierID]
		,[Supplier].[SupplierName]
	FROM [Warehouse].[StockItems] FOR SYSTEM_TIME AS OF @AsAt as StockItem
		INNER JOIN[Warehouse].[PackageTypes] FOR SYSTEM_TIME AS OF @AsAt as UnitPackage ON ([StockItem].[UnitPackageID] = [UnitPackage].[PackageTypeID])
		INNER JOIN[Warehouse].[PackageTypes] FOR SYSTEM_TIME AS OF @AsAt as OuterPackage ON ([StockItem].[OuterPackageID] = [OuterPackage].[PackageTypeID])
		INNER JOIN[Purchasing].[Suppliers] FOR SYSTEM_TIME AS OF @AsAt as Supplier ON ([StockItem].SupplierID = Supplier.SupplierID)
		WHERE[StockItem].[StockItemID] = @StockItemId
END

I found it was easy to miss the “FOR SYSTEM_TIME AS OF @AsAt” on the INNER JOINs.

......
| ADD
    {
        <column_definition>
      | <computed_column_definition>
      | <table_constraint>
      | <column_set_definition>
    } [ ,...n ]
      | [ system_start_time_column_name datetime2 GENERATED ALWAYS AS ROW START
                [ HIDDEN ] [ NOT NULL ] [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,
                system_end_time_column_name datetime2 GENERATED ALWAYS AS ROW END
                   [ HIDDEN ] [ NOT NULL ][ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,
                start_transaction_id_column_name bigint GENERATED ALWAYS AS TRANSACTION_ID START
        ]
       PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )
    | 

It is also possible to hide the start and end time columns which might be useful for when retrofitting this approach to a legacy application which uses SELECT * FROM … and might not handle the extra columns correctly.

.NET Core web API + Dapper – Lookup

Looking up and searching

This StockItemsLookupController has methods for looking up a single record using the StockItemID and retrieving a list of records with a name that “matches” the search text. In my initial version the length of the embedded Structured Query Language(SQL) which spanned multiple lines was starting to get out of hand.

ALTER PROCEDURE [Warehouse].[StockItemsStockItemLookupV1]
		@StockItemID as int
AS
BEGIN
	SELECT [StockItems].[StockItemID] as "ID"  
			,[StockItems].[StockItemName] as "Name" 
			,[StockItems].[UnitPrice]
			,[StockItems].[RecommendedRetailPrice] 
			,[StockItems].[TaxRate]
			,[StockItems].[QuantityPerOuter]
			,[StockItems].[TypicalWeightPerUnit]
			,[UnitPackage].[PackageTypeName] as "UnitPackageName"
			,[OuterPackage].[PackageTypeName] as "OuterPackageName"
			,[Supplier].[SupplierID] 
			,[Supplier].[SupplierName] 
	FROM[Warehouse].[StockItems] as StockItems  
	INNER JOIN[Warehouse].[PackageTypes] as UnitPackage ON ([StockItems].[UnitPackageID] = [UnitPackage].[PackageTypeID]) 
	INNER JOIN[Warehouse].[PackageTypes] as OuterPackage ON ([StockItems].[OuterPackageID] = [OuterPackage].[PackageTypeID]) 
	INNER JOIN[Purchasing].[Suppliers] as Supplier ON ([StockItems].SupplierID = [Supplier].]SupplierID])
	WHERE[StockItems].[StockItemID] = @StockItemId
END

The query also returns the inner/outer packaging and the supplier name (plus supplierId for creating a link to the Supplier’s details) to make the example more realistic.

[HttpGet("{id}")]
public async Task<ActionResult<Model.StockItemGetDtoV1>> Get([Range(1, int.MaxValue, ErrorMessage = "Stock item id must greater than 0")] int id)
{
	Model.StockItemGetDtoV1 response = null;

	try
	{
		using (SqlConnection db = new SqlConnection(this.connectionString))
		{
			response = await db.QuerySingleOrDefaultAsync<Model.StockItemGetDtoV1>(sql: "[Warehouse].[StockItemsStockItemLookupV1]", param: new { stockItemId=id }, commandType: CommandType.StoredProcedure);
		}

		if (response == default)
		{
			logger.LogInformation("StockItem:{0} not found", id);

			return this.NotFound($"StockItem:{id} image not found");
		}
	}
	catch (SqlException ex)
	{
		logger.LogError(ex, "Looking up a StockItem with Id:{0}", id);

		return this.StatusCode(StatusCodes.Status500InternalServerError);
	}

	return this.Ok(response);
}

This simple name search also uses the FromQuery attribute (like the pagination example) to populate a Data Transfer Object(DTO) with request query string parameters

[HttpGet]
public async Task<ActionResult<IAsyncEnumerable<Model.StockItemListDtoV1>>> Get([FromQuery] Model.StockItemNameSearchDtoV1 request)
{
	IEnumerable<Model.StockItemListDtoV1> response = null;

	try
	{
		using (SqlConnection db = new SqlConnection(this.connectionString))
		{
			response = await db.QueryAsync<Model.StockItemListDtoV1>(sql: "[Warehouse].[StockItemsNameSearchV1]", param: request, commandType: CommandType.StoredProcedure);
		}
	}
	catch (SqlException ex)
	{
		logger.LogError(ex, "Searching for list of StockItems with name like:{0}", request);

		return this.StatusCode(StatusCodes.Status500InternalServerError);
	}

	return this.Ok(response);
}

The request DTO properties have Data Annotations to ensure the values are valid and suitable error messages are displayed if they are not. The controller GET method will not even be called if the DTO is missing or the values are incorrect. I would use constants for the lengths etc. and the attribute value error messages can be loaded from resource files for multiple language support.

public class StockItemNameSearchDtoV1
{
	[Required]
	[MinLength(3, ErrorMessage = "The name search text must be at least 3 characters long")]
	public string SearchText { get; set; }

	[Required]
	[Range(1, int.MaxValue, ErrorMessage = "MaximumRowsToReturn must be present and greater than 0")]
	public int MaximumRowsToReturn { get; set; }
}

The SELECT TOP command to limit the number of records returned. To improve performance the results of this query could be cached but the result set might need to be filtered based on the current user.

ALTER PROCEDURE [Warehouse].[StockItemsSearchV1]
           @SearchText nvarchar(100),
           @MaximumRowsToReturn int
AS
BEGIN
    SELECT TOP(@MaximumRowsToReturn) [StockItemID] as "ID"
		   ,[StockItemName] as "Name"
		   ,[RecommendedRetailPrice]
		   ,[TaxRate]
    FROM Warehouse.StockItems
    WHERE SearchDetails LIKE N'%' + @SearchText + N'%'
    ORDER BY [StockItemName]
END;

I have used this approach to populate a list of selectable options as a user types their search text.