LINQ with SQL Server CE 3.5

LINQ is an extremely useful new language feature for the .NET Framework 3.5. It allows querying all sorts of data structures via the actual programming language (thus the Language INtegrated in LINQ). In addition, the specific variant, LINQ to SQL, includes a full O/RM (Object/Relational Mapper). Since many developers spend a ton of time mapping their relational data to objects, this included tool is a phenomenal time-saver. Of course, this is not a new concept, nor is it the first of its kind for .NET. It’s just free and included!

LINQ to SQL currently only works with SQL Server 2005. It’s not likely that Microsoft will bother with any other providers with the Entity Framework and LINQ to Entities on the horizon. There is a way to get standard LINQ to SQL to work with the SQL Server Compact Edition (SSCE) however. You have to use the SqlMetal command-line tool to generate the appropriate file(s). Once you generate the .dbml file, you can even open it in the Visual Studio 2008 designer.

Deploying SSCE when Using LINQ

Deploying SSCE with your desktop application is pretty easy. You can quickly publish your application via ClickOnce, but I’m not a big fan of that technology. Instead, I prefer an installer program. You can use any one that you prefer and still easily deploy SSCE with a couple of options.

Windows Installer

First, you can choose to create a pre-requisite or dependency tests for the system installation of SSCE for the version you require (3.5 or later to work with LINQ). If the engine is not installed, you can send the user to the Microsoft website for installation or you can launch the redistributable Windows Installer file for it. This method requires that the user have Administrator privileges in order to perform the system install.

Please note that if you launch the .msi file to install SSCE and your installer is a Windows Installer program, you will have to close your installer first; two instances of Windows Installer cannot be run simultaneously.

X-Copy

Second, you can just copy the necessary redistributable library files for the SSCE engine into your application’s install directory. This method does not require Administrator privileges! There are 7 files that need to be copied with your application and they should be copied to the same directory as your primary executable.

• sqlceca35.dll
• sqlcecompact35.dll
• sqlceer35EN.dll
• sqlceme35.dll
• sqlceoledb35.dll
• sqlceqp35.dll
• sqlcese35.dll

Make sure your application references the System.Data.SqlServerCe.dll assembly and it is set to copy the assembly locally as well. This is especially important when you use LINQ to SQL with SSCE because that assembly is implied at runtime and therefore not explicitly required as a reference in your application until it’s time to deploy on a system that does not have SSCE installed.

Please, be aware that if you choose to redistribute SSCE in either form (Windows Installer or individual libraries), you must register for redistribution rights with Microsoft.

The Hidden Problem

There is a hidden problem with all of this will finally crop up with the x-copy method. Since LINQ uses a factory pattern to acquire the necessary data provider, the necessary reference to the provider does not occur until runtime. Herein lies the problem.

When your application makes that first attempt via LINQ to connect to the SSCE database file without a system install, you will receive the following error message:

Cannot open “X:Pathtodatafiledatafile.sdf”. Provider ‘System.Data.SqlServerCe.3.5’ not installed.

If SSCE is installed by the user via the Windows Installer file, this problem is handled for you. The installer adds an entry to your machine.config file. Since the whole purpose of an x-copy install is usually to avoid the need for Administrator privileges, this just will not do.

Instead, you will need to add an application configuration file to your desktop application, if you do not have one already. Right-click your project in “Solution Explorer”, choose “Add new…”, select “Application Configuration”, and click OK. Make sure the new file looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.3.5" />
      <add
        name="Microsoft SQL Server Compact Data Provider"
        invariant="System.Data.SqlServerCe.3.5"
        description=".NET Framework Data Provider for Microsoft SQL Server Compact"
        type="System.Data.SqlServerCe.SqlCeProviderFactory,
          System.Data.SqlServerCe,
          Version=3.5.0.0,
          Culture=neutral,
          PublicKeyToken=89845dcd8080cc91"
      />
    </DbProviderFactories>
  </system.data>
</configuration>

This ensures that the appropriate database provider factory is added for your application, without the need to edit the end-user’s machine.config file or require a system installation of SSCE.