.NET Core web API + Dapper – Redis Cache

The IDistributedCache has Memory, SQL Server and Redis implementations so I wanted to explore how the Stack Exchange Redis library works. The ConnectionMultiplexer class in the Stack Exchange Redis library hides the details of managing connections to multiple Redis servers, connection timeouts etc. The object is fairly “chunky” so it should be initialized once and reused for the lifetime of the program.

public static void Main(string[] args)
{
    var builder = WebApplication.CreateBuilder(args);

    // Add services to the container.
    builder.Services.AddApplicationInsightsTelemetry();

    // Add services to the container.
    builder.Services.AddTransient<IDapperContext>(s => new DapperContext(builder.Configuration));

    builder.Services.AddControllers();

    builder.Services.AddSingleton<IConnectionMultiplexer>(s => ConnectionMultiplexer.Connect(builder.Configuration.GetConnectionString("Redis")));

    var app = builder.Build();

    // Configure the HTTP request pipeline.
    app.UseHttpsRedirection();
    app.MapControllers();

    app.Run();
}

I trialed the initial versions of my Redis project with Memurai on my development machine, then configured an Azure Cache for Redis. I then load tested the project with several Azure AppService client and there was a significant improvement in response time.

[ApiController]
[Route("api/[controller]")]
public class StockItemsController : ControllerBase
{
    private const int StockItemSearchMaximumRowsToReturn = 15;
    private readonly TimeSpan StockItemListExpiration = new TimeSpan(0, 5, 0);

    private const string sqlCommandText = @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM [Warehouse].[StockItems]";
    //private const string sqlCommandText = @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM [Warehouse].[StockItems]; WAITFOR DELAY '00:00:02'";

    private readonly ILogger<StockItemsController> logger;
    private readonly IDbConnection dbConnection;
    private readonly IDatabase redisCache;

    public StockItemsController(ILogger<StockItemsController> logger, IDapperContext dapperContext, IConnectionMultiplexer connectionMultiplexer)
    {
        this.logger = logger;
        this.dbConnection = dapperContext.ConnectionCreate();
        this.redisCache = connectionMultiplexer.GetDatabase();
    }

        [HttpGet]
    public async Task<ActionResult<IEnumerable<Model.StockItemListDtoV1>>> Get()
    {
        var cached = await redisCache.StringGetAsync("StockItems");
        if (cached.HasValue)
        {
            return Content(cached, "application/json");
        }

        var stockItems = await dbConnection.QueryWithRetryAsync<Model.StockItemListDtoV1>(sql: sqlCommandText, commandType: CommandType.Text);

#if SERIALISER_SOURCE_GENERATION
        string json = JsonSerializer.Serialize(stockItems, typeof(List<Model.StockItemListDtoV1>), Model.StockItemListDtoV1GenerationContext.Default);
#else
        string json = JsonSerializer.Serialize(stockItems);
#endif

        await redisCache.StringSetAsync("StockItems", json, expiry: StockItemListExpiration);

        return Content(json, "application/json");
    }

...

    [HttpDelete()]
    public async Task<ActionResult> ListCacheDelete()
    {
        await redisCache.KeyDeleteAsync("StockItems");

        logger.LogInformation("StockItems list removed");

        return this.Ok();
    }
}

Like Regular Expressions in .NET, the System.Test.Json object serialisations can be compiled to MSIL code instead of high-level internal instructions. This allows .NET’s just-in-time (JIT) compiler to convert the serialisation to native machine code for higher performance.

public class StockItemListDtoV1
{
    public int Id { get; set; }

    public string Name { get; set; }

    public decimal RecommendedRetailPrice { get; set; }

    public decimal TaxRate { get; set; }
}

[JsonSourceGenerationOptions(PropertyNamingPolicy = JsonKnownNamingPolicy.CamelCase)]
[JsonSerializable(typeof(List<StockItemListDtoV1>))]
public partial class StockItemListDtoV1GenerationContext : JsonSerializerContext
{
}

The cost of constructing the Serialiser may be higher, but the cost of performing serialisation with it is much smaller.

[HttpGet]
public async Task<ActionResult<IEnumerable<Model.StockItemListDtoV1>>> Get()
{
    var cached = await redisCache.StringGetAsync("StockItems");
    if (cached.HasValue)
    {
        return Content(cached, "application/json");
    }

    var stockItems = await dbConnection.QueryWithRetryAsync<Model.StockItemListDtoV1>(sql: sqlCommandText, commandType: CommandType.Text);

#if SERIALISER_SOURCE_GENERATION
    string json = JsonSerializer.Serialize(stockItems, typeof(List<Model.StockItemListDtoV1>), Model.StockItemListDtoV1GenerationContext.Default);
#else
    string json = JsonSerializer.Serialize(stockItems);
#endif

    await redisCache.StringSetAsync("StockItems", json, expiry: StockItemListExpiration);

    return Content(json, "application/json");
}

I used Telerik Fiddler to empty the cache then load the StockItems list 10 times (more tests would improve the quality of the results). The first trial was with the “conventional” serialiser

The average time for the conventional serialiser was 0.028562 seconds

The average time for the generated version was 0.030546 seconds. But, if the initial compilation step was ignored the average duration dropped to 0.000223 seconds a significant improvement.

Myriota – Uplink Payload formatters and caching

My myriota Azure IoT Hub Cloud Identity Translation Gateway payload formatters uses C# code (compiled with CS-Script cached with Alastair Crabtrees’s LazyCache) to convert uplink packet payloads to JSON.

I have found that putting the C/C++ structure for the uplink payload at the top of the convertor really helpful.

/*
myriota tracker payload format

typedef struct {
  uint16_t sequence_number;
  int32_t latitude;   // scaled by 1e7, e.g. -891234567 (south 89.1234567)
  int32_t longitude;  // scaled by 1e7, e.g. 1791234567 (east 179.1234567)
  uint32_t time;      // epoch timestamp of last fix
} __attribute__((packed)) tracker_message; 

*/ 
using System;
using System.Collections.Generic;

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;


public class FormatterUplink : PayloadFormatter.IFormatterUplink
{
    public JObject Evaluate(IDictionary<string, string> properties, string application, string terminalId, DateTime timestamp, JObject payloadJson, string payloadText, byte[] payloadBytes)
    {
        JObject telemetryEvent = new JObject();

        telemetryEvent.Add("SequenceNumber", BitConverter.ToUInt16(payloadBytes));

        double latitude = BitConverter.ToInt32(payloadBytes, 2) / 10000000.0;
        telemetryEvent.Add("Latitude", latitude);

        double longitude = BitConverter.ToInt32(payloadBytes, 6) / 10000000.0;
        telemetryEvent.Add("Longitude", longitude);

        UInt32 packetimestamp = BitConverter.ToUInt32(payloadBytes, 10);
        DateTime lastFix = DateTime.UnixEpoch.AddSeconds(packetimestamp);

       properties.Add("iothub-creation-time-utc", lastFix .ToString("s", CultureInfo.InvariantCulture));

       return telemetryEvent;
    }
}

The sample Tracker.cs payload formatter unpacks a message from Myriota Dev Kit running the Tracker sample and returns an Azure IoT Central compatible location telemetry payload.

BEWARE : I think the Azure IoT Central Position lat, lon + alt values might be case sensitive.

Azure IoT Explorer displaying Tracker.cs payload formatter output

The identity payload formatter to use is configured as part of the Destination webhook Uniform Resource Locator (URL).

Myriota Destination configuration application name URL configuration
namespace devMobile.IoT.MyriotaAzureIoTConnector.Connector.Models
{
    public class UplinkPayloadQueueDto
    {
        public string Application { get; set; }
        public string EndpointRef { get; set; }
        public DateTime PayloadReceivedAtUtc { get; set; }
        public DateTime PayloadArrivedAtUtc { get; set; }
        public QueueData Data { get; set; }
        public string Id { get; set; }
        public Uri CertificateUrl { get; set; }
        public string Signature { get; set; }
    }

    public class QueueData
    {
        public List<QueuePacket> Packets { get; set; }
    }

    public class QueuePacket
    {
        public string TerminalId { get; set; }

        public DateTime Timestamp { get; set; }

        public string Value { get; set; }
    }
}

A pair of Azure Blob Storage containers are used to store the uplink/downlink (coming soon) formatter files. The compiled payload formatters are cached with Uplink/Downlink + Application (from the UplinkPayloadQueueDto) as the key.

Azure IoT Storage Explorer uplink payload formatters

The default uplink and downlink formatters used when there is no payload formatter for “Application” are configured in the application settings.

Myriota device Uplink Serialisation

The Myriota Developer documentation has some sample webhook data payloads so I used JSON2csharp to generate a Data Transfer Object(DTO) to deserialise payload. The format of the message is a bit “odd”, the “Data “Value” contains an “escaped” JSON object.

{
  "EndpointRef": "ksnb8GB_TuGj:__jLfs2BQJ2d",
  "Timestamp": 1692928585,
  "Data": "{"Packets": [{"Timestamp": 1692927646796, "TerminalId": "0001020304", "Value": "00008c9512e624cce066adbae764cccccccccccc"}]}",
  "Id": "a5c1bffe-4b62-4233-bbe9-d4ecc4f8b6cb",
  "CertificateUrl": "https://security.myriota.com/data-13f7751f3c5df569a6c9c42a9ce73a8a.crt",
  "Signature": "FDJpQdWHwCY+tzCN/WvQdnbyjgu4BmP/t3cJIOEF11sREGtt7AH2L9vMUDji6X/lxWBYa4K8tmI0T914iPyFV36i+GtjCO4UHUGuFPJObCtiugVV8934EBM+824xgaeW8Hvsqj9eDeyJoXH2S6C1alcAkkZCVt0pUhRZSZZ4jBJGGEEQ1Gm+SOlYjC2exUOf0mCrI5Pct+qyaDHbtiHRd/qNGW0LOMXrB/9difT+/2ZKE1xvDv9VdxylXi7W0/mARCfNa0J6aWtQrpvEXJ5w22VQqKBYuj3nlGtL1oOuXCZnbFYFf4qkysPaXON31EmUBeB4WbZMyPaoyFK0wG3rwA=="
}
namespace devMobile.IoT.myriotaAzureIoTConnector.myriota.UplinkWebhook.Models
{
    public class UplinkPayloadWebDto
    {
        public string EndpointRef { get; set; }
        public long Timestamp { get; set; } 
        public string Data { get; set; } // Embedded JSON ?
        public string Id { get; set; }
        public string CertificateUrl { get; set; }
        public string Signature { get; set; }
    }
}

The UplinkWebhook controller “automagically” deserialises the message, then in code the embedded JSON is deserialised and “unpacked”, finally the processed message is inserted into an Azure Storage queue.

namespace devMobile.IoT.myriotaAzureIoTConnector.myriota.UplinkWebhook.Controllers
{
    [Route("[controller]")]
    [ApiController]
    public class UplinkController : ControllerBase
    {
        private readonly Models.ApplicationSettings _applicationSettings;
        private readonly ILogger<UplinkController> _logger;
        private readonly QueueServiceClient _queueServiceClient;

        public UplinkController(IOptions<Models.ApplicationSettings> applicationSettings, QueueServiceClient queueServiceClient, ILogger<UplinkController> logger)
        {
            _applicationSettings = applicationSettings.Value;
            _queueServiceClient = queueServiceClient;
            _logger = logger;
        }

        [HttpPost]
        public async Task<IActionResult> Post([FromBody] Models.UplinkPayloadWebDto payloadWeb)
        {
            _logger.LogInformation("SendAsync queue name:{QueueName}", _applicationSettings.QueueName);

            QueueClient queueClient = _queueServiceClient.GetQueueClient(_applicationSettings.QueueName);

            var serializeOptions = new JsonSerializerOptions
            {
                WriteIndented = true,
                Encoder = System.Text.Encodings.Web.JavaScriptEncoder.UnsafeRelaxedJsonEscaping
            };

            await queueClient.SendMessageAsync(Convert.ToBase64String(JsonSerializer.SerializeToUtf8Bytes(payloadWeb, serializeOptions)));

            return this.Ok();
        }
    }
}

The webhook application uses the QueueClientBuilderExtensions and AddServiceClient so a QueueServiceClient can be injected into the webhook controller.

namespace devMobile.IoT.myriotaAzureIoTConnector.myriota.UplinkWebhook
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var builder = WebApplication.CreateBuilder(args);

            // Add services to the container.
            builder.Services.AddControllers();

            builder.Services.AddApplicationInsightsTelemetry(i => i.ConnectionString = builder.Configuration.GetConnectionString("ApplicationInsights"));

            builder.Services.Configure<Models.ApplicationSettings>(builder.Configuration.GetSection("Application"));

            builder.Services.AddAzureClients(azureClient =>
            {
                azureClient.AddQueueServiceClient(builder.Configuration.GetConnectionString("AzureWebApi"));
            });

            var app = builder.Build();

            // Configure the HTTP request pipeline.

            app.UseHttpsRedirection();

            app.MapControllers();

            app.Run();
        }
    }
}

After debugging the application on my desktop with Telerik fiddler I deployed the application to one of my Azure subscriptions.

Azure Resource Group for the myriota Azure IoT Connector
Adding a new Destination in the myriota device manager

As part of configuring a new device test messages can be sent to the configured destinations.

Testing a new Destination in the myriota device manager
{
  "EndpointRef": "N_HlfTNgRsqe:uyXKvYTmTAO5",
  "Timestamp": 1563521870,
  "Data": "{"Packets": [{"Timestamp": 1563521870359,
    "TerminalId": "f74636ec549f9bde50cf765d2bcacbf9",
    "Value": "0101010101010101010101010101010101010101"}]}",
  "Id": "fe77e2c7-8e9c-40d0-8980-43720b9dab75",
  "CertificateUrl":    "https://security.myriota.com/data-13f7751f3c5df569a6c9c42a9ce73a8a.crt",
  "Signature": "k2OIBppMRmBT520rUlIvMxNg+h9soJYBhQhOGSIWGdzkppdT1Po2GbFr7jbg..."
}

The DTO generated with JSON2csharp needed some manual “tweaking” after examining how a couple of the sample messages were deserialised.

Azure Storage Explorer messages

I left the Myriota Developer Toolkit device (running the tracker sample) outside overnight and the following day I could see with Azure Storage Explorer a couple of messages in the Azure Storage Queue

Myriota device configuration

For a couple of weeks Myriota Developer Toolkit has been sitting under my desk and today I got some time to setup a device, register it, then upload some data.

Myriota Developer Toolkit

The first step was to download and install the Myriota Configurator so I could get the device registration information and install the tracker example application.

Using Windows File Explorer to “unblock” the downloaded file

After “unblocking” the zip file and upgrading my pip install the install script worked.

Myriota Configurator installation script

The application had to be run from the command line with “python MyriotaConfigurator.py”

Myriota Configurator main menu
Myriota Configurator retrieving device registration code

On the device I’m using the Tracker sample application to generate some sample payloads.

Myriota Configurator downloading tracker sample to device

The next step was to “register” my device and configure the destination(s) for its messages.

Myriota Device Manager Device configuration

Once the device and device manager configuration were sorted, I put the Tracker out on the back lawn on top of a large flowerpot.

Device Manager Access Times

On the “Access Times” page I could see that there were several periods when a satellite was overhead and overnight a couple of messages were uploaded.

ASP.NET Core authentication – In the beginning

While building my ASP.NET Core identity, Dapper Custom storage provider I found there wasn’t a lot of discussion of the ASPNETUserClaims functionality for fine “grained permissions”.

ASP.NET Core identity initial data model

ASP.NET Core identity Roles can also have individual claims but with the authorisation model of the legacy application I work on this functionality hasn’t been useful. We use role based authentication with a few user claims to minimise the size of our Java Web Tokens(JWT)

Visual Studio 2022 ASP.NET Core Web Application template options

The first step was to create a “bare-bones” ASP.NET Core Razor pages Web Application with Individual Accounts Authentication project

Default ASP.NET Core identity Web application Homepage

I tried to minimise the modifications to the application. I added EnableRetryOnFailure, some changes to names spaces etc. I also added support for email address confirmation with SendGrid and “authentication” link to the navabar in _Layout.cshtml.

@page
@model RolesModel
@{
    <table class="table">
        <thead>
            <tr>
                <th>Role</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var role in Model.Roles)
            {
                <tr>
                    <td>
                        @Html.DisplayFor(modelItem => role.Value)
                    </td>
                </tr>
            }
        </tbody>
    </table>
    <br/>
        <table class="table">
        <thead>
            <tr>
                <th>Claim Subject</th>
                <th>Value</th>
            </tr>
        </thead>

        <tbody>
            @foreach (var claim in Model.Claims)
            {
                <tr>
                    <td>
                        @Html.DisplayFor(modelItem => claim.Type)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => claim.Value)
                    </td>
                </tr>
            }
        </tbody>
    </table>
}

The “Authentication” page displays the logged in User’s Role and Claims.

namespace devMobile.AspNetCore.Identity.WebApp.EFCore.Pages
{
    [Authorize()]
    public class RolesModel : PageModel
    {
        private readonly ILogger<RolesModel> _logger;

        public List<Claim> Roles { get; set; }
        public List<Claim> Claims { get; set; }

        public RolesModel(ILogger<RolesModel> logger)
        {
            _logger = logger;
        }

        public void OnGet()
        {
            Roles = User.Claims.Where(c => c.Type == ClaimTypes.Role).ToList();

            Claims = User.Claims.Where(c => c.Type != ClaimTypes.Role).ToList();
        }
    }
}

Each user can have role(s), with optional claims, and some optional individual claims.

ASP.NET Core identity application Authentication information page

The WebApp.EFCore project is intended to be the starting point for a series of posts about ASP.NET Core identity so I have not included Cross-Origin Resource Sharing (CORS), Cross Site Request Forgery (CSRF) etc. functionality.

.NET Core web API + Dapper – MiniProfiler Revisited

While exploring some of the functionality of MiniProfiler there were some 3rd party examples which caught my attention.

using (SqlConnection connection = new SqlConnection(@"Data Source=...; Initial Catalog=SyncDB; Trusted_Connection=Yes"))
{
    using (ProfiledDbConnection profiledDbConnection = new ProfiledDbConnection(connection, MiniProfiler.Current))
    {
    if (profiledDbConnection.State != System.Data.ConnectionState.Open)
        profiledDbConnection.Open();
    using (SqlCommand command = new SqlCommand("Select * From Authors", connection))
    {
        using (ProfiledDbCommand profiledDbCommand = new ProfiledDbCommand(command, connection, MiniProfiler.Current))
        {                               
            var data = profiledDbCommand.ExecuteReader();

            //Write code here to populate the list of Authors
        }
    }
}                  

“Inspired” by code like this my first attempt to retrieve a list of stock items didn’t look right.

[HttpGet("AdoProfiledOtt")]
public async Task<ActionResult<IEnumerable<Model.StockItemListDtoV1>>> GetAdoProfiledOtt()
{
    List<Model.StockItemListDtoV1> response = new List<Model.StockItemListDtoV1>();

    using (SqlConnection connection = new SqlConnection(configuration.GetConnectionString("default")))
    {
        using (ProfiledDbConnection profiledDbConnection = new ProfiledDbConnection(connection, MiniProfiler.Current))
        {
            await profiledDbConnection.OpenAsync();

            using (SqlCommand command = new SqlCommand(sqlCommandText, connection))
            {
                using (ProfiledDbCommand profiledDbCommand = new ProfiledDbCommand(command, profiledDbConnection, MiniProfiler.Current))
                {
                    using (SqlDataReader reader = await command.ExecuteReaderAsync())
                    {
                        using (ProfiledDbDataReader profiledDbDataReader = new ProfiledDbDataReader(reader, MiniProfiler.Current))
                        {
                            var rowParser = profiledDbDataReader.GetRowParser<Model.StockItemListDtoV1>();

                            while (await profiledDbDataReader.ReadAsync())
                            {
                                response.Add(rowParser(profiledDbDataReader));
                            }
                        }
                    }
                }
            }

            await profiledDbConnection.CloseAsync();
        }
    }
}

Often Dapper has functionality like closing the connection if it needed to open it to reduce the amount of code required and this code looked verbose.

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

	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);
	}
	return this.Ok(response);
}

It seemed a bit odd that so many “usings” were needed so I had a look at ProfiledDBConnection.cs

/// <summary>
/// Initializes a new instance of the <see cref="ProfiledDbConnection"/> class.
/// Returns a new <see cref="ProfiledDbConnection"/> that wraps <paramref name="connection"/>,
/// providing query execution profiling. If profiler is null, no profiling will occur.
/// </summary>
/// <param name="connection"><c>Your provider-specific flavour of connection, e.g. SqlConnection, OracleConnection</c></param>
/// <param name="profiler">The currently started <see cref="MiniProfiler"/> or null.</param>
/// <exception cref="ArgumentNullException">Throws when <paramref name="connection"/> is <c>null</c>.</exception>
public ProfiledDbConnection(DbConnection connection, IDbProfiler? profiler)
{
    _connection = connection ?? throw new ArgumentNullException(nameof(connection));
    _connection.StateChange += StateChangeHandler;

    if (profiler != null)
    {
        _profiler = profiler;
    }
}
...
/// <summary>
/// Dispose the underlying connection.
/// </summary>
/// <param name="disposing">false if preempted from a <c>finalizer</c></param>
protected override void Dispose(bool disposing)
{
    if (disposing && _connection != null)
    {
        _connection.StateChange -= StateChangeHandler;
        _connection.Dispose();
    }
    base.Dispose(disposing);
    _connection = null!;
    _profiler = null;
}

One less “using” required as ProfiledDbConnection “automagically” disposes the SqlConnection. It also seemed a bit odd that the SqlCommand had a “using” so I had a look at ProfiledDbCommand.cs

 /// <summary>
/// Initializes a new instance of the <see cref="ProfiledDbCommand"/> class.
/// </summary>
/// <param name="command">The command.</param>
/// <param name="connection">The connection.</param>
/// <param name="profiler">The profiler.</param>
/// <exception cref="ArgumentNullException">Throws when <paramref name="command"/> is <c>null</c>.</exception>
public ProfiledDbCommand(DbCommand command, DbConnection? connection, IDbProfiler? profiler)
{
    _command = command ?? throw new ArgumentNullException(nameof(command));

    if (connection != null)
    {
        _connection = connection;
        UnwrapAndAssignConnection(connection);
    }

    if (profiler != null)
    {
        _profiler = profiler;
    }
}
...
/// <summary>
/// Releases all resources used by this command.
/// </summary>
/// <param name="disposing">false if this is being disposed in a <c>finalizer</c>.</param>
protected override void Dispose(bool disposing)
{
   if (disposing && _command != null)
   {
       _command.Dispose();
    }
    _command = null!;
    base.Dispose(disposing);
}

Another “using” not required as ProfiledDbCommand “automagically” disposes the SqlCommand as well. It also seemed a bit odd that the SqlDataReader had a using so I had a look at profileDbDataReader.cs

/// <summary>
/// Initializes a new instance of the <see cref="ProfiledDbDataReader"/> class (with <see cref="CommandBehavior.Default"/>).
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="profiler">The profiler.</param>
public ProfiledDbDataReader(DbDataReader reader, IDbProfiler profiler) : this(reader, CommandBehavior.Default, profiler) { }

/// <summary>
/// Initializes a new instance of the <see cref="ProfiledDbDataReader"/> class.
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="behavior">The behavior specified during command execution.</param>
/// <param name="profiler">The profiler.</param>
public ProfiledDbDataReader(DbDataReader reader, CommandBehavior behavior, IDbProfiler? profiler)
{
    WrappedReader = reader;
    Behavior = behavior;
    _profiler = profiler;
}
...
/// <summary>
/// The <see cref="DbDataReader"/> that is being used.
/// </summary>
public DbDataReader WrappedReader { get; }

/// <inheritdoc cref="DbDataReader.Dispose(bool)"/>
protected override void Dispose(bool disposing)
{
    // reader can be null when we're not profiling, but we've inherited from ProfiledDbCommand and are returning a
    // an unwrapped reader from the base command
    WrappedReader?.Dispose();
    base.Dispose(disposing);
}

Another “using” not required as ProfiledDbDataReader “automagically” disposes the SqlDataReader. This was my final version of profiling the System.Data.SqlClient code to retrieve a list of stock items.

[HttpGet("AdoProfiled")]
public async Task<ActionResult<IEnumerable<Model.StockItemListDtoV1>>> GetProfiledAdo()
{
    List<Model.StockItemListDtoV1> response = new List<Model.StockItemListDtoV1>();

    using (ProfiledDbConnection profiledDbConnection = new ProfiledDbConnection((SqlConnection)dapperContext.ConnectionCreate(), MiniProfiler.Current))
    {
        await profiledDbConnection.OpenAsync();

        using (ProfiledDbCommand profiledDbCommand = new ProfiledDbCommand(new SqlCommand(sqlCommandText), profiledDbConnection, MiniProfiler.Current))
        {
            DbDataReader reader = await profiledDbCommand.ExecuteReaderAsync();

            using (ProfiledDbDataReader profiledDbDataReader = new ProfiledDbDataReader(reader, MiniProfiler.Current))
            {
                var rowParser = profiledDbDataReader.GetRowParser<Model.StockItemListDtoV1>();

                while (await profiledDbDataReader.ReadAsync())
                {
                    response.Add(rowParser(profiledDbDataReader));
                }
            }
        }
    }

    return this.Ok(response);
}

The profileDbDataReader.cs implementation was “sparse” and when loading a longer list of stock items there were some ReadAsync calls which took a bit longer.

/// <summary>
/// The profiled database data reader.
/// </summary>
public class ProfiledDbDataReader : DbDataReader
{
    private readonly IDbProfiler? _profiler;

    /// <summary>
    /// Initializes a new instance of the <see cref="ProfiledDbDataReader"/> class (with <see cref="CommandBehavior.Default"/>).
    /// </summary>
    /// <param name="reader">The reader.</param>
    /// <param name="profiler">The profiler.</param>
    public ProfiledDbDataReader(DbDataReader reader, IDbProfiler profiler) : this(reader, CommandBehavior.Default, profiler) { }

    /// <summary>
    /// Initializes a new instance of the <see cref="ProfiledDbDataReader"/> class.
    /// </summary>
    /// <param name="reader">The reader.</param>
    /// <param name="behavior">The behavior specified during command execution.</param>
    /// <param name="profiler">The profiler.</param>
    public ProfiledDbDataReader(DbDataReader reader, CommandBehavior behavior, IDbProfiler? profiler)
    {
        WrappedReader = reader;
        Behavior = behavior;
        _profiler = profiler;
    }

    /// <summary>Gets the behavior specified during command execution.</summary>
    public CommandBehavior Behavior { get; }

    /// <inheritdoc cref="DbDataReader.Depth"/>
    public override int Depth => WrappedReader.Depth;

    /// <inheritdoc cref="DbDataReader.FieldCount"/>
    public override int FieldCount => WrappedReader.FieldCount;

    /// <inheritdoc cref="DbDataReader.HasRows"/>
    public override bool HasRows => WrappedReader.HasRows;

    /// <inheritdoc cref="DbDataReader.IsClosed"/>
    public override bool IsClosed => WrappedReader.IsClosed;

    /// <inheritdoc cref="DbDataReader.RecordsAffected"/>
    public override int RecordsAffected => WrappedReader.RecordsAffected;

    /// <summary>
    /// The <see cref="DbDataReader"/> that is being used.
    /// </summary>
    public DbDataReader WrappedReader { get; }

    /// <inheritdoc cref="DbDataReader.this[string]"/>
    public override object this[string name] => WrappedReader[name];

    /// <inheritdoc cref="DbDataReader.this[int]"/>
    public override object this[int ordinal] => WrappedReader[ordinal];
...
    /// <inheritdoc cref="DbDataReader.GetString(int)"/>
    public override string GetString(int ordinal) => WrappedReader.GetString(ordinal);

    /// <inheritdoc cref="DbDataReader.GetValue(int)"/>
    public override object GetValue(int ordinal) => WrappedReader.GetValue(ordinal);

    /// <inheritdoc cref="DbDataReader.GetValues(object[])"/>
    public override int GetValues(object[] values) => WrappedReader.GetValues(values);

    /// <inheritdoc cref="DbDataReader.IsDBNull(int)"/>
    public override bool IsDBNull(int ordinal) => WrappedReader.IsDBNull(ordinal);

    /// <inheritdoc cref="DbDataReader.IsDBNullAsync(int, CancellationToken)"/>
    public override Task<bool> IsDBNullAsync(int ordinal, CancellationToken cancellationToken) => WrappedReader.IsDBNullAsync(ordinal, cancellationToken);

    /// <inheritdoc cref="DbDataReader.NextResult()"/>
    public override bool NextResult() => WrappedReader.NextResult();

    /// <inheritdoc cref="DbDataReader.NextResultAsync(CancellationToken)"/>
    public override Task<bool> NextResultAsync(CancellationToken cancellationToken) => WrappedReader.NextResultAsync(cancellationToken);

    /// <inheritdoc cref="DbDataReader.Read()"/>
    public override bool Read() => WrappedReader.Read();

    /// <inheritdoc cref="DbDataReader.ReadAsync(CancellationToken)"/>
    public override Task<bool> ReadAsync(CancellationToken cancellationToken) => WrappedReader.ReadAsync(cancellationToken);

    /// <inheritdoc cref="DbDataReader.Close()"/>
    public override void Close()
    {
        // reader can be null when we're not profiling, but we've inherited from ProfiledDbCommand and are returning a
        // an unwrapped reader from the base command
        WrappedReader?.Close();
        _profiler?.ReaderFinish(this);
    }

    /// <inheritdoc cref="DbDataReader.GetSchemaTable()"/>
    public override DataTable? GetSchemaTable() => WrappedReader.GetSchemaTable();

    /// <inheritdoc cref="DbDataReader.Dispose(bool)"/>
    protected override void Dispose(bool disposing)
    {
        // reader can be null when we're not profiling, but we've inherited from ProfiledDbCommand and are returning a
        // an unwrapped reader from the base command
        WrappedReader?.Dispose();
        base.Dispose(disposing);
    }
}

In the [HttpGet(“DapperProfiledQueryMultipleStep”)] method I wrapped ReadAsync and could see in the profiling that every so often a call did take significantly longer.

using (MiniProfiler.Current.Step("invoiceSummaryLine.ReadAsync"))
{
   response.InvoiceLines = await invoiceSummary.ReadAsync<Model.InvoiceLineSummaryListDtoV1>();
}

I did consider modifying profileDbDataReader.cs to add some instrumentation to the Read… and Get… methods but, the authors of miniprofiler are way way smarter than me so there must be a reason why they didn’t.

.NET ASP.NET Core Custom Identity Provider Sample Update

As part of some security updates to one of the ASP.NET Core applications I work on there were changes to how users are authorised and authenticated(A&A). As part of this we were looking at replacing custom A&A (login names & passwords+ existing database) with ASP.NET Core Identity and a Custom Storage Provider. I downloaded the sample code from the Github AspNetCore.Docs repository and opened the project in Visual Studio 2022.

The Custom Storage Provider sample hasn’t been updated for years and used .NET Core 1.1 which was released Q4 2016.

When I opened the project there were errors and many of the NuGets were deprecated.

The sample code uses Dapper (micro ORM) to access tables in a custom Microsoft SQL Server database but there was also NuGets and code that referenced Entity Framework Core.

After several unsuccessful attempts at updating the NuGets packages I started again from scratch

The code wouldn’t compile so I started fixing issues (The first couple of attempts were very “hacky”). The UseDatabaseErrorPage method was from EF Core so it was commented out. The UseBrowserLink method was from the Browser Link support which I decided not to use etc.

...
namespace CustomIdentityProviderSample
{
    public class Startup
    {
        public Startup(IHostingEnvironment env)
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(env.ContentRootPath)
                .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
                .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true);

            if (env.IsDevelopment())
            {
                // For more details on using the user secret store see https://go.microsoft.com/fwlink/?LinkID=532709
                builder.AddUserSecrets<Startup>();
            }

            builder.AddEnvironmentVariables();
            Configuration = builder.Build();
        }

        public IConfigurationRoot Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            // Add identity types
            services.AddIdentity<ApplicationUser, ApplicationRole>()
                .AddDefaultTokenProviders();

            // Identity Services
            services.AddTransient<IUserStore<ApplicationUser>, CustomUserStore>();
            services.AddTransient<IRoleStore<ApplicationRole>, CustomRoleStore>();
            string connectionString = Configuration.GetConnectionString("DefaultConnection");
            services.AddTransient<SqlConnection>(e => new SqlConnection(connectionString));
            services.AddTransient<DapperUsersTable>();

            services.AddMvc();

            // Add application services.
            services.AddTransient<IEmailSender, AuthMessageSender>();
            services.AddTransient<ISmsSender, AuthMessageSender>();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
        {
            // loggerFactory.AddConsole(Configuration.GetSection("Logging"));
            // loggerFactory.AddDebug();

            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                // app.UseDatabaseErrorPage(); BHL
                // app.UseBrowserLink(); BHL
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
            }

            app.UseStaticFiles();

            app.UseRouting(); // BHL

            // app.UseIdentity(); BHL
            app.UseAuthentication();
            app.UseAuthorization();

            // Add external authentication middleware below. To configure them please see https://go.microsoft.com/fwlink/?LinkID=532715
            app.UseMvc(routes =>
            {
                routes.MapRoute(
                    name: "default",
                    template: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}

This process was repeated many times until the Custom Storage Provider sample compiled so I could run it in the Visual Studio 2022 debugger.

The Custom Storage Provider sample then failed because of changes to routing etc.

The Custom Storage Provider sample then failed because of changes to A&A middleware

The Custom Storage Provider sample then failed because the database creation script and the code didn’t match.

CREATE TABLE [dbo].[CustomUser](
	[Id] [uniqueidentifier] NOT NULL,
	[Email] [nvarchar](256) NULL,
	[EmailConfirmed] [bit] NOT NULL,
	[PasswordHash] [nvarchar](max) NULL,
	[UserName] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_dbo.CustomUsers] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Finally, the Custom Storage Provider sample worked but the page layout was broken

I then worked through the Razor views adding stylesheets where necessary.

@inject Microsoft.ApplicationInsights.AspNetCore.JavaScriptSnippet JavaScriptSnippet
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>@ViewData["Title"] - CustomIdentityProviderSample</title>
    @* BHL *@
    <link rel="stylesheet" href="https://ajax.aspnetcdn.com/ajax/bootstrap/3.3.7/css/bootstrap.css"
          asp-fallback-href="~/lib/bootstrap/dist/css/bootstrap.css"
          asp-fallback-test-class="sr-only" asp-fallback-test-property="position" asp-fallback-test-value="absolute" />

    <environment names="Development">
        <link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.css" />
    </environment>
    <environment names="Staging,Production">
        <link rel="stylesheet" href="https://ajax.aspnetcdn.com/ajax/bootstrap/3.3.7/css/bootstrap.css"
              asp-fallback-href="~/lib/bootstrap/dist/css/bootstrap.css"
              asp-fallback-test-class="sr-only" asp-fallback-test-property="position" asp-fallback-test-value="absolute" />
    </environment>
    @Html.Raw(JavaScriptSnippet.FullScript)
</head>
...

The Custom Storage Provider sample subset of pages then rendered better.

The Custom Storage Provider sample then failed when functionality like user phone number was used.

This required the addition of implementations (many just throw NotImplementedException exceptions) for IUserPhoneNumberStore, IUserTwoFactorStore, and IUserLoginStore etc.

using Microsoft.AspNetCore.Identity;
using System;
using System.Threading.Tasks;
using System.Threading;
using System.Collections.Generic;

namespace CustomIdentityProviderSample.CustomProvider
{
    /// <summary>
    /// This store is only partially implemented. It supports user creation and find methods.
    /// </summary>
    public class CustomUserStore : IUserStore<ApplicationUser>, 
        IUserPasswordStore<ApplicationUser>,
        IUserPhoneNumberStore<ApplicationUser>,
        IUserTwoFactorStore<ApplicationUser>,
        IUserLoginStore<ApplicationUser>
    {
        private readonly DapperUsersTable _usersTable;

        public CustomUserStore(DapperUsersTable usersTable)
        {
            _usersTable = usersTable;
        }

        public Task AddLoginAsync(ApplicationUser user, UserLoginInfo login, CancellationToken cancellationToken)
        {
            throw new NotImplementedException();
        }

        public async Task<IdentityResult> CreateAsync(ApplicationUser user, 
            CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            if (user == null) throw new ArgumentNullException(nameof(user));

            return await _usersTable.CreateAsync(user);
        }

        public async Task<IdentityResult> DeleteAsync(ApplicationUser user, 
            CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            if (user == null) throw new ArgumentNullException(nameof(user));

            return await _usersTable.DeleteAsync(user);

        }

        public async Task<ApplicationUser> FindByIdAsync(string userId, 
            CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            if (userId == null) throw new ArgumentNullException(nameof(userId));
            Guid idGuid;
            if(!Guid.TryParse(userId, out idGuid))
            {
                throw new ArgumentException("Not a valid Guid id", nameof(userId));
            }

            return await _usersTable.FindByIdAsync(idGuid);

        }

        public Task<ApplicationUser> FindByLoginAsync(string loginProvider, string providerKey, CancellationToken cancellationToken)
        {
            throw new NotImplementedException();
        }

        public async Task<ApplicationUser> FindByNameAsync(string userName, 
            CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            if (userName == null) throw new ArgumentNullException(nameof(userName));

            return await _usersTable.FindByNameAsync(userName);
        }

        public async Task<IList<UserLoginInfo>> GetLoginsAsync(ApplicationUser user, CancellationToken cancellationToken)
        {
            cancellationToken.ThrowIfCancellationRequested();
            if (user == null) throw new ArgumentNullException(nameof(user));

            return await _usersTable.GetLoginsAsync(user.Id);
        }
... 
}

This also required extensions to the DapperUsersTable.cs.

public async Task<IdentityResult> UpdateAsync(ApplicationUser user)
{
   string sql = "UPDATE dbo.AspNetUsers " + // BHL
                     "SET [Id] = @Id, [Email]= @Email, [EmailConfirmed] = @EmailConfirmed, [PasswordHash] = @PasswordHash, [UserName] = @UserName " +
                     "WHERE Id = @Id;";


   int rows = await _connection.ExecuteAsync(sql, new { user.Id, user.Email, user.EmailConfirmed, user.PasswordHash, user.UserName });

   if (rows == 1)
   {
      return IdentityResult.Success;
   }

return IdentityResult.Failed(new IdentityError { Description = $"Could not update user {user.Email}." });
}

After many failed attempts my very nasty Custom Storage Provider refresh works (with many warnings and messages). I now understand how they work well enough that I am going to start again from scratch.

.NET Core web API + Dapper – Distributed Cache

I have used LazyCache for several projects (The Things Network V2 HTTP, The Things Industries V2 MQTT The Things Industries V3 and Swarm Space Azure IoT Connector etc.) to cache Azure IoT Hub DeviceClient and other object instances.

The note on the wiki page For LazyCache v2+ users, you should consider switching away from LazyCache to IDistributedCache. More information at #59 caught my attention.

I have written other posts about caching Dapper query results with the Dapper Extension Library which worked well but had some configuration limitations. I also have posts about off-loading read-only workloads with Azure Active geo-replication or SQL Data Sync for Azure, which worked well in some scenarios but had limitations (performance and operational costs).

The IDistributedCache has Memory, SQL Server and Redis implementations so I built an Azure AppService to explore the functionality in more detail. In another project I had been working with the Azure SignalR Service and the use of the MessagePack library(rather than serialised JSON) caught my attention so I have added basic support for that as well.

I explored the in-memory implementation (AddDistributedMemoryCache) on my development machine and found “tinkering” with the configuration options had little impact on the performance of my trivial sample application.

public static void Main(string[] args)
{
    var builder = WebApplication.CreateBuilder(args);

    // Add services to the container.
    builder.Services.AddApplicationInsightsTelemetry();

    // Add services to the container.
    builder.Services.AddSingleton<IDapperContext>(s => new DapperContext(builder.Configuration));

    builder.Services.AddControllers();

#if SERIALISATION_MESSAGE_PACK
    //MessagePackSerializer.DefaultOptions = MessagePack.Resolvers.ContractlessStandardResolver.Options;
    //MessagePackSerializer.DefaultOptions = MessagePack.Resolvers.ContractlessStandardResolver.Options.WithCompression(MessagePackCompression.Lz4Block);
    MessagePackSerializer.DefaultOptions = MessagePack.Resolvers.ContractlessStandardResolver.Options.WithCompression(MessagePackCompression.Lz4BlockArray);
#endif

#if DISTRIBUTED_CACHE_MEMORY
    builder.Services.AddDistributedMemoryCache(options =>
    {
       options.SizeLimit = 1000 * 1024 * 1024; // 1000MB
    });
    builder.Services.AddDistributedMemoryCache();
#endif

#if DISTRIBUTED_CACHE_REDIS
    var configurationOptions = new ConfigurationOptions
    {
        EndPoints = { builder.Configuration.GetSection("RedisConnection").GetValue<string>("EndPoints") },
        AllowAdmin = true,
        Password = builder.Configuration.GetSection("RedisConnection").GetValue<string>("Password"),
        Ssl = true,
        ConnectRetry = 5,
        ConnectTimeout = 10000,
        SslProtocols = System.Security.Authentication.SslProtocols.Tls12,
        AbortOnConnectFail = false,
    };

    builder.Services.AddStackExchangeRedisCache(options =>
    {
        options.InstanceName = "Dapper WebAPI Instance";
        options.ConfigurationOptions = configurationOptions;
    });
#endif

#if DISTRIBUTED_CACHE_SQL_SERVER
    builder.Services.AddDistributedSqlServerCache(options =>
    {
        options.ConnectionString = builder.Configuration.GetConnectionString("CacheDatabase");
        options.SchemaName = "dbo";
        options.TableName = "StockItemsCache";
    });
#endif

    var app = builder.Build();

    // Configure the HTTP request pipeline.
    app.UseHttpsRedirection();
    app.MapControllers();
    app.Run();
}

I tested the SQL Server implementation (AddDistributedSqlServerCached) using the SQL Server on my development machine, and Azure SQL as a backing store. I did consider using SQL Azure In-Memory OLTP but the performance improvement with my trivial example would most probably not worth the additional cost of the required SKU.

CREATE TABLE [dbo].[StockItemsCache](
	[Id] [nvarchar](449) NOT NULL,
	[Value] [varbinary](max) NOT NULL,
	[ExpiresAtTime] [datetimeoffset](7) NOT NULL,
	[SlidingExpirationInSeconds] [bigint] NULL,
	[AbsoluteExpiration] [datetimeoffset](7) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The table used to store the data wasn’t very complex and I could view the data associated with a cache key in SQL Server Mangement studio.

SQL Server Managment Studio displaying cache table contents

One of the applications I work on uses a complex SQL Server Stored procedure to load reference data (updated daily) and being able to purge the cache at the end of this process like this might be useful. For a geographically distributed application putting the Azure SQL instance “closer” to the application’s users might be worth considering.

I trialed the Redis implementation with Memurai (on my development machine) and Azure Cache for Redis with multiple Azure AppService clients and there was a significant improvement in performance.

[HttpGet]
public async Task<ActionResult<IEnumerable<Model.StockItemListDtoV1>>> Get()
{
    var utcNow = DateTime.UtcNow;

    var cached = await distributedCache.GetAsync("StockItems");
    if (cached != null)
    {
#if SERIALISATION_JSON
        return this.Ok(JsonSerializer.Deserialize<List<Model.StockItemListDtoV1>>(cached));
#endif
#if SERIALISATION_MESSAGE_PACK
        return this.Ok(MessagePackSerializer.Deserialize<List<Model.StockItemListDtoV1>>(cached));
#endif
    }

    var stockItems = await dbConnection.QueryWithRetryAsync<Model.StockItemListDtoV1>(sql: sqlCommandText, commandType: CommandType.Text);

#if SERIALISATION_JSON
    await distributedCache.SetAsync("StockItems", JsonSerializer.SerializeToUtf8Bytes(stockItems), new DistributedCacheEntryOptions()
#endif
#if SERIALISATION_MESSAGE_PACK
    await distributedCache.SetAsync("StockItems", MessagePackSerializer.Serialize(stockItems), new DistributedCacheEntryOptions()
#endif
    {
        AbsoluteExpiration = new DateTime(utcNow.Year, utcNow.Month, DateTime.DaysInMonth(utcNow.Year, utcNow.Month), StockItemListAbsoluteExpiration.Hours, StockItemListAbsoluteExpiration.Minutes, StockItemListAbsoluteExpiration.Seconds)
    });

    return this.Ok(stockItems);
}

[HttpGet("NoLoad")]
public async Task<ActionResult<IEnumerable<Model.StockItemListDtoV1>>> GetNoLoad()
{
    var cached = await distributedCache.GetAsync("StockItems");
    if (cached == null)
    {
        return this.NoContent();
    }

#if SERIALISATION_JSON
    return this.Ok(JsonSerializer.Deserialize<List<Model.StockItemListDtoV1>>(cached));
#endif
#if SERIALISATION_MESSAGE_PACK
        return this.Ok(MessagePackSerializer.Deserialize<List<Model.StockItemListDtoV1>>(cached));
#endif
}

In my test environment the JSON payload for a list of stock items was a bit “chunky” at 25K bytes, so I added compile time configurable support for the MessagePack library. This significantly reduced the size of the payload LZ4Block (5K bytes) and LZ4BlockArray (5K2 bytes) which should reduce network traffic.

Assuming the overheads of JSON vs. MessagePack serialisation are similar and the much smaller MessagePack library payload I would most probably use MessagePack and LZ4BlockArray (For improved compatibility with other implementations) compression.

.NET Core web API + Dapper – Distributed Cache Redis HMGET Error

Initially, I was running the WebAPI Dapper DistributedCache application on my development box and it ran third/forth time after sorting out the Azure Cache for Redis connection string and firewall configuration.

StockItems list served from the cache running on my Desktop
The Azure Cache for Redis firewall configuration for my home office

I then deployed the WebAPI Dapper DistributedCache application to an Azure AppService and it failed (spoiler alert it wasn’t DNS).

Azure AppService hosted StockItems list code failing in web browser

I then fired up Azure Application Insights to look at the logging information.

Azure Application Insights diagnostic information

There were many questions and tentative answers on stackoverflow some of them I tried, and which didn’t work in my scenario.

No connection is active/available to service this operation: HMGET Dapper WebAPI InstanceStockItems; 
UnableToConnect on xxxxxxxxxxxxxxxxxxxxxxxxxxx.redis.cache.windows.net:6380/Interactive, 
Initializing/NotStarted, 
last: NONE, 
origin: BeginConnectAsync, 
outstanding: 0, 
last-read: 10s ago, last-write: 10s ago, keep-alive: 60s, 
state: Connecting, 
mgr: 10 of 10 available, 
last-heartbeat: never, global: 10s ago, 
v: 2.2.4.27433, 
mc: 1/1/0, mgr: 10 of 10 available, 
clientName: XXXXXXXXXXXX, <----------------------This was important
IOCP: (Busy=0,Free=1000,Min=1,Max=1000), 
WORKER: (Busy=5,Free=1018,Min=1,Max=1023), 
v: 2.2.4.27433 
UnableToConnect on xxxxxxxxxxxxxxxxxxxxxxxxxxx.cache.windows.net:6380/Interactive, 
Initializing/NotStarted, 
last: NONE, 
origin: BeginConnectAsync, 
outstanding: 0, 
last-read: 10s ago, last-write: 10s ago, keep-alive: 60s, 
state: Connecting, 
mgr: 10 of 10 available, 
last-heartbeat: never, global: 10s ago, 
v: 2.2.4.27433 

I then recreated my configuration from scratch so none of my random changes based on stackoverflow posts would mess my next round of debugging.

Azure AppService Virtual, Outbound and Additional Outbound IP Addresses

The error appeared to be a networking issue, most probably the firewall blocking connections so I added the Azure AppService Virtual IP Address which didn’t work…

Firewall configuration for Azure AppService Virtual IP Address

I then went back and re-read the error message and noticed “clientName: XXXXXXXXXXXX,”. The Azure AppService client name wasn’t the default domain…

Firewall configuration for Azure AppService Virtual and outbound IP Addresses

I then added the Outbound IP addresses to the firewall and the Azure AppService could connect to my Azure Cache for Redis instance.

StockItems list served from the cache running in an AppService

As soon as I realised it wasn’t DNS I should have “stopped counted to ten”, debugged the issue carefully and not immediately gone to stackoverflow.

.NET Core web API + Dapper – LazyCache

I have used Alastair Crabtree’s LazyCache (read-through in-memory cache) on a couple of other projects e.g. The Things Industries V3 Connector (DeviceClient instances) and Swarm Space Azure IoT Connector (DeviceClient Instances and CS-Script binaries).

Adding LazyCache support to an ASP.Net Core WebAPI application is one line of code.

public static void Main(string[] args)
{
    var builder = WebApplication.CreateBuilder(args);

    // Add services to the container.
    builder.Services.AddApplicationInsightsTelemetry();

    // Add services to the container.
    builder.Services.AddTransient<IDapperContext>(s => new DapperContext(builder.Configuration));

    builder.Services.AddControllers();

    // Register IAppCache as a singleton CachingService
    builder.Services.AddLazyCache();

    var app = builder.Build();

    // Configure the HTTP request pipeline.

    app.UseHttpsRedirection();

    app.MapControllers();

    app.Run();
}

One of the applications I work uses a lot of “reference” data which is effectively static e.g. national/regional holidays in countries where they have customers, and data which is updated on a schedule e.g. exchange rates downloaded at a known time every day.

//...
 private readonly TimeSpan StockItemListAbsoluteExpirationTime = new TimeSpan(23, 59, 59);
//....

private async Task<IEnumerable<Model.StockItemListDtoV1>> GetStockItemsAsync()
{
    return await dbConnection.QueryWithRetryAsync<Model.StockItemListDtoV1>(sql: sqlCommandText, commandType: CommandType.Text);
}

[HttpGet]
public async Task<ActionResult<IEnumerable<Model.StockItemListDtoV1>>> Get()
{
    MemoryCacheEntryOptions memoryCacheEntryOptions = new MemoryCacheEntryOptions()
    {
        Priority = CacheItemPriority.NeverRemove,
        AbsoluteExpiration = DateTime.UtcNow.Date.Add(StockItemListAbsoluteExpirationTime)
    };

    return this.Ok(await cache.GetOrAddAsync("StockItems", () => GetStockItemsAsync(), memoryCacheEntryOptions));
}

[HttpDelete()]
public ActionResult Delete()
{
    cache.Remove("StockItems");

    return this.Ok();
}

For the holiday information updates, the dataset could be deleted(“burped”) then refreshed the next time it is referenced or deleted then re-loaded. For exchange rate updates the cache could automatically expire the dataset shortly after the scheduled download.

When I deployed my demo application to an Azure AppService the requests (using the composer tab of Telerik Fiddler) took longer than when the application was running on my development box which was unexpected. I think this was because the JavaScript Object Notation(JSON) response payload was larger than the AzureSQL Tabular Data Stream(TDS) payload.

The LazyCache source code hasn’t been updated for quite a while (but it is quite stable and works well) and moving to other libraries has been recommended in the wiki.