SQL Server Compact Edition / Entity Framework bug

  • Article

I am working on this really smart, smart client application installer. Which smartly installs other pieces of another smart client application as and when required.

It makes sure that only those bits are downloaded that are not already installed. So it takes care of upgrades on its own. So basically we are writing our own "ClickOnce" functionality.

Why are we not using ClickOnce out of the box? Well that would require another post, but let's say we require much more granularity than what "ClickOnce" provides us, on this "Installation Manager", and put it at that.

Anyways, I am doing some book keeping using SQL Server CE on the client machine to maintain account of upgrades and installations. For this I am querying the SQL Server CE database and in the process and I encounter an error that says:

"The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses, except when these data types are used with the LIKE or IS NULL predicates."

I am not even working on ntext data type. The select query goes something like this:

IQueryable

<FileInfo> files = entities.FileInfo.Where(fileInfo => fileInfo.FileName.Equals(fileName));

And "FileName" is not an ntext datatype in the entity "fileInfo". It is an nvarchar.

Interestingly, if you fire the same query on the Management Studio (SQL Version of the query of course), it works fine. It only breaks when you use LINQ to SQL or EF.

After a little research I find out that there is a bug in SQL Server 3.5 RTM which was incidentally not resolved even in the "SQL Server Compact 3.5 SP1 English". And later there was an out of the bag release that was made to resolve this issue. So the Hotfix, cannot be installed directly. You need to uninstall "Microsoft SQL Server Compact 3.5 SP1 English" from Add Remove Programs before you install this hotfix.

Once the Hotfix is installed, your query will just work...!

In case if you are wondering whether you need to re-install "Microsoft SQL Server Compact 3.5 SP1 English"? Well, installing it back will remove the hotfix. So DO NOT DO THAT.