SqlServerCe Versions Oddities

Posted on September 18, 2018 in umbraco , dotnet

TL;DR: install hotfix.

We are currently working on polishing the implementation and support of variants, a.k.a. multi-lingual values, in Umbraco 8. Which means we regularly end up writing hairy SQL queries to retrieve, for instance, the value of a given property, for a given language. Queries that contain SQL fragment such as:

JOIN [umbracoLanguage] ON ...
WHERE [umbracoLanguage].[languageISOCode] = 'de-DE'

Some of these queries are... more complex that we would like them to be, and we end up running them in the excellent LINQPad tool to ensure that they return what we want them to.

And, precisely, today, one of these query totally failed to return the expected rows. To the point that we started removing JOINs after JOINs to figure out where things were going bad, and ended up with:

SELECT * FROM [umbracoLanguage] WHERE [languageISOCode] = 'en-US'

And... guess what? That query would return zero rows in LINQPad. And yet the back-office clearly identified an en-US language. And a nl language, too. And guess what? This query:

SELECT * FROM [umbracoLanguage] WHERE [languageISOCode] = 'nl'

Returned one row.

When Things Get Weird

And it can get even weirder. Add the fr-FR language via the back-office: SQL cannot get it. Explicitely INSERT INTO language bem-ZM via SQL... and SQL can get it. Also, the following query would return one row:

SELECT * FROM [umbracoLanguage] WHERE [languageISOCode]+'' = 'en-US'

Try to INSERT INTO language en-US via SQL... and it works, despite the UNIQUE index on the ISO code. And then you have to identical entries and a UNIQUE index, and all sorts of crazy things happen. Because they are binary-identical, as the following query would show:

SELECT [languageIsoCode], CONVERT(VARBINARY, [languageIsoCode]) FROM [umbracoLanguage]

More fun? Drop the UNIQUE index and re-create it:

DROP INDEX [umbracoLanguage].[IX_umbracoLanguage_languageISOCode]
CREATE UNIQUE NONCLUSTERED INDEX [IX_umbracoLanguage_languageISOCode] ON [umbracoLanguage] ([languageISOCode])

And then all queries work... until you create a new language via the back-office, because that language remains invisible to SQL queries.

Ah and of course, running any of these queries from C# (and not in LINQPad) works perfectly. So, at that point, we started questionning LINQPad sanity and tested another excellent tool, SqlCe40Toolbox. Alas, same weird results.

Eureka

Time to stop trusting tools, and build our own very simple console application that can run a query against a SqlCe database. This requires that we pick a NuGet package for the SqlCe libraries. Should we pick the default Microsoft.SqlServer.Compact or the custom Umbraco.SqlServerCe? Let's try one and then the other.

And... our query works with Umbraco's but fail with Microsoft's! So, obviously, the SqlCe version is the problem! Indeed, it turns out that there are several versions of SqlCe 4.0. And some of them, including the very latest (as of today) have never been formally released.

Microsoft's System.Data.SqlServerCe NuGet package contains version 4.0.8876.1. But the more recent (and latest, as of today) 4.0.8902.1 version only exists as a hotfix which you have to request from Microsoft and which, for instance, fixes issues with column values containing dashes.

Umbraco runs on this very latest version, which we packaged as the Umbraco.SqlServerCE NuGet package. However, tools such as LINQPad use the version that lives in the GAC, and if you have not installed the hotfix, it is an old version.

Installing the hotfix immediately fixed the issue. So, if you plan to look into Umbraco's SqlCe databases using tools such as LINQPad... you know what you have to do.