.NET Core web API + Dapper – Pagination

Pagination for payload size reduction

This controller method returns a limited number of records(pageSize) from a position(pageNumber) in a database query resultset to reduce the size of the response payload.

The SQL command uses the ROWS FETCH NEXT … ROWS ONLY syntax, The use of this approach is not really highlighted in official developer documentation (though I maybe missing the obvious).

There is some discussion in the ORDER BY clause syntax documentation.

Using OFFSET and FETCH to limit the rows returned

We recommend that you use the OFFSET and FETCH clauses instead of the TOP clause to implement a query paging solution and limit the number of rows sent to a client application.

Using OFFSET and FETCH as a paging solution requires running the query one time for each “page” of data returned to the client application. For example, to return the results of a query in 10-row increments, you must execute the query one time to return rows 1 to 10 and then run the query again to return rows 11 to 20 and so on. Each query is independent and not related to each other in any way. This means that, unlike using a cursor in which the query is executed once and state is maintained on the server, the client application is responsible for tracking state

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

	try
	{
		var parameters = new DynamicParameters();

		parameters.Add("@PageNumber", request.PageNumber);
		parameters.Add("@PageSize", request.PageSize);

		using (SqlConnection db = new SqlConnection(this.connectionString))
		{
			response = await db.QueryAsync<Model.StockItemListDtoV1>(sql: @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM[Warehouse].[StockItems] ORDER BY ID OFFSET @PageSize * (@PageNumber-1) ROWS FETCH NEXT @PageSize ROWS ONLY", param: parameters, commandType: CommandType.Text);
		}
	}
	catch (SqlException ex)
	{
		logger.LogError(ex, "Retrieving list of StockItems with PageSize:{0} PageNumber:{1}", request.PageSize, request.PageNumber);

		return this.StatusCode(StatusCodes.Status500InternalServerError);
	}

	return this.Ok(response);
}

This sample also uses the FromQuery attribute to populate a Data Transfer Object(DTO) with request query string parameters

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

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

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.

http://localhost:36739/api/StockItemsPagination/

The result is

ols.ietf.org/html/rfc7231#section-6.5.1″,”title”:”One or more validation errors occurred.”,”status”:400,”traceId”:”00-917b6336aa8828468c6d78fb73dbe446-f72fc74b22ce724b-00″,”errors”:{“PageSize”:[“PageSize must be present and greater than 0”],”PageNumber”:[“PageNumber must be present and greater than 0”]}}

http://localhost:36739/api/StockItemsPagination?pageSize=10

{“type”:”https://tools.ietf.org/html/rfc7231#section-6.5.1&#8243;,”title”:”One or more validation errors occurred.”,”status”:400,”traceId”:”00-dd5f2683c6d7dc4a84bb04949703fc34-0c3658e2e54c2648-00″,”errors”:{“PageNumber”:[“PageNumber must be present and greater than 0”]}}

https://localhost:36739/api/StockItemsPagination?pageSize=10

The result is

{“type”:”https://tools.ietf.org/html/rfc7231#section-6.5.1&#8243;,”title”:”One or more validation errors occurred.”,”status”:400,”traceId”:”00-63f591ee3bfdc7418a83afbdba2faf7f-3d2ea994eb0c5c49-00″,”errors”:{“PageSize”:[“PageSize must be present and greater than 0”]}}

The amount of code can be reduced a bit further by dropping the dynamic parameter and passing the StockItemListDtoV1 object is as a parameter.

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

	try
	{
		using (SqlConnection db = new SqlConnection(this.connectionString))
		{
			response = await db.QueryAsync<Model.StockItemListDtoV1>(sql: @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM[Warehouse].[StockItems] ORDER BY ID OFFSET @PageSize * (@PageNumber-1) ROWS FETCH NEXT @PageSize ROWS ONLY", param: request, commandType: CommandType.Text);
		}
	}
	catch (SqlException ex)
	{
		logger.LogError(ex, "StockItemsPagination exception retrieving list of StockItems with PageSize:{0} PageNumber:{1}", request.PageSize, request.PageNumber);

		return this.StatusCode(StatusCodes.Status500InternalServerError);
	}

	return this.Ok(response);
}

I use both approaches, for example if database fields or parameters have quite a different naming convention to C# properties (with query DTOs then can often be fixed with attributes) I would use the explicit approach .The later approach also had slightly better code metrics

Metrics for version with DynamicPararmeters
Metrics for version with DTO parameters

.NET Core web API + Dapper – Asynchronicity

Asynchronous is always better, yeah nah

For a trivial controller like the one below the difference between synchronous and asynchronous calls is most probably negligible, the asynchronous versions may even be slightly slower. ASP.NET Core web API applications should be designed to process many requests concurrently.

The Dapper library has the following asynchronous methods

These asynchronous methods enable a small pool of threads to process thousands of concurrent requests by not waiting on blocking database calls. Rather than waiting on a long-running synchronous database call to complete, the thread can work on another request.

namespace devMobile.WebAPIDapper.Lists.Controllers
{
	[ApiController]
	[Route("api/[controller]")]
	public class StockItemsAsyncController : ControllerBase
	{
		private readonly string connectionString;
		private readonly ILogger<StockItemsAsyncController> logger;

		public StockItemsAsyncController(IConfiguration configuration, ILogger<StockItemsAsyncController> logger)
		{
			this.connectionString = configuration.GetSection("ConnectionStrings").GetSection("WideWorldImportersDatabase").Value;

			this.logger = logger;
		}

		[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.QueryAsync<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);
		}
	}
}

This sample controller method returns a small number of records (approximate 230) in one request so performance is unlikely to be a consideration. A controller method which returns many (1000s or even 10000s) records could cause performance and scalability issues. In a future post I will add pagination and then do some stress testing of the application to compare the different implementations.

.NET Core web API + Dapper – Failure

It will break

With no error handling the code was a bit fragile so I modified the program.cs file and added support for the built in logging and Debug provider. To reduce the amount of code in the controller I have also moved the DTO to a separate file in the “models” folder.

namespace devMobile.WebAPIDapper.Lists
{
	public class Program
	{
		public static void Main(string[] args)
		{
			CreateHostBuilder(args).Build().Run();
		}

		public static IHostBuilder CreateHostBuilder(string[] args) =>
			 Host.CreateDefaultBuilder(args)
				.ConfigureLogging(logging =>
				{
					logging.ClearProviders();
					logging.AddDebug();
				})
				.ConfigureWebHostDefaults(webBuilder =>
				{
					webBuilder.UseStartup<Startup>();
				});
	}
}

To test the exception handling I “broke” the Dapper query embedded SQL.

namespace devMobile.WebAPIDapper.Lists.Controllers
{
	[Route("api/[controller]")]
	[ApiController]
	public class StockItemsFailureController: ControllerBase
	{
		private readonly string connectionString;
		private readonly ILogger<StockItemsFailureController> logger;

		public StockItemsFailureController(IConfiguration configuration, ILogger<StockItemsFailureController> logger)
		{
			this.connectionString = configuration.GetSection("ConnectionStrings").GetSection("WideWorldImportersDatabase").Value;

			this.logger = logger;
		}

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

			try
			{
				using (SqlConnection db = new SqlConnection(this.connectionString))
				{
					response = db.Query<Model.StockItemListDtoV1>(sql: @"SELECTx [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);
		}
	}

The controller failed and the following error was displayed in the Visual Studio output window

devMobile.WebAPIDapper.Lists.Controllers.StockItemsFailureController: Error: Retrieving list of StockItems

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'as'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in /_/Dapper/SqlMapper.cs:line 1055
   at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in /_/Dapper/SqlMapper.cs:line 1083
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 725
   at devMobile.WebAPIDapper.Lists.Controllers.StockItemsFailureController.Get() in C:\Users\BrynLewis\source\repos\WebAPIDapper\Lists\Controllers\03.StockItemsFailureController.cs:line 53
ClientConnectionId:f37eb089-a560-406d-8c24-cf904bb17d8a
Error Number:156,State:1,Class:15
The program '[16996] iisexpress.exe: Program Trace' has exited with code 0 (0x0).
The program '[16996] iisexpress.exe' has exited with code -1 (0xffffffff).

In a couple of future posts I will add support for Log4Net, nLog, Serilog and a couple other libraries.

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.