Monday, November 25, 2013

Entity Framework 6 & SQL Server Compact 4 (2)–Easy Private Desktop Deployment

UPDATE: Instead of using the NuGet package mentioned below, use the package mentioned in this newer blog post: http://erikej.blogspot.dk/2014/10/entity-framework-6-and-sql-server.html

In this post I will describe a simplified approach to SQL Server Compact Private Desktop Deployment with Entity Framework 6, for an overview blog post on Private Deployment with SQL Server Compact, see my blog post here.

When using Private Deployment with Entity Framework 6, due to the fact that the EntityFramework.SqlServerCompact also installs the SQL Server Compact NuGet package, we must use a slightly different approach from what I described here in order to keep thing simple. The main difference is that we must force the project to use x86 Target Platform rather than AnyCPU, Prefer 32 bit. This is due to the fact that when running Prefer 32 bit, the SQL Server Compact ADO.NET provider loads the wrong files, as the PROCESSORPLATFORM in this case remains AMD64 on 64 bit systems, but the executing .NET Framework is the x86 version.

To read more about the new default Platform target option introduced in .NET 4.5, see the MSDN documentation here, and the blog post here.

In addition, I will demonstrate how to use my SQL Server Compact Toolbox to quickly get started with a Database First workflow.

For the sake of simplicity, and in order to focus attention on the private deployment aspects, I will demonstrate with a console application, but the same approach will also work for WinForms and WPF applications. The approach will NOT work for dll output projects, like for example add-ins and similar, as it relies on configuration entries in app.config. And this blog post is about deployment, not layered architectures. 

In short, we need to do the following:

1: Create a project and set Target Platform to x86

2: Add the EntityFramework.SqlServerCompact.PrivateConfig NuGet package to the project

3: Add our database file as project content

4: Create EDMX (or Code First classes) and adjust connection string

5: Add code to properly deploy the database file during app launch

But let’s expand on each task below.

Before you get started, make sure you have the following installed:

1: Visual Studio 2013 Pro or higher.

2: The latest version of  my SQL Server Compact Toolbox add-in (Install via Tools/Extensions in VS). The Toolbox requires the SQL Server Compact 3.5 SP2 and 4.0 SP1 runtimes to be installed (the 3.5 requirement will be lifted in the next release of the Toolbox)

3: An existing SQL Server Compact database file, I will use Chinook

With that in place, let us open Visual Studio and get started:

Create new console application

Go to File, New Project, and create a new Windows Console application. Make sure to set the target platform to 4.0 or newer.

image_thumb3

Now set the Target Platform to x86 (this is an important step, if you forget to do this you will get a BadImageFormat exception during runtime/debug)

Go to the project properties, and select the Build section/tab, and choose x86:

image

Install Entity Framework 6 from NuGet

To get the latest and greatest Entity Framework 6 bits, always install from NuGet. And thanks to the amazing package dependency resolution features of NuGet, just install the single required package, and the remaining packages will be installed as dependencies. In this case, the only thing that is missing from the EntityFramework.SqlServerCompact package (which depends on SQL Server Compact 4 and EF6) is a DbProvider registration in the app.config file, that enables you to run without the SQL Server Compact runtime installed in GAC and machine.config. To fix this “"misssing link”, I have created the http://www.nuget.org/packages/EntityFramework.SqlServerCompact.PrivateConfig/ 

package, which simply adds the missing config entries and in turn depends on all other required packages. It adds this to the app.config file:

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









In other words, simply install the EntityFramework.SqlServerCompact.PrivateConfig package. Right click the References folder in your project, and select Manage NuGet Packages. Search for the package name and install it. And all required packages and references will be installed. (I am working on getting these config entries added to the base EntityFramwork.SqlServerCompact package, in order to make things more streamlined)




Add your database file to the project


I will use Chinook, which you can install from NuGet. But of course feel free to use your own database file.


Find the Chinook SQL Server Compact database package, and install it:


image




Make sure to mark the database file as Content, Copy Always in the project. I will describe later in this post how to place it correctly on the end users PC.




image




 Create the Entity Data Model (EDMX) and adjust the connection string




Build the project.




Now in the SQL Server Compact Toolbox, connect to the database file in your project folder:




image




Right click the database, and select the “Add Entity Data Model (EDMX) to current project” menu item:




image




Click OK, and the EDMX file and various other files will be added to your project:




image




Build the project.




Now let us add some test code to the Main method in order to verify that everything works so far:

using (var context = new ChinookEntities())
{
foreach (var album in context.Album.ToList())
{
Console.WriteLine(album.Title);
}

}
Console.ReadKey();





















We can now access the database via the generated object model, and do not have to type SQL, but can use LINQ to Entities to query the database. In addition, we can update the database (INSERT, UPDATE, DELETE) via methods on the derived DbContext class, ChinookEntities. In app.config, the following connection string has been added:

  <connectionStrings>
<add name="ChinookEntities" connectionString="metadata=res://*/Chinook.csdl|res://*/Chinook.ssdl|res://*/Chinook.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;Data Source=C:\Users\erik.COMMENTOR\Documents\Visual Studio 2013\Projects\ConsoleApplication4\Chinook.sdf&quot;"
providerName="System.Data.EntityClient" />
</connectionStrings>









In order to make the connection string user and folder independent, change the data source as follows:

<connectionStrings>
<add name="ChinookEntities" connectionString="metadata=res://*/Chinook.csdl|res://*/Chinook.ssdl|res://*/Chinook.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;Data Source=|DataDirectory|\Chinook.sdf&quot;"
providerName="System.Data.EntityClient" />
</connectionStrings>



Deploy the database file


The final step will be done to ensure that the database file will be located in a writeable location on the users machine when deployed/installed. We will simply do this in code in order to not depend on any install actions and issues. We will use the same approach that I have already used in my blog post here, which takes advantage of the DataDirectory connection string macro. So add this piece of code to the Program class:

private const string dbFileName = "Chinook.sdf";
private static void CreateIfNotExists(string fileName)
{
string path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
// Set the data directory to the users %AppData% folder
// So the database file will be placed in: C:\\Users\\<Username>\\AppData\\Roaming\\
AppDomain.CurrentDomain.SetData("DataDirectory", path);

// Enure that the database file is present
if (!System.IO.File.Exists(System.IO.Path.Combine(path, fileName)))
{
//Get path to our .exe, which also has a copy of the database file
var exePath = System.IO.Path.GetDirectoryName(
new Uri(System.Reflection.Assembly.GetExecutingAssembly().CodeBase).LocalPath);
//Copy the file from the .exe location to the %AppData% folder
System.IO.File.Copy(
System.IO.Path.Combine(exePath, fileName),
System.IO.Path.Combine(path, fileName));
}
}















Remember to add a call to CreateIfNotExists as the first line in the Main method:

static void Main(string[] args)
{
CreateIfNotExists(dbFileName);









You can now use ClickOnce, XCopy or an Installer to deploy your app (the files in the release folder), with no other requirements than the target .NET Framework version. Uninstall the 4.0 runtime from your PC in order to test (and/or test on another PC without SQL CE 4.0 installed)




What we have achieved:




- Simple, self contained deployment of a single user desktop app of any type to any .NET 4.0 or higher platform (not ARM, though)




- Automated creation of the required settings in app.config via NuGet packages




- RAD (Rapid App Development) “Database First” access to a well-performing, well-documented and powerful ORM.




You can download the complete solution (without packages) from here.

21 comments:

Vojtech Dohnal said...

Great post! Working for me. Thank you, Erik.

Vojtech Dohnal said...

One little problem: if I mark .SDF file as "Content" in VS then I get File type="Data file" in app.exe.manifest. ClickOnce then does not copy the file in the executing folder and the .SDF file thus throws not found exception. I solved this by marking .SDF as "None" and "Copy allways",

monkeydeus said...

I've followed just about every recommendation out there to get EF6 to work with SSCE 4.0, including these here, and I still get "provider cannot be found" errors. What else could be wrong?

ErikEJ said...

Unknown: Did you use my "PrivateConfig" NuGet pakage? It adds the required .config entries, and this error Means they are missing

monkeydeus said...

Erik - Yes, I did at one point. Upon opening VS2012 again I did not, so, reinstalled it. I still had trouble. I created a db. I then used the SSCE toolbox to create a model. The error dialog was very lengthy:

System.ArgumentException: The specified store provider 'System.Data.SqlServerCe.4.0' cannot be found in the configuration, or 'System.Data.SqlServerCe.4.0' is not valid ---> System.ArgumentException: Unable to find the requested .Net Framework Data Provider. It may not be installed ...


This is what my packages file currently looks like:







And I have the system.Data DbProviderFactories element in my config file. It seems something is very hosed on this installation. In the meantime, I can make a mdf file and use it with a model in VS2013, and need to upgrade to the new VS for other reasons anyhow, so I do have a workaround.

I would be happy to know the root cause of this for future reference if you can help, but otherwise, I have an effective workaround.

ErikEJ said...

Unknown: I think your sqlce 4 install is badly broken, suggest you reinstall

tonecool said...

Thanks for great post Erik. I have one problem. When I try to deploy app with ClickOnce additional files (contained in amd64 and x86 folders) are not deployed. I tried to add those files as solution content but without success (some manifest related errors ocurred). Is there any way to deploy those dll-s too with ClickOnce ?

ErikEJ said...

Tone: Add them as project content, not solution content, and they shoudl be included when you publish

Unknown said...

As always, an excellent article - thank you for so many excellent blogs and articles.

I just wanted to summarize for others to expedite clickonce deployment the steps in vs2013:
*install nuget package EntityFramework.SqlServerCompact.PrivateConfig
*As clearly stated in this article, set - project properties -> Build -> x86
*copy the folder x86 and all its contents from %Program Files%\Microsoft SQL Server Compact Edition\v4.0\Private to the top level of the project. Unlike previous deployment methods described, no other project files or references need be altered
*set each file withing x86 and its subfolder to Copy Local - Always, and Build Action - Content

Jeff said...

Do you never eat nor sleep? :-)

It might be worth mentioning that the CreateIfNotExists() call isn't needed when deploying via ClickOnce. CO handles DataDirectory internally.

Ref:
http://msdn.microsoft.com/en-us/library/d8saf4wy.aspx
http://msdn.microsoft.com/en-us/library/system.deployment.application.applicationdeployment.datadirectory%28v=vs.110%29.aspx

Zukhra said...

Thanks for such a great post, Erik.
I'm migrating a desktop application from EF4 + SSCE 3.5 into EF6 + SSCE 4.0 and have a problem with running the project on a machine without SSCE 4.0 installed.
the project builds and runs ok if the server installed, but when I uninstall it there is a build error occurs, but project runs (although, not all the time :) ).
The error is "The ADO.NET provider with invariant name 'System.Data.SqlServerCe.4.0' is either not registered in the machine or application config file, or could not be loaded.".
Config file is:






































package.config created by NuGet:







Do you know what can be wrong here? I used ObjectContext instead of DBContext. Can it be the cause of th eproblem?

ErikEJ said...

Zukhra: I cannot see your config files, pls ask in the MSDN forum

Brady Kelly said...

I'm getting an 'Unable to find package' error when trying to get the EntityFramework.SqlServerCompact.PrivateConfig package.

ErikEJ said...

Brady: just use the latest EntityFramework.SqlServerCompact.(Legacy) package, it includes my fix!

Brady Kelly said...

Is that Legacy a comment? NuGet doesn't recognise it. Sorry if I'm being thick, but just being careful.

ErikEJ said...

Brady: The Entityframework.SqlServerCompact.Legacy package is for SQL Server Compact 3.5 SP2, contributed by undersigned! :-)

Brady Kelly said...

Thanks Erik.

Unknown said...

Hi Erik,

First, thank you for your guide. I've followed your guide. However, I met an error when running the app on other pc.
"Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8854. Install the correct version of SQL Server Compact". How can I fix this error? Thanks.

ErikEJ said...

anh: It has occurred to me that doing "real" private deployment with the Entity Framework 4.0 provider is not possible without assembly redirects, and you are getting this issue because you have two conflicting SQL CE 4 versions on the target machine. Only workaround for now is to install the SQL CE 4.0 MSI on the target PC

Unknown said...

Thank you Erik.
I have benn trying your example but i keep getting this error message on the foreach

An unhandled exception of type 'System.Data.Entity.Core.EntityCommandCompilationException' occurred in EntityFramework.dll

What do you think i am doing wrong. Thank you

ErikEJ said...

Do you simply run my sample, or have you changed anything?