Faster MS SQL database existence checking with Entity Framework Core and Entity Framework
Bit out of frustration when demoing features newest versions of Entity Framework Core, I’ve found out a new feature of SqlClient (SqlConnection to be precise) that’s available since .NET 4.5.1 that might affect speed when working often without existing database.
What I’m talking about is Idle Connection Resiliency feature (oddly I’ve found only PHP version). For our discussion the important part is that SqlClient performs retry with a small delay when opening the connection fails. This is usually good. But there are at least two places, when using Entity Framework Core or Entity Framework, where this is not desired.
One is when executing Migrations and not having a database at all. The other is when using EnsureDeleted and EnsureCreated combination, like when executing (integration) tests. Luckily the solution is pretty easy. Simply add ConnectRetryCount=0 to the connection string and you’re good to go. This will turn off the Idle Connection Resiliency feature, thus it’s not without, maybe, undesired side-effect.
Let’s see this in action. Below is simple code that uses EnsureDeleted and EnsureCreated (the ElapsedRestart, as the name suggests, returns elapsed time and restarts the stopwatch).
using (var db = new MyContext())
{
var sw = Stopwatch.StartNew();
Console.WriteLine("Deleting");
db.Database.EnsureDeleted();
Console.WriteLine(sw.ElapsedRestart());
Console.WriteLine("Creating");
db.Database.EnsureCreated();
Console.WriteLine(sw.ElapsedRestart());
Console.WriteLine();
Console.WriteLine("Deleting");
db.Database.EnsureDeleted();
Console.WriteLine(sw.ElapsedRestart());
Console.WriteLine("Creating");
db.Database.EnsureCreated();
Console.WriteLine(sw.ElapsedRestart());
}
Without the ConnectRetryCount=0 we can roughly 10 seconds delay first during EnsureDeleted, because the database does not exist and then in second batch during EnsureCreated, for the same reason.
Deleting
00:00:11.3410613
Creating
00:00:00.4671336
Deleting
00:00:00.0261642
Creating
00:00:10.2167985
Adding the ConnectRetryCount=0 makes it flow without any delays.
Deleting
00:00:00.7322148
Creating
00:00:00.4016524
Deleting
00:00:00.0207446
Creating
00:00:00.1830252
At the moment this cannot be changed programmatically, thus there’s not much Entity Framework Core or Entity Framework can do. But it’s already tracked (CoreFX, EFCore, EF6), so there’s a hope.
