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.


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.SqlServer (not needed for our scenarios)


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.


Andrea-Dev-Italy said...

Thank you very much for instructions.
I have a project written with previous version of VS and EF.
I've done porting to VS2013 and EF6.
This project is done with model first workflow and when I try to generate database from model I've this message:
"An exception of type 'System.ArgumentException' occurred while attempting to generate the database script from the model. The exception message is: 'Unable to convert runtime connection string to its design-time equivalent. Connection string: Data Source="
I've the same message with new simple test progect.
Do you know how to solve this problem ?
Andrea Piccioni

ErikEJ said...

Andrea: Suggest you email me the simple test project, and include a step by step description of how you created the project.

Gabriel Benzaquen said...

My problem is similar to that of Andrea.
I have a ready project that works perfectly.
But when I try to use the 'generate database from model' option, the same error already described by Andrea occurs.
If anyone can help me, it would be useful

Jared said...

The answer for me to Andrea's problem was generating the database from the .edmx in VS2012, not VS2013.

Andrea-Dev-Italy said...

Hi Gabriel and Jared, Erik has created Issue 1838 on Codeplex EF project.

Hajo said...

if I 'update Model from Database' I get an Exception: "Unable to convert runtime connection string to its design-time equivalent"
I use VS2013
Can you help me.

ErikEJ said...

Hajo: As stated, this scenario is currently broken...

Eagle3386 said...

Hi Erik,

are you able to give an ETA on a possible fix? Not something like "2014-02-28", but "within 2 months" or so?

Thanks in advance!

Best regards,

ErikEJ said...

A fix that enable full EF Tools support in VS 2013 is available here , please read release notes carefully, and let me know if it works for you, I plan to update the blog post text soon...

Paulo Ricardo said...

Hi, ErikEJ!

I use VS2013 Express Edition, so I'm not able to install your add-ins concerning to SQL CE. :-(

Although, I've installed your SQL CE 4.0 Toolbox and used it to create a couple of database to use in a Web MVC application.

This way, what would be the best approach to generate the model (edmx) based on the previously created SQL Server CE databases?

Thanks for your time.

Best regards.

Paulo Ricardo Ferreira

ErikEJ said...

Paulo: Currently, you can use edmgen2 (available as code on github) to create the edmx. PLlease email me directly for an alternative to that, that I cannot share publicly right now