Entity Framework 6 & SQL Server Compact (7) –New features and fixes in version 6.1

Entity Framework 6.1 is now available on NuGet, and this blog post by Rowan Miller, Program Manager for the Entity Framework team, covers the major new features in this release. Most of the new features are also applicable to SQL Server Compact users, for example Code First Model from Database in the EDM Wizard, which is also available with SQL Server Compact if the latest SQL Server Compact Toolbox version is  installed. Rather than repeating the blog post from Rowan, this blog post will look at some of the minor features in EF 6.1 affecting SQL Server Compact. (A few of which I have had Pull Requests accepted)

#824 Designer: DDL Generation Template for SQL CE should separate each statement with GO

This fix allows you to run the script created by the “Generate Database from Model” EF Tools feature in the latest version of the Toolbox, as described here.

#898 Reverse Engineer Code First: Using database name as entity container name causes issues when database name has invalid chars (bad for SQL Compact)

This fix effectively makes “Code First from Database” work with SQL Server Compact, as it was previously broken.

#1322 SqlCePropertyMaxLengthConvention should be updated to reflect changes in the standard PropertyMaxLengthConvention

This fix makes code more portable between database engines

#1852 Migration for EF 6.0 wrong migration script for altering property (required->non required) for SQL CE 4

This fix ensures that NULL or NOT NULL is always specified in migration SQL when changing between [Required] and not required

#1863 Add DbProvider registration to SQL Server Compact NuGet package

This fix enables private deployment of SQL Server Compact, both for use in desktop, web and unit test scenarios (and makes the workaround I describe here obsolete)

#1878 Add SQL Server Compact 3.5 provider + NuGet package

This feature enables you to use SQL Server Compact 3.5 with Entity Framework 6.x, via the EntityFramework.SqlServerCompact.Legacy NuGet package, and includes all the same fixes and features as the SQL Compact 4.0 provider. I blogged about the new provider earlier.

#1962 SQL Server Compact data provider for EF does not support some Entity SQL canonical functions

It turned out, that the current SQL Server Compact data provider only supported the “canonical” functions defined for EF 1.0  – this fix adds support for most applicable functions from EF 4. The following functions are not supported due the SQL Server Compact having a limited number of data types and built-in functions:  StDev, StDevP, Var, VarP, Reverse, CurrentUtcDateTime, CurrentDateTimeOffset, GetTotalOffsetMinutes, CreateDateTimeOffset, CreateTime, AddMicroseconds, AddNanoseconds, DiffMicroseconds, DiffNanoseconds

SQL Server Compact Toolbox 3.7.2–Visual Guide of new features

After more than 260.000 downloads, version 3.7.2 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. The focus for this release is a number of features that help improve the experience when using the new support for SQL Server Compact 3.5 (supplied by yours truly) in Entity Framework 6.1 and the related Entity Framework 6.1 Tools.

Run “Generate from Model” script without errors

When using the “Generate Database from Model” option in the Entity Data Model Tools, the tool generates a script to DROP and CREATE the generated objects in the database. If you have tried to run the script until now in the SQL Server Compact Toolbox, the script would fail. A new option “Ignore DDL errors” in the Toolbox SQL Editor will allow you to run the script without any errors:

DDEX install requires VS restart

The simple DDEX providers supplied by the Toolbox for VS 2012 and VS 2013 are installed when the Toolbox is activated, and therefore require VS to be restarted to be used. A notification is now available to notify you of this. You can read more about the DDEX providers in my previous blog post here and here.

(Also, notice the new, “modern” icons in the toolbar of the Toolbox.)

Preserve SQL Server date types

A new option is now available to control scripting of the SQL Server date, datetime2 and datetimeoffset data types. The default behaviour is to always convert these column types to datetime (SQL Server Compact does not have these datatypes). But this can cause data loss (as datetime has limited precision) or overflow errors, as datetime only supports dates from 1753 to 9999, unlike date, datetime2 and datetimeoffset, that all support dates from 0001 to 9999.

Improved About dialog

The About dialog has been improved for readability, and contains a counter for the current number of Toolbox downloads:

Bug fixes

EDMX generation feature failed if only VS 2012 and not VS 2013 was installed.

"Refresh" of tables, including refresh after schema changes, was broken

Scripting API improvements

- improved SQLCE runtime check

- datetimeoffset columns scripted as datetime by default now, not nvarchar()

- sql_variant and hierarchyid handling improved, to avoid getting wrong ordinal for identity and timestamp

- sqlite indexes are now always given unique names

The scripting API improvements are also included in the latest version of my SQL Compact command line export tool and Scripting API, available here.

Feedback

As usual, please provide feedback if you have any feature requests, ideas or encounter any issues (or even bugs!!) by using the CodePlex Issue tracker.

Entity Framework 6 & SQL Server Compact (6)–Entity Framework Reverse POCO Code First Generator

The Entity Framework Reverse POCO Code First Generator is a customizable T4 template, that reverse engineers an existing database and generates Entity Framework Code First POCO classes, Configuration mappings and DbContext It is available directly in Visual Studio via the Tools, Extensions and Updates menu item. It can be used with VS 2010 or later, and the latest version supports Entity Framework 6 and both SQL Server and SQL Server Compact (both 3.5 and 4.0).

This T4 template is similar to the Entity Framework Power Tools “Reverse Engineer Code First” feature, but much more versatile and flexible, and constantly updated by the developer. You can see how this project compares with the Power Tools feature here.

To get started using the template with SQL Server Compact, follow these simple steps:

Install the relevant Entity Framework NuGet package:

• EntityFramework.SqlServerCompact.PrivateConfig (EF 6.0.2 with SQL CE 4.0)
• EntityFramework.SqlServerCompact (EF 6.1 when released with SQL CE 4.0)
• EntityFramework.SqlServerCompact.Legacy (EF 6.1 when released with SQL CE 3.5 – currently available as beta)

Add a connection string to your app.config/web.config:

<connectionStrings>
   <add name="MyDbContext" 
    providerName="System.Data.SqlServerCe.4.0"
    connectionString="Data Source=C:\data\mydb.sdf" />
</connectionStrings>

In Visual Studio, right click project and select "add - new item".

Select Online, and search for "reverse poco". 

Select the "EntityFramework Reverse POCO Code First Generator" template

Give the file a name, such as Database.tt and click Add.

Edit the Database.tt file and specify the connection string name as "MyDbContext" which matches your name in app.config.

Save the Database.tt file, which will now generate the Database.cs file.

There are many options you can use to customise the generated code. All of these settings are in the Database.tt files with comments attached.