Scalable and Performant ASP.NET Core Web APIs: Database Round Trips

This is another post in a series of posts on creating performant and scalable web APIs using ASP.NET Core. In this post we’ll focus on database round trips in our data access code …

Database round trips are expensive. The bigger the distance between where our data access code is deployed and our deployed database, the more expensive that round trip is. So, we want to keep the trips from our data access code to the database to a minimum.

The example in a previous post on ORMs is a an example of reducing 4 trips to a single trip. This highlights that Dapper gives us fine grain control to really reduce our database round trips.

N+1 Problem

Let’s have a look at a classic problem – the “N+1” problem …

First, here’s our database model. In summary, we have contacts that can have multiple activities stored against them.

Database model

Here’s our controller action method for GET /api/contacts that returns a collection of contacts including any associated activities:

We can see straight away that the endpoint isn’t fast:

N+1 Problem

If we use SQL profiler, we can see there is a call to get the Contact record and then “N” calls to get the Activity records for each contact – the classic “N+1” problem!

SQL Trace of N+1 Problem

To improve the performance of this endpoint, we can use dappers “multi-mapping” feature to grab and map the data in a single round trip:

We can see in Postman that the endpoint responded much quicker:

Fast contacts and activities

We can also see in SQL profiler that only a single database call was made:

Fast contacts and activities SQL Trace

Multiple Result Sets

So, Dapper helps us reduce round trips when a controller action method is fetching related data with its “multi-mapping” feature. But what about cases when the API needs to return data from unrelated tables? How can we fetch unrelated data in a single database call?

As an example, let’s look at the implementing a single controller action for some contact “lookup” values that are needed for a screen in an app that is used by our API. Here are the database tables:

Database Lookup model

… and here’s an example of the response body we expect:

i.e. We have 3 unrelated tables that we need to query data for and we’d like to do this in a single database hit. The good news is that we can use dapper’s multi-recordset feature to do this:

If we hit this endpoint, it’s nice and quick:
ulti-Recordset Result

Let’s check in SQL Profiler that this does only hit the database once:
Multi-Recordset SQL Profile


Scalable and Performant ASP.NET Core Web APIs: Database Connections

This is another post in a series of posts on creating performant and scalable web APIs using ASP.NET core 2.0. In this post we’ll focus on database connections, making sure the connection pool is used efficiently …


We’ve chosen Dapper as our ORM in a previous post. So, we are responsible for creating SQL connections for our queries.

Some problematic code

We have a very dirty controller action method below for GET /api/contacts/{contactId}. This code has a serious problem with it – we catch and return the exception with a “200” status code so that we can easily see the problem. Obviously this isn’t production code!

If we hit the endpoint in Postman, it responds fine. It we hit it again, it’s still fine … and again … still fine. It’s pretty quick as well.

Connection problem - 1st try

So, what’s the problem? Let’s load test the endpoint …

We’re going to use Web Surge for the load test and hit the endpoint for 30 seconds with 8 threads. If we watch the Output window, – the test starts fine and then grinds to a halt. In fact we start to get timeouts.

Connection load test
Connection timeout

If we try to hit the same endpoint again in Postman, we get the following, which gives us more information:
Connection timeout in Postman

So, there is a problem getting a connection from the connection pool because all the connections are in use. If we look at our code again, we aren’t closing our connection. So, the connection stays open until the garbage collector closes it. This means that the next request that comes in won’t be able to use that connection from the connection pool – it will have to take a different connection. This process continues until there are no connections left in the pool … which is where we get the timeout error.

The fix

So, we can see that connections are limited resources and should only be open for when it is in use – i.e. we should close the connection straight after the query has finished.

A quick fix attempt is:

… but what if the query errors? We’ll be left with an open connection again …

A better implementation is to wrap the query in a Using block. This results in our connection automatically being closed and disposed for us – even if an exception occurs.

If we run the same load test again, we find that our endpoint continues to operate fine for the full 30 seconds:

Connection load test fix


So, the key point to remember is that connections are precious resources and should only be open when we need them. As a rule of thumb we should create and open the connection immediately before executing the required database query and close the connection immediately after the query has finished and we have captured the results. The Using keyword is a robust way of ensuring our connection is always closed and disposed and returned to the connection pool.

We’ll remain on the topic of data access for our next post – this time looking at database round trips.

Don’t forget to subscribe to the rest of this series in the footer of this page!

Building Microservices with .NET Core 2.0 – Book Review

I’ve been doing a lot of work on ASP.NET Core recently and wanted to really get my head into microservices which seems a hot topic at the moment. So, I decided to purchase Building Microservices with .NET Core 2.0 and give it a read. This post is a short review on this book.

Building Microservices with .NET Core 2.0


Chapter 1 – Introduction

This chapter goes through what microservices are, where the term came from and why the architecture is great! The chapter goes through how the architecture differs from a monolithic and a Service-Oriented Architecture (SOA) architectures.

Chapter 2 – Implementing Microservices

This chapter goes through high level factors when breaking a system up into microservices and tactics for doing so. It covers the characteristics of what a good serive is in order to acheive vertical isolation. It goes through a nice example implementation using Visual Studio 2017, ASP.NET Core 2.0, EF Core and SQL Server.

Chapter 3 – Integration Techniques and Microservices

The third chapter focuses on how the microservices communicate with each other in addition to how external systems can communicate with the system as a whole. The chapter goes through synchronous and asynchronous communication types as well as the concept of a API gateway. In terms of code, it goes through an example of implementing and interacting with a service bus queue in Azure.

Chapter 4 – Testing Microservices

This chapter starts by identifying the challenges of testing microservices. It then goes on to how to tackle those challenges covering all the areas in the classic testing pyramid. The chapter ends with going through a working example of different types of tests using Visual Studio 2017, ASP.NET Core 2.0, xUnit and moq.

Chapter 5 – Deploying Microservices

Chapter five starts by reminding us of the challenges of deploying monolithic systems. It then goes on to cover best practices when deploying microservices focusing on isolation which is a key factor for success. It also covers how microservices fit in with continuous integration and continuous delivery. The chapter finishes with a step by step example of deploying a microservice using Docker.

Chapter 6 – Securing Microservices

This chapter starts by identifying how microservice security is different to securing a monolithic system. It goes on to cover how we can secure microservices using OAuth 2.0 and OpenID Connect. The working example in the chapter goes through securing a microservice with Azure AD.

Chapter 7 – Monitoring Microservices

This chapter goes through how to add instrumentation and telemetry to microservices. As you might imagine, debugging a transaction spanning multiple microservices is challenging – this chapter covers approaches for making this easier.

Chapter 8 – Scaling Microservices

Chapter 8 explores how we can scale microservices which is a key advantage of the microservices approach. It covers how we can design our microservices to scale and make them fault tolerant.

Chapter 9 – Introduction to Reactive Microservices

This chapter starts by explaining what a “reactive” microservice is and goes through a working ASP.NET Core example.

Chapter 10 – Creating a Complete Microservice Solution

The final chapter starts by summarising some of the key microservices concepts covered in the previous chapters and goes through a high level design of sample bookstore system.


Personally I thought the book was really good. It started from the basics and was easy to understand – the diagrams thoughout the book were fantastic for my understanding!

I liked how the chapters went through the theory followed by relevant realistic code examples.

The book is a quick read as well at just under 300 pages.

So, if you’re a .NET developer and interested in learning about microservices then this book is a great start point.

Scalable and Performant ASP.NET Core Web APIs: SQL Server Isolation Level

This is another post in a series of posts on creating performant and scalable web APIs using ASP.NET core 2.0. In this post we’ll have a little look at isolation levels in SQL Server which is something that is often not thought about … until we hit performance issues …

By default, SQL Server uses the “Read Committed” isolation level and by default this blocks reads whilst updates take place. This can lead to scalability issues if we have lots of users reading data in addition to users writing data – particularly when the writes are heavy.

Let’s look at a simple example of simulating some users updating a contact record whilst other users get contact records.

We’ll start of with getting a benchmark for users just getting contact records. Here’s our Dapper data access code to get a contact record:

Here’s a snipet of results from our load test on the API endpoint:

GET benchmark

On to our data access code for updating a contact record … We’ve simulated a long database write by using a Thread.Sleep resulting in the updating taking > 1 sec.

Ok, lets load test some users updating a contact record whilst other users are getting the contact record:

GET requests blocked

We see that the GET requests take far longer than they were previously because they are being blocked by the PUT requests.

So, what can we do? How can we resolve this? It is tempting to use the “Read Uncommitted” isolation level. This appears to resolve the problem ….

… but what if the SQL did more database writes after the UPDATE on the Contact table and in the process errored and rolled back? We’d be potentially reading and returning incorrect data!

There is a much simpler solution … We can change the “Read Committed” isolation level behaviour to not block and instead read the current committed data. We change this on the database using the following SQL command:

So, no code changes – sweet!

If we now do the same load test again, the GET requests are much faster because they are not being blocked by the the PUT requests:
GET requests faster

In summary, having READ_COMMITTED_SNAPSHOT ON can generally lead to better performance without losing any accuracy in the data.

Don’t forget to subscribe to the rest of this series in the footer of this page!

Scalable and Performant ASP.NET Core Web APIs: ORM Choice

This is another post in a series of posts on creating performant and scalable web APIs using ASP.NET Core. In this post we’ll start to focus on data access within our API which is the often the cause of performance bottlenecks. We’ll start off with chosing our object relationship mapping framework / library (ORM)

Be careful with EF Core

Entity Framework Core nicely encapsulates the database details from developers. However this approach does carry some performance overheads and gotchas.

Let’s look at an example. Here’s our database model:

Database Model

We have a controller action method /contacts/{contactId} that returns a contact record.

Here’s the SQL Profiler trace:

EF Core SQL Profile - Example 1

Where’s the WHERE clause!? EF Core has fetched all the data in the Contact table too early because of the .ToList(). If we remove the .ToList(). EF Core behaves a little more as we expect:

EF Core SQL Profile - Example 2

Let’s add some more requirements for our action method … We now want to include the primary address, primary email address and primary phone number in /contacts/{contactId}.

The implementation is below. We’ve been good EF Core developers and used .AsNoTracking() so that no unnecessary change tracking occurs. We’ve also been specific about what fields we want to retreive so that EF Core doesn’t fetch more data than we need.

Here’s the SQL Profiler trace:

EF Core SQL Profile - Example 3

We have 4 calls to the database when a single SQL statement could have done the job.

So, with EF core, we need to know what we are doing and be really careful if we are implementing an API on top of a decent sized database. There are also cases when EF Core is going to generate inefficient SQL or round trip to the database multiple times – that’s just how it works!

Here’s a great post on other performance gothas in Entity Framework. It’s not specifically about EF Core but many of the points do apply.

Dapper helps build performant data access code

Dapper is a micro ORM that doesn’t abstract the SQL from us. In Dapper, we use regular SQL to access the database. Dapper simply maps the data to our c# objects.

Here’s the data access code using Dapper for the example above.

Here’s the SQL Profiler trace confirming only 1 hit on the database:
Dapper SQL Trace

So, our ORM choice for the rest of this series on creating performant and scalable web APIs using ASP.NET core 2.0 is Dapper. We’ll continue looking at data access in our next post – this time focusing on isolation levels and the impact on performance.

Don’t forget to subscribe to the rest of this series in the footer of this page!

Scalable and Performant ASP.NET Core Web APIs: Load Testing

Load Testing

This is another post in a series of articles on creating performant and scalable web APIs using ASP.NET core 2.0. In this post we’ll focus on tools that can help us load test our API to ensure it’s going to perform and scale when it goes into production. Performance and scalability issues are much easier and quicker to resolve before our API has gone into production, so, it’s worth testing our API under simulated demand before it gets there.


WebSurge is a load testing tool for APIs behind the firewall or in the cloud that is really simple and quick to use.

During development, when we think our API is nearly complete, we can use WebSurge to determine how many requests per second our API can handle. So, this is not testing specific user scenarios under load, this is just testing a single API end point under load to see how many requests per second it can handle.


This video gives a great overview on WebSurge.

We’ll use WebSurge frequently during this series of blogs to determine the improvement we can make by doing different things.

Visual Studio Load Testing

If we are lucky enough to have the Enterprise version of Visual Studio, we can use the load testing tool within Visual Studio. It is more flexible than WebSurge but it is more time consuming to write the tests. This is perhaps a good choice if we are writing tests that simulate specific user scenarios under load.

In order to create tests, we first add a “Web Performance and Load Test” project to our solution.

When producing the actual tests we could record the test using a IE plugin that integrates with Visual Studio’s load testing tool. This is great for load testing traditional server driven web applications. However, for testing web APIs, creating a c# unit test is much simpler and gives us a lot of flexibility.

Unit Test

Now that we have a test, we can put this under load. We do this by right clicking on the project and clicking “Add > Load Test …”. A wizard will help us create the load test. We will see that we can use a cloud based load or a load generated from our PC (and potentially other PCs in our infrastructure). The wizard lets us configure lots of stuff such as the test duration, the think times (if we are trying to simulate realistic workloads), the number of users and obviously the tests to run and how they are mixed up during the load. After we have completed the wizard, we can run the load test by clicking the “Run Load Test” icon.

Run Load Test

After the load test has finished running, we get a summary of the results. The statistic that I’m most interested in is “Tests / Sec” which is similar to “Requests / Sec” in WebSurge.

Load Test Results


BenchmarkDotNet is a low level tool to help us understand how our code will run at scale. It’s particularly good at comparing different pieces of code that give the same result to determine which one is the most efficient.

To do this we need to put our code in a .NET Core console app and bring in the BenchmarkDotNet nuget package.

As an example, let’s test the fastest method of iterating through a list to find an item. We’ll compare a classic for loop, a foreach loop and a FirstOrDefault() LINQ statement.

The functions to be tested need to have the [Benchmark] attribute. We simply call BenchmarkRunner.Run() in Main to invoke the test.

To simulate what would happen in production, we should build the console app in “release” mode and call it from the command line:

Here’s the results:

… the classic way is always best!

So, now we’ve got the tools we need to test and profile our ASP.NET Core Web API. In the next post we’ll get into the actual code, starting with our data access code.

Don’t forget to subscribe to the rest of this series in the footer of this page!

Scalable and Performant ASP.NET Core Web APIs: Profiling and Monitoring


This is the 2nd post in a series of articles on creating performant and scalable web APIs using ASP.NET Core 2.0. In this post we’ll focus on tools that can help us profile and monitor our API so that we can spot any performance and scalability issues before our customers do.

Often, like most bugs, the earlier in the development cycle we find a performance or scalability problem, the quicker and easier it is to fix. So, it is important to make use of these tools from the start of the development cycle as well as when the API is in production.

During our dev cycles, as well as checking we get the right status code, response body, … we should check the duration of the call and the size of the response. We’ll then hopefully spot if the API is slowing down during the development phase.

Below is the popular Postman tool, giving us the duration and size of the response.


Development Profiling Tools

Apart from keeping an eye on the duration and size of the API calls, what else can we do? What other tools are there in the development phase to give us confidence that the API is going to perform and scale well?

Stackify Prefix

Stackify Prefix is a great free profiling tool that can be used during the development of an ASP.NET Core Web API. It tracks web requests and database queries and we can even wire it up to Serilog. It’s really useful to have this on a 2nd screen as we build out our API.

To get started with Prefix and profile our API, first download Prefix and then bring in the following nuget package:

We then need to add the Stackify middleware before the MVC middleware. It also needs to come before any exception logging middleware as well so that the exceptions appear in Prefix:

If we want to include the Serilog logs in Prefix, we need to bring in the following nuget package:

… and add the following bits in appSettings.json:

To run prefix, we need to click on the icon in the task bar, enable it and then open it in a browser:

Run Prefix

If we then run our ASP.NET Core Web API in Visual Studio, Prefix will start to profile our API. We will see the requests, database queries and other information we’ve written to Serilog:



Application Insights in Visual Studio

Application Insights is Mircosoft’s fully fledged Application Performance Monitoring (APM) tool (we’ll come on to APMs later in this post). We can run this locally in Visual Studio 2017 to profile the API during development as an alternative to Stackify Prefix. It’s convenient because we are already likely using Visual Studio to develop the API.

To add this to a Visual Studio project, we right click on project in Solution Explorer and click “Add > Application Insights Telementry …”. We then click the “Start Free” button and then click “Or just add the SDK to try local only mode” at the bottom of the screen. The Application Insights SDK will then be added to the solution.

Application Insights, will automatically track our web requests – we don’t need to add any middleware. Unlike, Prefix, it doesn’t track database queries – we’d need to track these in our logger and wire Application Insights up to our logger (it is automatically wired up to the standard ILogger).

After we’ve added Application Insights to our solution in Visual Studio, we can view the trace information by going to “View > Other Windows > Application Insights Search”. If we enter a date and time in the “From” and “To” inputs and click the search icon, we will hopefully see trace information for the API calls in the bottom half of the screen. The actual individual trace items are in the middle column. If we click on a trace item, we get additional details of the item to the right of it. The element that I find really useful in the “Track Operation” section on the bottom right of the screen. This gives us an overview of the API call and we can quickly see what bits are slow and need further investigation.

Application Insights

“Application Insights Trends” is great for giving us an overview of the trace information. We can access this in Visual Studio via View > Other Windows > Application Insights Trends.

Personally, I prefer the UX of Prefix, but Application Insights is worth a look.

SQL Server Profiler

As the name suggests, SQL Server Profiler traces the SQL statements executed on a SQL Server database. This is a tool that comes with SQL Server and obviously is only useful if our API uses SQL Server for its storage! If we are using a different database then there is likely to be an equivalent profiler.

We can use this tool if our higher level profiling tool (like Prefix or an APM) has pointed to a problem in the data access code. This gives us a clear understanding of what SQL is being executed along with the associated costs.

This tool can also give us all the activity from the SQL Server – it may not be our API that is problematic – it may be some other process that is hogging the resources.

As well as the duration of each SQL statement, we can get other useful performance related information like the amount of CPU in milliseconds used by the statement, the number of page read I/Os caused by the statement and the number of page write I/Os caused by the statement.

SQL Profiler

Visual Studio Memory Profiler

Visual Studio has a set of low level profiling tools. The one that I find most useful is the memory profiler which lets us take snapshots of the managed and native memory heap and drill in to the differences between the snapshots. This can help us find memory leaks or just inefficient use of memory in our code.

We can switch the memory profiler on by clicking “Debug / Windows / Show Diagnostic Tools”.

Visual Studio Memory Profiler

We won’t be using this every day – just when our higher level profiling tool points to a problem in a specific area of code that we want to profile a little deeper.

Application Performance Monitoring (APM) Tools

APMs are primarily used to profile our code in production with regards to performance. However, these tools are also useful in the QA environment – particularly if we are running a load test and want to measure how different parts of the API perform.

Stackify Retrace

Stackify Retrace is a service that allows us to track the same as Stackify Prefix tracks, but it tracks it in production. It also tracks other metrics on the server such as CPU usage. Retrace can also notify us when certain events happen – e.g. when CPU usage > 90%.

In addition to adding the same middleware to our API code as we did for Prefix, we need to register with Stackify and download and install an agent. The agent will send the profile information to Stackify for us to view in their portal.

As with most APMs, this is a paid service which starts from $10 per month at the moment.

Application Insights

As mentioned before, Application Insights is Microsoft’s fully fledged APM tool. As well as wiring up locally, we can wire this up to the Azure Application Insights service. This allows us to view the information in the Azure Portal.

Azure Insights

The nice thing about this APM is that there is a free usage tier for up to 1GB worth of data. Worth a look – particularly if our API is hosted in Azure.

So, there we have a range of high level and low level profiling tools that work well with ASP.NET Core 2.0. In the next post we’ll look at load testing tools …

Don’t forget to subscribe to the rest of this series in the footer of this page!

Scalable and Performant ASP.NET Core Web APIs: Introduction

Performant and scalable web APIs

Over the next month or so I’ll be posting a series of posts on creating performant and scalable web APIs using ASP.NET Core 2.0.

Performance is how fast our API can deal with a single request and make a response. Scalability is the amount of concurrent requests our API can deal with before it slows down significantly.

ASP.NET core itself is very focused on performance, which makes it a great choice for building APIs that perform well. v2.0 of .NET Core gave us general performance improvements on commonly used areas such as many of the collection classes, LINQ, compression and text processing. You can read more about the improvements here. In order for us to create APIs that scale well as more users use the API, we’ll need to architect our API well with usage growth in mind.

The series will start with how to profile and load test our API from a performance point of view. This is really important if we want to know we have a performance problem well before our customers tell us!

We’ll then move on to data access. ORMs are very popular these days but they can be the cause of performance issues …

We’ll look at paging, filtering and searching which improve the consumers experience of our API as well as hopefully having a positive performance impact.

We’ll have a good look at caching … allowing the client to cache responses as well as having a gateway cache on our server.

We’ll move on to allowing our API to work asynchronously to see how that effects performance and scalability.

We’ll look at a Microservices architecture and how that can help scalability.

We’ll end up on the topic of large volume imports – a common requirement in line of business applications that store lots of data.

Ensure you don’t miss any of these posts by subscribing to this blog in the footer of this page!

Controlling our ASP.NET Core Serilog Log

In a previous post we built up a Serilog log full of useful information. In this post we’re going to see if we can control exactly what is logged, without a software deployment. By default, we want our logs to be fairly light and have the ability to increase the amount of logging when we are diagnosing a problem.

So, we have an ASP.NET Core Web API in production, hosted in IIS. We also have the middleware running from this post that gives us additional properties in our log like “RequestPath” and “UserName”. Here’s our appSettings.json:

If we GET /api/contacts/f7d10f53-4c11-44f4-8dce-d0e0e22cb6ab we get:

Default log

If we increase the level to “Error”, we no longer get the “Information” entries. We will only get exceptions logged in our log. We don’t need to restart the app pool in IIS – the change kicks in as soon as we save appSettings.json. Nice!

If we decrease the level to “Debug”, we get a lot more log entries.

Debug Log

So, switching to the “Debug” level is going to be valuable when we are diagnosing a problem. However, that’s a lot of log entries from all over our web API – can we be more specific on what we want to log? That’s where filter expressions come in …

In order to use filter expressions, we need to reference the following nuget:

Having changed the MinimumLevel to “Debug”, let’s try to add a filter so that we only get “Debug” entries on GET api/contacts/{contactId} whilst still getting errors logged. Let’s change appSettings.json to:

Unfortunately for the filter to kick in, we do need to restart the app pool in IIS. After doing this, we do get all the log entries for that particular path:

Filtered Log

The filter expression uses a powerful SQL like syntax. The filter expression docs give the full details.

Another example of using a filter expression to control our logs is if a particular user is having a problem. The following appSettings.json will give all the log entries for “Carl” as well as any other errors.

Cool stuff!

Instrumenting Dapper Queries in ASP.NET Core

In my last post we touched on what we get from EF Core in our Serilog log entries. But what if we are using dapper? How can we trace the SQL and get query execution times?

We already have our Seriog outputting to SQL Server and our middleware from the last post.

Unfortunately, we don’t get any logging out of the box for dapper like we do with EF Core – so, we need to do a bit of work.

We’ll create a class that wraps the dapper methods we use and we’ll instrument those methods. We turn stats on for the connection and get them after the query has finished, passing them to a central method that does the logging.

The LogInfo() method logs the SQL, parameters and useful stats to Serilog. We could have just logged all the stats by doing .ForContext("Stats", stats, destructureObjects: true) but the log output wouldn’t have been as neat.

The AddAdditionalInfoToException() method makes sure we have the full SQL and parameters in the exception that has occurred ensuring that they are together in our log.

Now, to use our DapperWrap class, we need to register it in Startup.

We can then inject DapperWrap where we need it. I’ve used it in a simple controller below:

We then get the following in our serilog log:

Dapper log

… with the following properties:

For an exception we get:

Dapper exception

… which includes the full SQL and parameter in the properties: