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 Project
Before we jump into the deployment details, let's build a quick app as a baseline.

1. Create New Project
   1. We'll start by creating a new WPF project. We'll use C#, but VB is just as well. To follow along, I've named it: CompactEntityProviderPrivateDeployment
      Yes, this works with WinForms as well, but I'll never really learn WPF if I keep using my old habits of WinForms...
2. Add a new Local Database
   1. Add a new item to the project, and select Local Database. I've named it: LocalData.sdf
   2. Hit cancel on the Choose Your Database Objects wizard.
      This is the default wizard for DataSets and we're focusing on the Entity Framework here...
3. Add a new table
   Rather than do yet another Northwind sample, we'll create a single table database.
   1. Double click the LocalData.sdf file to open the database within Server Explorer
   2. Right click on the Tables node and select Create Table
   3. Name the table Customers
   4. Add a column: Name = Id, DataType = nChar, Length = 10, Allow Nulls = No, Primary Key = Yes
   5. Add a column: Name = CompanyName
   6. Press Ok, to complete the creation of the table
4. Add some data
   1. Open the Tables node
   2. Right click and select "Show Table Data"
   3. Add a few entries (1, Acme | 2, Widgets | 2, Microsoft | ...)
5. Add the Entity Model to program against
   1. Right Click on the project and select Add New Item
   2. Select ADO.NET Entity Model
   3. Name the item: LocalData.edmx
   4. Choose Generate from Database
   5. Accept the default for LocalData.sdf, click next
   6. Check the Customers table and press finish
6. Viewing the data on the WPF form
1. Drag a ListBox to the Window1.xaml designer
2. Double click the title bar of the window to create the Window_Loaded event
3. Add a class instance of the Entity model outside of the Window_Loaded event
   public partial class Window1 : Window {
     LocalDataEntities _localDataContext;
4. In the Window_Loaded event, within a Try/Catch block, instance the Entity Model and load the listbox
   private void Window_Loaded(object sender, RoutedEventArgs e) {
       try {
           _localDataContext = new LocalDataEntities();
           this.listBox1.ItemsSource = from c in _localDataContext.Customers select c;
           this.listBox1.DisplayMemberPath = "Name";
       }
       catch (Exception ex) {
           MessageBox.Show(ex.ToString());
       }
   }
5. Run the app
6. All should work fine for now and your app should look as follows:

Deploying the SQL Server Compact Runtime
In 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.

1. Add the Assembly References
   View References for your project - notice System.Data.SqlServerCe.dll isn't listed. This is because providers are loaded by the Entity framework at runtime based on the connection string in App.config. We'll need to manually add the references for VS to deploy these dlls.
   1. Choose Add Reference and select System.Data.SqlServerCe from the first .NET tab and press Ok
   2. Choose Add Reference, but this time choose Browse to find the Entity dll.
      It's not listed by default as it was assumed the Entity Framework would only be used when centrally installed.
      Browse to: C:\Program Files\Microsoft SQL Server Compact Edition\v3.5 and select System.Data.SqlServerCe.Entity.dll
      Note: on 64bit machines, it doesn't matter if you grab the Entity dll from the 32 or 64bit directory as the same "platform neutral" dll is used for both
2. Change the managed references to be "local"
   1. In the References node, select System.Data.SqlServerCe and then select the property grid. Change Copy Local = True. This tells VS to copy the dll to the output directory, even though it's GACd on your development computer. When VS builds your project, it will copy the dll to your output directory, but it will also add it to the ClickOnce Application Files list.
   2. Change the System.Data.SqlServerCe.Entity reference to Copy Local = True as well
3. Add the native runtime for SQL Server Compact
   Similar to the .NET Framework, SQL Server Compact achieves its speed, reliability and compact size by using native code for the storage engine and query processor. As of 3.5 SP1, Compact added 64bit support. To avoid your end users from having to figure out whether they have 32 or 64bit machines, only to then choose a 32 or 64bit install, you can include both the 32 and 64bit runtimes directly within your application so end users can get a single "install/copy" of your app and it will "just work" regardless of their bit alignment

1. To add the 32bit runtime: Add a folder named X86 to your project
2. Select Add Existing Item and navigate to: C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5
   Note: if your client is a 32bit client, simply remove the (x86) portion of the directory:
3. Using CTRL + Click, select sqlcese35.dll, sqlceqp35.dll, sqlceme35.dll and sqlcecompact35.dll, then choose the little down arrow on the Add button and choose Add As Link
   This will make sure any updated installed on your client will be added to the project the next time you build
   
4. Select the 4 files within Solution Explorer and select Properties
5. Change Copy To Output Directory = Copy If Newer
   Similar to the .NET References above, this tells VS to verify the date/time stamp at build time and only spend time copying the files if the source is newer than the output directory
   
6. Assuming you have a 64bit machine with SQL Server Compact 3.5 SP1 64bit installed you can add the 64bit binaries as well
1. Add a folder named AMD64 to your project
2. Select Add Existing Item and navigate to: C:\Program Files\Microsoft SQL Server Compact Edition\v3.5
3. Just as above, using CTRL + Click, select sqlcese35.dll, sqlceqp35.dll, sqlceme35.dll and sqlcecompact35.dll, then choose the little down arrow on the Add button and choose Add As Link

Remove the ClickOnce Pre Requisites
Now 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.

1. Right click on the project and select Properties. You can also double click Project Properties for C# projects, or My Project for VB projects
   
2. Uncheck everything, but .NET Framework 2.0 as this will give you the cleanest Publish page.
   Unless your clients have admin rights, none of the other pre-reqs will work anyways, so you might as well clean up the choices they can actually make. By choosing .NET FX 2.0, VS will generate some javascript that will collapse the region prompting installation of the .NET Framework.

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 Entry
As 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.

1. Grab the XML fragment for the Compact DbProvider
2. Open machine.config from: C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG
3. Search for SQL Server Compact
4. Copy the entire XML fragment for <system.data>
5. Open app.config in your client app. 
   Note You must edit the hosting exe as dll's don't load unique app.config files.  We recognize that most apps do their data access in dlls, and often the developer building the exe portion of the app shouldn't know or care what's in the dlls of the app, so this is why we know this is a bit painful and would like to find a better solution.  But, this is where we are, so, blogging on...
6. Paste the XML fragment from machine.config, but trim it down to the following.  Note, I've greyed out the other content that you likely have in app.config to show the positioning within the file:

<?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=&quot;Data Source=|DataDirectory|\LocalData.sdf&quot;" 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=3.5.1.0, 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.

1. Publish the application to see where we are
   You're now finally ready to ClickOnce publish your application, with SQL Server Compact and the Entity Provider included
   1. Right click on the project and choose "Publish..."
   2. Enter an HTTP address for a webserver you have permissions to: http://localhost/CompactEntityProvider/
      If you've got Vista and don't know the additional settings, send me a comment below and I'll either add the steps, or point you to Saurabh Pant for the steps
   3. Finish the wizard and you should now have a default web page with the ability to Install your app
2. Install the app
   Before doing anything else, install the app and let it run. If you've placed the try/catch block around the instancing/loading of the entity model, you should get an exception
   The exception is because of those darn DBA types that make sure things are accurate and don't want duplicate data <g>. So, how to work around this? We've played with a few things such as changing the invariant name to something unique, but that means you must correlate the change in the connection strings that VS automatically generates for you. Since this is just too easy to mess up, we've come up with the following recommendation:
3. Resolving the duplicates
   The problem is essentially that we know we need an Entity provider for SQL Server Compact, but we don't know if one is already on the machine. Turns out one of the easiest ways to resolve duplicate data is to delete one. The Config framework does provide a nice way to do this.
   1. In the app.config file, add the collored <remove...> XML fragment:

   <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. 

   2. Republish and install the app
      1. Right click on the project and choose "Publish..." again. This time, just click finish as you should have everything configured properly
      2. Click Install, and voila, your app should now be running just fine.
         Regardless of a central install, without having to create unique invariant names, you now have Compact running within your app

   The real test
   Of 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.

   Sample Sourcecode 

   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.

   Thanks,

   Steve