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.

Friday, November 15, 2013

Entity Framework 6 & SQL Server Compact 4 (1)–Workflows and tooling

In this new series, I will give an introduction to the new tooling and some of the new features in Entity Framework 6 (EF6). But lets begin with an overview of workflow and tooling option in the various Visual Studio versions, that EF6 supports.

EF6 consist of 2 major parts:

- the EF6 runtime, available via NuGet, which support Visual Studio 2010, 2012 and 2013 (version 6.0.0. of the runtime is included in the VS 2013 install, but always update to the latest released NuGet version)

- the EF Tools, which is mainly the Entity Data Model Wizard and the graphical EDMX editor and Model Browser in Solution Explorer. The EF Tools have been updated and are included with VS 2013, and you can also grab the EF6 Tools for Visual Studio 2012 here. For VS 2010, there are no updated tools.

Pawel Kadluczka (@moozzyk) from the Entity Framework team has posted an excellent, detailed blog post about changes to the tooling.

Workflows

As you may be aware, there are 4 available workflows available with Entity Framework:

Database First, where you reverse engineer an Entity Data Model (EDMX) from an existing database.

Model First, where you “draw” the data model in an empty EDMX, and the can generate database objects based on the model.

Code First, where you define data classes, and either decorate with attributes of use fluent configuration, and also define a class that inherits from DbContext.

Code Second, where you generate Code First classes based on an existing database. You can use for example the EF Power Tools to do this

You can get more information about the different workflows and how to choose one here.

SQL Compact workflows in Visual Studio

In this section I will describe how to perform each workflow in VS 2010, 2012 and 2013, as there are differences due to varying degrees of tooling support. For any of the workflows below, the first thing to do is install the Entity Framework 6 SQL Server Compact NuGet package in your project, the package name is EntityFramework.SqlServerCompact:

image

This package will add the following references to your project:

EntityFramework

EntityFramework.SqlServer (not needed for our scenarios)

EntityFramework.SqlServerCompact

System.Data.SqlServerCe (not really needed for EF scenarios)

- the reason why System.Data.SqlServerCe is not needed, is that EF6 relies on the DbProviderFactory registration of SQL Server Compact, either in machine.config or your app.config/web.config.

The package will also add required app/web .config settings in the current project, to register the SQL Server Compact Entity Framework provider for EF6. (Not the DbProvider registration, but I have a solution for that in my next blog post)

Visual Studio 2010

Since the EF6 Tools are not available for VS 2010, the only available workflow using Entity Framework 6 (which fully supports .NET 4.0) is Code First. There is an overview of Code First with SQL Server Compact, EF6 and VS 2010  available here on CodeProject. (You can of course continue to use the EF designer with EF 4 based projects). I also have a blog post on Code First here.
Please note that my recommend approach is simply to install the EntityFramework.SqlServerCompact package first, as this will include all other required packages in the project for you, rather than installing the EntityFramework package first as done in the CodeProject blog post.

Visual Studio 2012

VS 2012 has a DDEX (Server Explorer) provider for SQL Server Compact, which makes all workflows simple.

Database First: Connect to a database file in Server Explorer, and then add an “ADO.NET Entity Model” to your project. (Or connect to/create one during the wizard)

Model First: Add an Empty “ADO.NET Entity Model” to your project. Add entities etc. to the Model. When prompted during script generation (Generate Database from Model), connect to or create a SQL Server Compact database file. I have a fix for the .tt script template to make the generated script more compatible with the Toolbox. My fix will be included with the EF 6.1 tooling.

Code Second: Install the Entity Framework Power Tools from Tools/Extensions (I believe they require the EF6 Tools to be installed to work)

Visual Studio 2013

VS 2013 has no DDEX (Server Explorer) provider for SQL Server Compact, which makes all workflows a bit more complicated.There is a VS UserVoice item to bring back the DDEX provider, if you feel like voting for it.

UPDATE: The latest version of my Toolbox add-in (3.7.1, currently in beta) now installs a simple DDEX provider in VS 2013, that enables the same workflows as in VS 2012 described above. Download from here and read the release notes carefully.

Database First: Install my SQL Server Compact Toolbox add-in via Tools/Extensions. Also install SQLCE 3.5 SP2 and SQLCE 4.0 SP1 if not already present. Right click the database and create an Entity Data Model from the context menu.

Model First: Install my SQL Server Compact Toolbox add-in via Tools/Extensions. Also install SQLCE 3.5 SP2 and SQLCE 4.0 SP1 if not already present. Create an empty database file. Right click the database and create an Entity Data Model from the context menu. Add entities etc. to the Model. A SQL Compact compatible script will be generated when selecting “Generate Database from Model”. I have a fix for the .tt script template to make the generated script more compatible with the Toolbox.

Code Second: I am working on getting a pull request accepted for the EntityFramework Reverse POCO Code First Generator project. This project is an alternative to the EF Power Tools Reverse Engineer feature, but presently does not support SQL Server Compact. Once it has been accepted, you can generate Code Second classes from a SQL Server Compact database file with this tool. UPDATE: Latest version of this template now supports SQL Server Compact

NEXT: Easy Private Desktop Deployment with SQL Server Compact and Entity Framework 6.

Monday, November 11, 2013

SQL Server Compact breaks when upgrading from Windows 8 to Windows 8.1

Symptoms

1: The SQL Server Compact Toolbox now longer works after upgrading to Windows 8.1

2: Entity Framework based SQL Server Compact solutions no longer work after upgrading to Windows 8.1

Issue

When upgrading a machine that already has SQL Server Compact 3.5 SP2 and/or 4.0 installed, the DbProvider registration, which is stored in machine.config is broken, probably due to the fact, that a newer version of .NET Framework is installed (version 4.5.1) which simply overwrites the existing maching.config file. Both the SQL Server Compact Toolbox and Entity Framework depend on this registration.

Fix

The fix is to repair/re-install SQL Server Compact 3.5 SP2 and/or 4.0 SP1, both versions are fully supported on Windows 8.1/Windows Server 2012 R2.

You can download 3.5 SP2 from here: http://www.microsoft.com/en-us/download/details.aspx?id=5783

You can download 4.0 SP1 from here: http://www.microsoft.com/en-us/download/details.aspx?id=30709