When you create an Azure SQL Database with the Serverless (pay per second of usage) pricing model there’s an auto-pause option which will pause the DB after a period of inactivity. This option is intended to save you money. One problem with enabling it is connection failures when activity re-starts again as the DB has to go through a cold start.
When you’re connecting to a paused DB from Entity Framework Core you’ll often see an error like below…
An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding ‘EnableRetryOnFailure()’ to the ‘UseSqlServer’ call.
As suggested by the error we can handle this by including EnableRetryOnFailure in our DbContext configuration as shown below…
When the above options are set and the DB is in a paused state the first request of the app will take longer than normal but the user won’t see an error as the DB will be accessible after one or two connect retries.
Testing EnableRetryOnFailure when Azure SQL DB is paused
Unfortunately at the moment there doesn’t seem to be a way to simulate the paused state for Azure SQL Databases so you’ll need to wait an hour (min auto-pause time) to test. You can confirm the DB is in a paused state from the portal…
… and after hitting your app if you’re logging debug and info messages you should see some failed connection attempts (first yellow box below) and finally a successful connection and query execution (second yellow box below) in your logs.
The sample log output below (click for a larger view in a new window) is taken from the App Service Log stream page which allows you to view your application logs in real-time direct from the portal.
If you’ve got this error after scaffolding a controller which uses Entity Framework Core like below…
it means you haven’t registered your DbContext as a service. You need to do this as the scaffolded controller uses dependency injection to inject an instance of your DbContext into its constructor. For example here is a sample scaffolded controller…
MVC is throwing the exception as it cannot find what class implements EFContext when it is attempting to create an instance of the CustomersController class.
To fix this we can amend the scaffolded controllers to create a DbContext instance directly in their constructors but the better way is to simply register the EFContext as a service in the Startup.cs -> ConfigureServices method. We can do this by calling the AddDbContext method as shown below…
The code above will register EFContext as a scoped service which means it is tied to the request which is ideal in a web application. As you can see we can also configure options such as which provider we are using… but we don’t have to do this here, we can defer this to the OnConfiguring method in our DbContext class if we wish.
In Entity Framework when we’re using the code first approach EF will examine our classes and using a defined mapping will create a DB structure based on them. This can result in an inefficient DB design as by default all C# properties of a certain type will be mapped to the same DB column type.
For example in Entity Framework Core 5.0 some default mappings are…
Accepting EF default mappings across a whole DB can result in a lot of wasted space and potentially poorer performance. Thankfully there are a number of ways we can tell Entity Framework what column types we want our C# properties mapped to.
Define DB column types using data annotations
By using data annotations we can define the mappings directly in our classes immediately above each property. This means its very easy to see what type a particular property is mapped to.
From a domain driven design point of view however, data annotations may not be ideal as we’re essentially mixing in implementation details from our DB layer (SQL Server column types) with our domain objects.
As can be seen below data annotations are easy to use.
Another in-class approach is to actually use different data types… so we’d use short where we want a smallint DB column and byte when we want a tinyint DB column etc. This level of granularity is not available for all data kinds though so it will only get you so far.
Define DB column types using fluent API
In Entity Framework Core the fluent API for defining property mappings is available by providing an override of the OnModelCreating method in our DbContext class. Below we can see the fluent API equivalent of the data annotations from above.
Note… I’ve included two mappings for the Decimal_Fee property just to show the new EF Core 5.0 syntax that’s now available for setting the DB precision on relevant properties.
Between data annotations and the fluent API I’d recommend using the fluent API as it allows us to keep all our mapping related code together and perhaps more importantly allows us to keep our domain models free of persistence related clutter.
When using Entity Framework Core and ASP.NET Core MVC we can easily inject a DbContext instance into our controller, repository or service classes as Dependency Injection is built into .NET Core.
The image below (click for a larger view in a new window) shows the steps involved…
– Step 1 – Register the DbContext as a service in the ConfigureServices method in the Startup.cs file. AddDbContext creates a Scoped service, this means it is tied to the request, which is ideal in a web app.
– Step 2 – Inject into and use the service in your consuming class or classes. Many ways to do this but I’ve shown two below. In 2a we inject the specific service and in 2b we inject an instance of IServiceProvider which can be useful if we have many services required in addition to a DbContext.
I recommend injecting the specific service as this expresses intent more clearly and makes it more obvious to other developers what the dependencies are. Indeed the IServiceProvider approach is an example of the service locator pattern which many consider to be an anti pattern but make your own mind up… every pattern can be turned into an anti pattern if its used unwisely.
Do I inject DbContext into controller, service or repository classes?
Note… in the example above I’ve injected directly into the controller class. I’m just doing this to keep things simple for the purposes of this post. In real apps I wouldn’t recommend this as it means your EF logic resides in your MVC controller action methods meaning you couldn’t re-use it in another web app, console app etc.
Whether or not to use the repository pattern with Entity Framework and therefore inject the DbContext into repository rather than service classes is a whole different question. I don’t use it in most EF apps but this topic has been debated online for years and everyone has a different opinion on it so please do your own research here.