.NET Core web API + Dapper – Web Caching

Web cache validation with eTags

On a couple of the systems I work on there are a number of queries (often complex spatial searches) which are very resource intensive but are quite readily cached. In these systems we have used HTTP GET and HEAD Request methods together so that the client only re-GETs the query results after a HEAD method indicates there have been updates.

I have been trying to keep the number of changes to my Microsoft SQL Azure World Wide Importers database to a minimum but for this post I have added a rowversion column to the StockGroups table. The rowversion data type is an automatically generated, unique 8 byte binary(12 bytes Base64 encoded) number within a database.

StockGroups table with Version column

Adding a rowversion table to an existing System Versioned table in the SQL Server Management Studio Designer is painful so I used…

ALTER TABLE [Warehouse].[StockGroups] ADD [Version] [timestamp] NULL

To reduce complexity the embedded SQL is contains two commands (normally I wouldn’t do this) one for retrieving the list StockGroups the other for retrieving the maximum StockGroup rowversion. If a StockGroup is changed the rowversion will be “automagically” updated and the maximum value will change.

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

	try
	{
		using (SqlConnection db = new SqlConnection(this.connectionString))
		{
			var parameters = new DynamicParameters();

			parameters.Add("@RowVersion", dbType: DbType.Binary, direction: ParameterDirection.Output, size: ETagBytesLength);

			response = await db.QueryAsync<Model.StockGroupListDtoV1>(sql: @"SELECT [StockGroupID] as ""ID"", [StockGroupName] as ""Name""FROM [Warehouse].[StockGroups] ORDER BY Name; SELECT @RowVersion=MAX(Version) FROM [Warehouse].[StockGroups]", param: parameters, commandType: CommandType.Text);

			if (response.Any())
			{
				byte[] rowVersion = parameters.Get<byte[]>("RowVersion");

				this.HttpContext.Response.Headers.Add("ETag", Convert.ToBase64String(rowVersion));
			}
		}
	}
	catch (SqlException ex)
	{
		logger.LogError(ex, "Retrieving list of StockGroups");

		return this.StatusCode(StatusCodes.Status500InternalServerError);
	}

	return this.Ok(response);
}

I used Telerik Fiddler to to capture the GET response payload.

HTTP/1.1 200 OK
Transfer-Encoding: chunked
Content-Type: application/json; charset=utf-8
ETag: AAAAAAABrdE=
Server: Microsoft-IIS/10.0
X-Powered-By: ASP.NET
Date: Sat, 26 Jun 2021 06:12:16 GMT

136
[
   {"id":5,"name":"Airline Novelties"},
   {"id":2,"name":"Clothing"},
   {"id":6,"name":"Computing Novelties"},
   {"id":8,"name":"Furry Footwear"},
   {"id":3,"name":"Mugs"},
   {"id":1,"name":"Novelty Items"},
   {"id":10,"name":"Packaging Material"},
   {"id":9,"name":"Toys"},
   {"id":4,"name":"T-Shirts"},
   {"id":7,"name":"USB Novelties"}
]
0

The HEAD method requests the maximum rwoversion value from the StockGroups table and compares it to the eTag. In a more complex scenario this could be a call to a local cache to see if a query result has bee refreshed.

[HttpHead]
public async Task<ActionResult> Head([Required][FromHeader(Name = "ETag")][MinLength(ETagBase64Length, ErrorMessage = "eTag length invalid too short")][MaxLength(ETagBase64Length, ErrorMessage = "eTag length {0} invalid too long")] string eTag)
{
	byte[] headerVersion = new byte[ETagBytesLength];

	if (!Convert.TryFromBase64String(eTag, headerVersion, out _))
	{
		logger.LogInformation("eTag invalid format");

		return this.BadRequest("eTag invalid format");
	}

	try
	{
		using (SqlConnection db = new SqlConnection(this.connectionString))
		{
			byte[] databaseVersion = await db.ExecuteScalarAsync<byte[]>(sql: "SELECT MAX(Version) FROM [Warehouse].[StockGroups]", commandType: CommandType.Text);

			if (headerVersion.SequenceEqual(databaseVersion))
			{
				return this.StatusCode(StatusCodes.Status304NotModified);
			}
		}
	}
	catch (SqlException ex)
	{
		logger.LogError(ex, "Retrieving StockItem list");

		return this.StatusCode(StatusCodes.Status500InternalServerError);
	}

	return this.Ok();
}

I used Fiddler to to capture a HEAD response payload a 304 Not modified.

HTTP/1.1 304 Not Modified
Server: Microsoft-IIS/10.0
X-Powered-By: ASP.NET
Date: Sat, 26 Jun 2021 22:09:02 GMT

I then modified the database and the response changed to 200 OK indicating the local cache should be updated with a GET.

HTTP/1.1 200 OK
Transfer-Encoding: chunked
Server: Microsoft-IIS/10.0
X-Powered-By: ASP.NET
Date: Sat, 26 Jun 2021 22:09:59 GMT

This approach combined with the use of the If-Match, If-Modified-Since, If-None-Match and If-Unmodified-since allows web and client side caches to use previously requested results when there have been no changes. This can significantly reduce the amount of network traffic and server requests.

As part of my testing I modified the eTag so it was invalid (to check the Convert.ToBase64String and Convert.TryFromBase64String error handling) and the response was much smaller than I expected.

HTTP/1.1 400 Bad Request
Content-Length: 240
Content-Type: application/problem+json; charset=utf-8
Server: Microsoft-IIS/10.0
X-Powered-By: ASP.NET
Date: Sat, 26 Jun 2021 06:28:11 GMT

This was unlike the helpful validation messages returned by the GET method of the StockItems pagination example code

{
   "type":"https://tools.ietf.org/html/rfc7231#section-6.5.1",
   "title":"One or more validation errors occurred.",
   "status":400,
   "traceId":"00-bd68c94bf05f5c4ca8752011d6a60533-48e966211dec4847-00",
   "errors": 
   {
      "PageSize":["PageSize must be present and greater than 0"],
      "PageNumber":["PageNumber must be present and greater than 0"]
   }
}

The lack of diagnostic information was not helpful and I’ll explore this further in a future post.

ASP MVC Core V2.1 and Cross-Origin Resource Sharing

I’m working on an project for a customer which implements a number of application programming Interfaces(API) for a Single Page Application(SPA) and other clients. We are using entity tags (ETags) for versioning and the front end developers found the couldn’t access them from javascipt running in mainstream browser clients (June 2018).

The problems was understanding how Cross-Origin Resource Sharing (CORS) worked and how it interacted with our security model (API key and OAuth2.0 depending on the client)

In our scenario we first found the pre-flight check wasn’t working because in the HyperText Transfer Protocol (HTTP) OPTIONS method our X-API-KEY check was failing

OPTIONS http://xyz.azurewebsites.net/api/portfolio HTTP/1.1
...
Access-Control-Request-Headers: x-api-key
Access-Control-Request-Method: GET
Accept-Encoding: gzip, deflate
Content-Length: 0
Host: xyz.azurewebsites.net
Connection: Keep-Alive
Pragma: no-cache

HTTP/1.1 400 Bad Request
Transfer-Encoding: chunked
Server: Kestrel
X-Powered-By: ASP.NET
...
Date: Sun, 24 Jun 2018 05:48:30 GMT

13
API Key is invalid.
0

So I disabled X-API-KEY validation in startup.cs

public async Task Invoke(HttpContext context)
{
   if (context.Request.Method == "OPTIONS")
   {
      await this.next.Invoke(context);
      return;
   }

   var claims = new List();
…

OPTIONS then worked

OPTIONS http://xyz.azurewebsites.net/api/portfolio HTTP/1.1
...
Access-Control-Request-Headers: x-api-key
Access-Control-Request-Method: GET
Accept-Encoding: gzip, deflate
Content-Length: 0
Host: xyz.azurewebsites.net
Connection: Keep-Alive
Pragma: no-cache

HTTP/1.1 404 Not Found
Server: Kestrel
X-Powered-By: ASP.NET
...
Date: Sun, 24 Jun 2018 05:52:20 GMT
Content-Length: 0

I then turned on CORS allowing pretty much anything

public void ConfigureServices(IServiceCollection services)
{
   services.AddCors(options =>
   {
      options.AddPolicy("CorsPolicy",
      builder => builder.AllowAnyOrigin()
         .AllowAnyMethod()
         .AllowAnyHeader()
         .AllowCredentials());
   });
   services.AddMvc();
}

public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
   if (env.IsDevelopment())
   {
      app.UseDeveloperExceptionPage();
   }

   TelemetryConfiguration.Active.InstrumentationKey = this.configuration.GetSection("ApplicationInsights").GetSection("InstrumentationKey").Value;

   loggerFactory.AddLog4Net();
   this.log.Info("Startup.Configure called");

   app.ApplyUserKeyValidation();
   app.UseCors("CorsPolicy");
   app.UseMvc();
   }
}

OPTIONS then worked

OPTIONS http://xyz.azurewebsites.net/api/portfolio HTTP/1.1
...
Access-Control-Request-Headers: x-api-key
Access-Control-Request-Method: GET
Accept-Encoding: gzip, deflate
Content-Length: 0
Host: xyz.azurewebsites.net
Connection: Keep-Alive
Pragma: no-cache

HTTP/1.1 204 No Content
Vary: Origin
Server: Kestrel
Access-Control-Allow-Credentials: true
Access-Control-Allow-Headers: x-api-key
Access-Control-Allow-Origin: file://
X-Powered-By: ASP.NET
...
Date: Sun, 24 Jun 2018 05:57:33 GMT

GET then worked

GET http://xyz.azurewebsites.net/api/portfolio HTTP/1.1
...
X-API-KEY: ABCDEFGHIJKLMNOPQRSTUVWXYZ
Accept-Language: en-NZ
Accept-Encoding: gzip, deflate
If-None-Match: 00-00-00-00-00-00-00-76
Host: xyz.azurewebsites.net
Connection: Keep-Alive

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8
ETag: 00-00-00-00-00-00-00-76
Vary: Origin,Accept-Encoding
Server: Kestrel
Access-Control-Allow-Credentials: true
Access-Control-Allow-Origin: file://
X-Powered-By: ASP.NET
...
Date: Sun, 24 Jun 2018 05:57:34 GMT
Content-Length: 2216

[{"...."}}

But HEAD didn’t work

HEAD http://xyz.azurewebsites.net/api/portfolio HTTP/1.1
...
X-API-KEY: ABCDEFGHIJKLMNOPQRSTUVWXYZ
Accept-Language: en-NZ
Accept-Encoding: gzip, deflate
If-None-Match: 00-00-00-00-00-00-00-76
Host: xyz.azurewebsites.net
Connection: Keep-Alive

HTTP/1.1 400 Bad Request
Content-Length: 0
Vary: Origin
Server: Kestrel
Access-Control-Allow-Credentials: true
Access-Control-Allow-Origin: file://
X-Powered-By: ASP.NET
...
Date: Sun, 24 Jun 2018 05:59:55 GMT

From the Application Insights logging and RestTest client (which I ran locally and remotely) I could see that the client side code couldn’t access the value of our eTag.  It had to be “exposed”

public void ConfigureServices(IServiceCollection services)
{
   services.AddCors(options =>
   {
      options.AddPolicy("CorsPolicy",
            builder => builder.AllowAnyOrigin()
            .AllowAnyMethod()
            .AllowAnyHeader()
            .WithExposedHeaders("etag")
            .AllowCredentials()
         );
      });
      services.AddMvc();
   }
...

GET then worked

GET http://xyz.azurewebsites.net/api/portfolio HTTP/1.1
...
X-API-KEY: ABCDEFGHIJKLMNOPQRSTUVWXYZ
ETag: 00-00-00-00-00-00-00-76
Accept-Language: en-NZ
Accept-Encoding: gzip, deflate
If-None-Match: 00-00-00-00-00-00-00-76
Host: xyz.azurewebsites.net
Connection: Keep-Alive

HTTP/1.1 200 OK
Transfer-Encoding: chunked
Content-Type: application/json; charset=utf-8
Content-Encoding: gzip
ETag: 00-00-00-00-00-00-00-76
Vary: Origin,Accept-Encoding
Server: Kestrel
Access-Control-Allow-Credentials: true
Access-Control-Allow-Origin: file://
X-Powered-By: ASP.NET
...
Date: Sun, 24 Jun 2018 07:53:41 GMT

[{"...."}}

HEAD then worked

OPTIONS http://xyz.azurewebsites.net/api/portfolio HTTP/1.1
...
Access-Control-Request-Headers: x-api-key,etag
Access-Control-Request-Method: HEAD
Accept-Encoding: gzip, deflate
Content-Length: 0
Host: xyz.azurewebsites.net
Connection: Keep-Alive
Pragma: no-cache

HTTP/1.1 204 No Content
Vary: Origin
Server: Kestrel
Access-Control-Allow-Credentials: true
Access-Control-Allow-Headers: x-api-key,etag
Access-Control-Allow-Origin: file://
X-Powered-By: ASP.NET
...
Date: Sun, 24 Jun 2018 07:57:31 GMT

HEAD http://xyz.azurewebsites.net/api/portfolio HTTP/1.1
...
X-API-KEY: ABCDEFGHIJKLMNOPQRSTUVWXYZ
ETag: 00-00-00-00-00-00-00-76
Accept-Language: en-NZ
Accept-Encoding: gzip, deflate
Host: xyz.azurewebsites.net
Connection: Keep-Alive
Pragma: no-cache

HTTP/1.1 304 Not Modified
Vary: Origin
Server: Kestrel
Access-Control-Allow-Credentials: true
Access-Control-Allow-Origin: file://
X-Powered-By: ASP.NET
...
Date: Sun, 24 Jun 2018 07:57:31 GMT

I had some oddness with releasing code updates which I think was down to caching of pre-flight request responses.
Next steps tidy up the headers etc. and lock the CORS configuration down to expose the minimum necessary required for the application to work.