When privately deploying SQL Server Compact you will need to take a few extra steps if you wish to use the ADO.NET Entity Model. Here's some info on how to do it.
Why the extra steps?The ADO.NET Entity model was designed as a mid-tier solution. In the mid tier, IT owns the box and they can typically install the software needed. On the client, particularly in corporate environments, the end user doesn't have permission to "install" software. The Entity Framework was also designed to leverage the provider agnostic programming model. Since the System.Data.SqlClient.dll is GAC'd with the .NET Framework, and the DB Provider model was designed around machine.config, a number of assumptions were made. However, no fear, you can add new DbProvider entries, but it may not be as obvious, or what you think works may only work in certain situations. I'll explain as I walk through the steps.
To make this clear, I'll walk through the scenario from Create New Project. If you already have a project that privately deploys SQL Server Compact, jump ahead to Adding the Entity Provider entry
Create New ProjectBefore we jump into the deployment details, let's build a quick app as a baseline.
Deploying the SQL Server Compact RuntimeIn the above app, we're working in our development environment with all the latest gadgets. So of course everything works fine, but what about your end users? Do they have everything you just used? While ClickOnce can help end users Pre-Requisite the additional components, this assumes end users have the rights to install software. With a focus on security, Microsoft has worked hard to convinced customers to lock down their PCs. Which means that unless IT does the rollout, you may not have what you need. If your one of those "bottom line affecting" apps that have IT sponsorship, you may be fine, and you can skip this section. If your one of those development/product teams that are working "against the grain" for the corporate mandate that "all apps must be web apps", or you simply want to enable a clean "install" experience for your app, you can take the following steps to assure your apps will "just work". If SQL Server Compact is centrally installed, great. The central version will be used. If there is no centrally installed version, or you need a newer version that what IT is ready to push out, no problem, just follow these steps. If/when IT catches up, the .NET Loader will simply switch to the central version.
Remove the ClickOnce Pre RequisitesNow that you've included SQL Server Compact with your app, you no longer need the ClickOnce prerequisites. The Pre-Reqs require Admin rights to install, and this has been a blocker for many companies that have followed Microsoft's advice to lock down their computers.
Where are we?At this point, we have a basic WPF app with some local data. We've including the SQL Server Compact runtime so we don't have to worry about whether it's installed or not. Are we ready to publish the app for ClickOnce installation - nope, not yet. This is the meat of the heading, the reason I've written all the above text. Just in case you don't believe me, try publishing with ClickOnce, shutting down VS, uninstalling SQL Server Compact (both 32 or 64), or simply installing on a machine without Compact installed. You'll notice you get a failure to load data as it can't resolve the Entity Provider.
Main Purpose of This Post
Adding the Entity Provider EntryAs I noted above, the Entity Framework was initial designed for mid tier solutions. This assumes that everything is centrally installed for all services to leverage. However, on clients, we may need to add additional functionality to the application that doesn't ship in the .NET Framework. When SQL Server Compact is centrally installed, the MSI will configure an entry into Machine.config. On clients that don't have SQL Server Compact centrally installed, you'll need to add the entry for the Provider Factory to find SQL Server Compact. But, you can't add it to Machine.config, because that's a central resource which requires Admin rights. Instead, we can add it to app.config, but there is a catch. It turns out database people (ADO.NET team) think about data integrity and constraints as a religion <g>. If you have a machine.config entry and then attempt to add an app.config entry the runtime will throw a constraint violation. We've discussed changing this in the future to simply merge the values (DataTable.Merge) to update any "primary keys" with the new value, but for SQL Server Compact 3.5 and FX 3.5, we need to do a little workaround.
<?xml version="1.0" encoding="utf-8"?><configuration> <connectionStrings> <add name="LocalDataEntities" connectionString="metadata=res://*/LocalData.csdl|res://*/LocalData.ssdl|res://*/LocalData.msl;provider=System.Data.SqlServerCe.3.5;provider connection string="Data Source=|DataDirectory|\LocalData.sdf"" providerName="System.Data.EntityClient" /> </connectionStrings> <system.data> <DbProviderFactories> <remove invariant="System.Data.SqlServerCe.3.5"></remove> <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=126.96.36.199, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/> </DbProviderFactories> </system.data></configuration>
Notice two attributes for name and invariant. It turns out invariant is actually the "unique constraint/name".If we were to run the app at this point, with Compact centrally installed, the app would fail to load with a duplicate config entry. It's a little vague as the exception is a bit buried in the inner exceptions of the top level exception.
<DbProviderFactories> <remove invariant="System.Data.SqlServerCe.3.5"></remove> <add name="Microsoft SQL Server Compact Data Provider"
This simple line of XML tells the ADO.NET Provider Factory to first remove any entries for this invariant name. It's only for this particular app instance, so you're not affecting any other apps on the box. If it's not there, no problem, database people know how to remove things without failing.
The real testOf course the real test is to deploy the app to a machine that doesn't have Compact installed. Now the Entity framework does require .NET FX 3.5 SP1, so your clients do need all those goody bits. But, you might get IT roll out the .NET Framework. It would be great to get them to deploy Compact as well, but IT folks get a little scared about deploying a SQL Server product to all their clients. Not sure why. Maybe we just need to wait a few years ‘till the new guys replace the old ones that remember...
To figure out if your app was loading Compact privately, I've written a lame (UI) about box that you can add to your project to see. The entire source to this walkthrough is below.
Hope this post was helpful. Would love to hear how many of you are using the Entity Framework with Compact. How many have been able to centrally deploy Compact, or you've gone the private deployment route. Was it because of the Admin rights issue, packaging of your app, or something else.