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:

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.

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

SQL Server Compact Toolbox 3.6–Visual Guide of new features

After more than 210.000 downloads, version 3.6 (skipping version 3.5 to avoid confusion) of my SQL Server Compact Toolbox extension for Visual Studio 2013, 2012 and 2010 is now available for download. This blog post is a visual guide to the new features included in this release, many suggested by users of the tool via the CodePlex issue tracker

Table Builder

In order to improve the experience for Toolbox users, I have now started adding features to the Toolbox found in the Server Explorer tooling, starting with a Table Builder. This is due to the fact, that Server Explorer support in VS 2012 and VS 2013 is limited (in 2012 SQL Compact 4.0 only, and in VS 2013 none, where as Server Explorer in VS 2010 supports both 3.5 and 4.0).

To use the Table Builder, right click a database, and select Build Table:

Then specify the columns to be built, and click Script!

A CREATE TABLE script will then be displayed in the SQL editor. In the current version, it is not possible to modify an existing table, maybe next time?

If you have both VS 2012 and VS 2013, feel free to contact me for a way to re-enable Server Explorer support for SQL Server Compact 4.0 in VS 2013.

Report Viewer

You can now view the data in any table via the Microsoft Report Viewer, which also enables you to export data in Excel, Word and PDF format.

Right click any table, and select View Data as Report

You can right click the report (or use the Toolbar) to Print/Export the table data.

This featured is based on this Stackoverflow reply: http://stackoverflow.com/questions/267091/best-way-to-view-a-table-with-lots-of-columns

Generate Entity Framework 6 Entity Data Model (EDMX) in Visual Studio 2013

This feature is very similar to the existing feature for Visual Studio 2010, allowing you to do Database First development with SQL Server Compact and Entity Framework 6. The new implementation for Visual Studio 2013 takes advantage of assets from the version 6 Entity Framework designer, this includes using the included .tt files to code generate a DbContext and related POCO classes rather than a legacy ObjectContext. To use this feature, first install the EntityFramework.SqlServerCompact NuGet package in your project, and the simply right click the database you would like to generate the model for:

Then you will be presented with a very basic “Entity Data Model” dialog, which allows you to select which tables to include in the model, and to specify other relevant options:

Clicking OK will generate an EDMX file and releated .tt files etc, in the project based on the Entity Framework 6 designer way of doing this.

Other improvements and bug fixes

Scripting: Improved parsing of SELECT statements
Scripting; Proper scripting of float and real values
Scripting: Fixed some schema diff scripts bugs
UI: Improved display of database size and space available
Integration: Improved VS 2013 RTM support

SQL Server Compact 4 desktop app with simple Private Deployment and LINQ to SQL

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

By forcing your app to run using x86 always (Prefer 32-bit), which is the new default Platform target option  for apps targeting .NET Framework 4.5, deployment of SQL Server Compact with you app becomes simpler, but must follow different guidelines from what I have previously blogged about. (The same approach will also work with apps targeting .NET 4.0, just set the Platform target to x86 in the location shown below. And the same approach will also work with the SQL Server Compact 3.5 DLL files.)

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 LINQ to SQL with SQL Server Compact 4.0, a low overhead, fast performing ORM.

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.

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

1: Visual Studio 2010/2012/2013 Pro or higher 

2: SQL Server Compact Toolbox add-in (Install via Tools/Extensions in VS)

3: An existing SQL Server Compact database file, I will use Chinook, which you can download from here. 

4: The SQL Server Compact 4.0 SP1 runtime

(You could also use the free Visual Studio 2010/2012/2013 for Windows Desktop with the standalone SQL Server Compact Toolbox for 4.0, which also supports LINQ to SQL code generation)

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.

Include the SQL Server Compact binaries in your project

Now include the SQL Server Compact 4.0 binaries and ADO.NET Provider as content in your app. Copy C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Private\System.Data.SqlServerCe.dll to your project folder, and then copy all files and folders in C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Private\x86 also to your project folder.

In the Solution Explorer, select Show All Files, and include the new folder and the seven files just copied in the Project:

Now mark all the copied files (also the files in the Microsoft.VC9.CRT folder) and mark them as Content, Copy Always:

Finally, add a Reference to the System.Data.SqlServerCe.dll file in your project folder:

(Make sure to check the file location and the Version, should be 4.0.0.1)

Add your database file to the project

Make sure it is also Content, Copy Always – we use the”Database First” workflow here.

Generate the LINQ to SQL DataContext

Next,we will generate a LINQ to SQL DataContext class and related Table classes based on the database, so connect to the database in SQL Server Compact Toolbox, using the Add SQL Server Compact 4.0 Connection menu item:

Then right click the database and select “Add LINQ to SQL DataContext to current project”:

(I am just using ChinookContext as Context name)

Click OK, and a DataContext class file will be added to your project, and the required reference to System.Data.Linq will be added to the project.

Now let us add some test code to the Main method in order to verify that everything works so far, so the Program.cs code looks like this:

using System;
using System.Data.SqlServerCe;

namespace LinqToSqlCePrivateDeploy
{
    class Program
    {
        private const string 
            dbFileName = "Chinook_SqlServerCompact_AutoIncrementPKs.sdf";
        
        private static string dbConnectionString = 
            string.Format("Data Source=|DataDirectory|{0};Max Database Size=4091", dbFileName);
        static void Main(string[] args)
        {
            using (var connection = 
                new SqlCeConnection(dbConnectionString))
            {
                using (var context = new ChinookContext(connection))
                {
                    //To log SQL statements, use:
                    //context.Log = Console.Out;
                    foreach (var album in context.Album)
                    {
                        Console.WriteLine(album.Artist.Name);
                        Console.WriteLine(album.Title);
                    }
                }
            }
            Console.Read();
        }
    }
}

We can now access the database via the generated object model, and do not have to type SQL, but can use LINQ to query the database. In addition, we can update the database (INSERT, UPDATE, DELETE) via methods on the DataContext.

Notice that the DataContext must be constructed with a SqlCeConnection object, in order for LINQ to SQL to work with SQL Server Compact 4.0.

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. In addition, we can do this without storing any connection strings in app.config, making the app more self-contained. 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.

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, with no other requirements than the target .NET Framework version.

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)

- No need for special incantations in app.config

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

You can download the complete solution from here; http://sdrv.ms/179QBaa

SQL Server Compact Code Snippet #20 : change database password

The SqlCeEngine class (in the System.Data.SqlServerCe ADO.NET provider) includes a number of useful methods for handling global database chores, like verifying the integrity of the database, and shrink the database file. It also includes the Compact method, which creates a new database file (optionally in-place) of an existing database file. During this process, you can specify a number of database creation parameters, namely the collation order, the case sensitivity or the password of the database. In code:

    using (SqlCeEngine engine = 
        new SqlCeEngine(@"Data Source=C:\temp\mysecretdb.sdf;Password=oldpassword"))
    { 
        engine.Compact("Data Source=;Password=newpassword");
    }

Notice the special data source key used, specifying that the new database should be created in-place.

SQL Server Compact Code Snippet #19 : migrate a SQL Server database to SQL Compact

This snippet again demonstrates my SQL Server Compact scripting API, some initial guidance here. For a blog post describing the opposite direction, see this. Notice, that is you are scripting a SQL Server Compact 4.0 database file, you can install the required DLL files via NuGet (ErikEJ.SqlCEScripting).

This time I will demonstrate how to migrate a complete SQL Server (LocalDB/Express/Full) database to SQL Server Compact. The requirements are simply that the current user has read access to the SQL Server database. Then all tables, constraints, indexes and data will be moved to an empty SQL Compact database, all in just 6 lines of code:

using (IRepository serverRepository = new ServerDBRepository4(@"Data Source=.;Trusted_Connection=true;Initial Catalog=Chinook"))
{
    string fileName = Path.GetTempFileName();
    var generator = new Generator4(serverRepository, fileName);
    generator.ScriptDatabaseToFile(Scope.SchemaData);

    var helper = new SqlCeHelper4();
    var sqlCeConnectionString = @"Data Source=C:\temp\newdb.sdf";
    helper.CreateDatabase(sqlCeConnectionString);
                
    using (IRepository sqlCeRepository = new DB4Repository(sqlCeConnectionString))
    {
        sqlCeRepository.ExecuteSqlFile(fileName);
    }
}

The code requires the following using statements:

using ErikEJ.SqlCeScripting;
using System.IO;

The ServerDBRepository constructor simply requires any valid SQL Server ADO.NET connection string.

The ScriptDatabaseToFile creates a script file with all content of the database, and the ExecuteSqlFile method runs the script against a SQL Server database.

Notice the use of the SqlCeHelper4 class, which creates an empty database file.

Primeworks, supplier of tools for SQL Server Compact, is closing, making all products free, open source

João Paulo Figueira, owner of Primeworks, http://primeworks-mobile.com/, just announced that the company is closing. In a blog posting João announced yesterday: “The business volume has decreased so steeply that it can no longer support the product development and support.”

Primeworks offers complete set of excellent tools for working with all versions of SQL Server Compact database files, both on the desktop and directly on a Windows Mobile/CE device.

Luckily, the tools will continue to be available for download, and eventually even become open source.  João states: “In the very near future, all products will be removed from our online business platform and the licensing requirements removed from the distribution installers. All products will be essentially free (copyrights retained). Next, I will devote some time to publish all the source code and convert these products into open source projects.”

I am sorry to see Primeworks leave the market, but very happy that the excellent tools will now become available for free.