EF Core Migrations – creating Indexes Online depending on SQL Server edition

I recently hit the classic case of trying to add Indexes to a large table. Whilst Entity Framework Core supports creating Indexes online during migrations, not all versions of SQL Server support this.

In the case that your migration contains the code:

 migrationBuilder.CreateIndex(
                 name: "IX_TableName_ColumnName",
                 table: "TableName",
                 column: "ColumnName").Annotation("SqlServer:Online", true);

This will fail hard on SQL Server Express, which you are likely using for development locally, with the error message “Online index operations can only be performed in Enterprise edition of SQL Server.”. Online index operations are available in Enterprise or luckily in my case, Azure SQL.

Whilst there is not a “feature flag” to detect the support of Online index creation, you can execute the following query to detect the edition of SQL Server your app is running on.

SELECT SERVERPROPERTY(‘EngineEdition’)

Which returns 3 for Enterprise edition or 5 for SQL Azure (full list here).

EF Core has removed the ability to easily execute scalar queries so you’ll need a small extension method:

public static class SqlQueryExtensions
    {
        public static T ExecuteScalar<T>(this DbContext context, string rawSql,
         params object[] parameters)
        {
            var conn = context.Database.GetDbConnection();
            using (var command = conn.CreateCommand())
            {
                command.CommandText = rawSql;
                if (parameters != null)
                    foreach (var p in parameters)
                        command.Parameters.Add(p);
                conn.Open();
                return (T) command.ExecuteScalar();
            }
        }

 

And then you can set a public static property on your migration before calling DbContext.Migrate():

var dbEngineVersion = dbContext.ExecuteScalar<int>("SELECT SERVERPROPERTY('EngineEdition')");
MyMigrationName.UseOnlineIndexCreation = dbEngineVersion == 3 || dbEngineVersion == 5;
dbContext.Database.Migrate();


public partial class MyMigrationName : Migration
{
    public static bool UseOnlineIndexCreation { get; set; }

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        if (UseOnlineIndexCreation)
        {
            migrationBuilder.CreateIndex(
             name: "IX_TableName_ColumnName",
             table: "TableName",
             column: "ColumnName").Annotation("SqlServer:Online", true);
        }
        else
        {
            migrationBuilder.CreateIndex(
             name: "IX_TableName_ColumnName",
             table: "TableName",
             column: "ColumnName");
            }

        }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(
            name: "IX_TableName_ColumnName",
            table: "AuditTrail");
    }
}

Now your Index will be created Online on editions of SQL Server that support it.

 

Missing StoreKey PFX certificates when building a Visual Studio 2019 UWP project

I came across an interesting issue updating my UWP app to Visual Studio 2019 and a new Azure DevOps pipeline. “Associate with Store” no longer adds password-less PFX files named *TemporaryKey.pfx and *StoreKey.pfx to your project to sign your store submissions – instead in VS2019 it now adds the certificates to your local user store only.

Which means when it comes to build, you get errors like

C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\Microsoft\VisualStudio\v15.0\AppxPackage\Microsoft.AppXPackage.Targets(4353,5): Error APPX0102: A certificate with thumbprint '' that is specified in the project cannot be found in the certificate store. Please specify a valid thumbprint in the project file.
C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\Microsoft\VisualStudio\v15.0\AppxPackage\Microsoft.AppXPackage.Targets(4353,5): Error APPX0107: The certificate specified is not valid for signing. For more information about valid certificates, see http://go.microsoft.com/fwlink/?LinkID=241478.

For comparison:

Above: Visual Studio 2017

Above: Visual Studio 2019 – notice the options to select from file and create test certificate are no longer.

To fix this for Azure Devops, you’ll need to install the PFX private key on every build. Follow these steps:

  • On the Choose Certificate window (shown above) choose View Full Certificate
  • On the second tab, choose “Copy to file…” to start the export to PFX process
  • Export the private key to a password protected PFX file
  • Add the PFX file to your project directory, like where it used to be in VS 2017
  • Update your .csproj file, adding a <PackageCertificateKeyFile> element containing the filename alongside <PackageCertificateThumbprint>
  • Add your PFX to source control making sure it is not ignored
  • In Azure Devops Pipelines, you’ll need a quick Powershell build step to add the certificate to the local user store:
  • Make sure that the WorkingDirectory option is set to the folder with the PFX file (alongside the .csproj) file.

That Powershell script in full:

$pfxpath = 'MySigningKey.pfx'
$password = 'supersecretpassword'

Add-Type -AssemblyName System.Security
$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2
$cert.Import($pfxpath, $password, [System.Security.Cryptography.X509Certificates.X509KeyStorageFlags]"PersistKeySet")
$store = new-object system.security.cryptography.X509Certificates.X509Store -argumentlist "MY", CurrentUser
$store.Open([System.Security.Cryptography.X509Certificates.OpenFlags]"ReadWrite")
$store.Add($cert)
$store.Close()

Now when your app is built, the private signing key will be loaded from the local machine store.

A note on security

The above is a quick and dirty way of getting this working – adding a PFX file to your source code repository is not best practice and you shouldn’t do this if you can help it. This is probably why Microsoft changed this behaviour in VS2019. An improvement on this could be to use the Secure Files feature of Azure DevOps to securely hold the PFX file until the build templates have a decent way of handing this scenario.

 

 

 

 

Multiple monitors? You should buy VMWare Fusion instead of Parallels Desktop

In a post three years ago, I waxed lyrical about how much better Parallels Desktop was compared to VMWare for the very common task of running Windows on your Mac.

It’s time to take that back.

Parallels Desktop is no longer fit for purpose if you are an advanced user.

How Parallels Desktop broke multiple monitors

In older versions of macOS, virtual desktops spanned your whole set of monitors. Therefore if you had a left and right monitor, switching spaces (or virtual desktops) would switch both, giving you two “Desktops”, Desktop 1 (left monitor A and right monitor A) and “Desktop 2”, (left monitor B and right monitor B). Switching between desktops would switch both screens. The major downside of this was that when applications were run “Full screen” (rather than just maximised), they would go full screen on one monitor and leave the other one completely blank, which was complete madness

In Parallels 11, Parallels supported two ways of rendering full screen on multiple monitors. The first was using macOS’s built in full screen function (more on that in a minute) and the other using a “non-native” method that involved drawing a windowless fullscreen window on top of the whole screen. 

To work around the full screen issue when using multiple monitors, macOS Yosemite introduced the option for displays to have their own “Spaces”. This meant that your left and right monitors have their own sets of virtual desktops. However, this meant that each monitor could be switched desktop independently, introducing say 4 different combinations when you had two monitors and two desktops. This was a context switching nightmare. Most power users turn this off, especially if they are using keyboard shortcuts (CTRL+arrow keys) to switch between spaces because the monitor that would switch would be the one your mouse cursor was over.

The combination of turning off “Displays have separate Spaces” in macOS, and disabling “native full screen mode” in Parallels was the perfect, wanted behaviour that Parallels users of multiple monitors had become accustomed to for many, many years.

Parallels 12 changed all that, by removing the non-native full screen mode option that was working perfectly in version 11, leaving users with no satisfactory multi-monitor display mode.

Users were up in arms:

8 pages of complaints on the official Parallels forum when Parallels 12 launched with this

“Usable” multi-monitor support feature request

Did Parallels listen? Well, only a little. Near the end of version 12’s shelf life they pushed an update out that contained a work around – an option to “switch” all other spaces to Parallels when you clicked Parallels on another space. Sounds great but still doesn’t allow you to switch in and out of Windows on all of your screens at once.

Users were livid. The pithy Knowledge Base article didn’t help either.

Then Parallels 13 came out with no new fixes for this. Parallels was effectively dead for users with multiple monitors.

Other reasons not to use Parallels any more

The push for yearly subscription pricing. You aren’t Creative Cloud guys. The last thing users want when buying a piece of utility software is to set calendar reminders that they are going to be auto-rebilled.

The shovelware and crapware that Parallels pushes on you, even via advertisements with the application that you paid for. Who doesn’t want a subscription to Parallels Remote Access or “Parallels Toolbox”?

Only 9.99 USD a year!!

The resurrection of VMWare Fusion

Back in the day, Parallels spanked VMWare Fusion on performance. They became market leaders and deserved it. I fondly remember running Parallels 4 against a Bootcamp partition on a now clunky old Mac Mini and being pleasantly surprised.

I’ve recently given VMWare Fusion 8.5 a go and I am pleased to say the performance against Parallels for my main use case (Visual Studio on Windows 10) is indistinguishable. It imported my Parallels VM flawlessly. It didn’t pester me to install anti-virus in my Windows 10 VM (something so completely pointless Parallels must be getting kickbacks). There will be a free upgrade to VMWare Fusion 10 this October. And most importantly…

It works correctly with multiple monitors!

Yes, VMWare Fusion 8.5 behaves the same way Parallels 11 used to work.

RIP Parallels Desktop.