Using Entity Framework with an SQL Compact Private Installation

Question

I am using Entity Framework 4 in a desktop application with SQL Compact. I want to use a private installation of SQL Compact with my application, so that my installer can install SQL Compact without giving the user a second installation to do. It also avoids versioning hassles down the road.

My development machine has SQL Compact 3.5 SP1 installed as a public installation, so my app runs fine there, as one would expect. But it's not running on my test machine, which does not have SQL Compact installed. I get this error:

The specified store provider cannot be found in the configuration, or is not valid.

I know some people have had difficulty with SQL Compact private installations, but I have used them for a while, and I really like them. Unfortunately, my regular private installation approach isn't working. I have checked the version numbers on my SQL CE files, and they are all 3.8.8078.0, which is the SP2 RC version.

Here are the files I have included in my private installation:

• sqlcecompact35.dll
• sqlceer35EN.dll
• sqlceme35.dll
• sqlceqp35.dll
• sqlcese35.dll
• System.Data.SqlServerCe.dll
• System.Data.SqlServerCe.Entity.dll

I have added a reference to System.Data.SqlServerCe to my project, and I have verified that all of the files listed above are being copied to the application folder on the installation machine.

Here is the code I use to configure an EntityConnectionStringBuilder when I open a SQL Compact file:

var sqlCompactConnectionString = string.Format("Data Source={0}", filePath);

// Set Builder properties
builder.Metadata = string.Format("res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl", edmName);
builder.Provider = "System.Data.SqlServerCe.3.5";
builder.ProviderConnectionString = sqlCompactConnectionString;
var edmConnectionString = builder.ToString();

Am I missing a file? Am I missing a configuration stepp needed to tell Entity Framework where to find my SQL Compact DLLs? Any other suggestions why EF isn't finding my SQL Compact DLLs on the installation machine? Thanks for your help.

Answer 1

I figured out how to do it, thanks to a blog post by Steve Lasker. Basically, here is what you have to do:

(1) Set a reference to System.Data.SqlServerCe.dll in your project. Set the CopyLocal property to True.

(2) In the App.config for your project, add the following XML markup. It tells EntityFramework to look to your private installation of SQL Compact for its data provider:

<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.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
</system.data>

(3) In the Setup project, add the following files to the Application Folder in the File System Editor:

• sqlcecompact35.dll
• sqlceme35.dll
• sqlcese35.dll
• System.Data.SqlServerCe.Entity.dll

Answer 2

Just for the record, with SQL CE 4 the web.config entries are the following:

  <system.data>
    <DbProviderFactories>
      <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=4.0.0.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
    </DbProviderFactories>
  </system.data>

Version=4.0.0.1 in case of a private deployement and Version=4.0.0.0 in case of a general deployment.

Answer 3

thanks for your tip - helped me alot. There's a post on the SQL Server Compact-Team blog which add some additional information for the Sql Server Compact 3.5 SP2 release.

After struggling for a while with the private deployment of sql server compact i found out some additional requirements.

I tried out my app on several different systems and recognized that my app not worked properly on some of them.

Example: Try this:

-I've set up a clean winxp sp3 installation

-installed the .net Framework 4.0 extended

-deployed my app to the new installation (including all necessary dll's and tweaks described in your/sql server compact team blog post's)

So, after some research i found out that in addition to the .net framework 4 installation i had to install the .net framework 2 as well and it worked fine.

So here is my question: which component is used by the sql server compact edition which is not contained in the .net framework 4?

I don't want overload my setup and chain two frameworks to my bootstrapper... Does anybody know any good Tip?

Many thanks to the sql server compact Team!

SQL Server Compact v3.5 depends on ‘Visual C++ Runtime 2005 (or 8.0)’ (also known as CRT80). We package the CRT80 modules in our MSI. In case of private deployment, you need to take care of this dependency. Things work with .NET FX v2.0 on the system automatically because .NET FX v2.0 also packages and installs CRT80 modules.