Private Deploy of EF with SQL CE 4.0 and Click Once

By Mirek on (tags: Click Once, deployment, Entity Framework, Sql Ce, Sql Server Compact 4.0, categories: infrastructure)

Recently I faced the problem with deploying an WPF application, which uses Entity Framework and Sql Server Compact 4.0 nuget packages. The application was deployed automatically by TFS Server as a Click Once  installer. The problem was that the setup did not download Sql Server package at client machine and application could not work. Apart from checking what is maybe wrong on TFS side configuration, the simplest solution was to do a so called Private Deploy of the Sql CE libraries. Unfortunately it turned out to not be easy too.

To be more precise the Entity Framework was in version 5.0.0 –rc and Sql Server Compact had version 4.0.8876.1. To eliminate problems with incompatibility of those versions, I tried to deploy Click Once installer locally and copied it as zip to the client machine. There the Sql Server Compact was correctly downloaded (as defined in installer prerequisite) and application succeed to run.

Basically Private Deploy is just a way of providing required assemblies with an application by adding these assemblies to the application project. In my case I had to add couple of Sql Server Compact dlls to the root folder of my WPF project. The detailed description of this process step by step can be found here or here.

After installing the application on the client machine the first problem was that the Entity Framework could not resolve the database provider.

System.NotSupportedException: Unable to determine the provider name for connection of type 'System.Data.SqlServerCe.SqlCeConnection'

I found out that when Sql Server CE is installed using standard installer then the appropriate entries are added to the machine.config file which informs the framework which assembly contains the missing provider for Sql CE. It was enough to put this entry in application config file

    <remove invariant="System.Data.SqlServerCe.4.0" />
    <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

and the database provider now is properly recognized.

The next problem revealed with following exception

Could not load System.Data.SqlServerCe.Entity.dll. Reinstall SQL Server Compact.

This time I found a solution on this blog post. It was enough to include additional nuget package called EntityFramework.SqlServerCompact. Finally the application started with no errors.