Entity Framework 6 & SQL Server Compact (3)–Getting started with the SQL Server Compact 3.5 provider (and Merge Replication)

As you may know, SQL Server Compact version 4.0 does not have support for Merge Replication and Remote Data Access (you can still use RDA, however). This was a showstopper if you wanted to use the latest version of Entity Framework, as up until now, only SQL Server Compact version 4.0 was supported with Entity Framework 5 and 6. But now a SQL Server Compact 3.5 provider for Entity Framework 6 is available, currently as pre-release on NuGet.

The new 3.5 provider is based on the exact same codebase as the 4.0 provider, and thus has all the new features (SqlCeFunctions, Migrations etc.), bug fixes and performance improvements included in the 4.0 provider. In addition, this new provider supports IDENTITY Keys, just like the 4.0 provider. (A showstopper for many with the present 3.5 provider). As it is based on the same codebase as the 4.0 provider, any future improvements and bug fixes will also be included with this provider.

This blog post will describe how you can get started with the new 3.5 provider with Entity Framework 6, and will also include some pointers on getting started with Merge Replication. I will assume that you have Visual Studio 2012 or 2013 Professional or higher installed, and also have my SQL Server Compact Toolbox add-in installed. If you are using Visual Studio 2012 and Database First, you must also have the Entity Framework 6.0.2 Tools installed, download from here. And of course you must have the SQL Server Compact 3.5 SP2 Desktop runtime installed.

So let us create a new console app, that uses Entity Framework 6 and SQL Server Compact 3.5, and which could potentially be a Merge Replication subscriber.

Create a new Console project:

For this walkthrough, we will use Database First, but you can of course also use Code First.

Add the EntityFramework.SqlServerCompact.Legacy NuGet package to the project (remember to allow pre-releases), by right clicking the project and selecting Manage NuGet Packages…

This will install Entity Framework 6.1-alpha1 and the 3.5 provider and add the required registration in app.config.

Build the project.

Connect/create the database that you want to use in the SQL Server Compact Toolbox, right click it, and select “Add Entity Data Model to current Project”

Just click OK:

This will add Chinook.edmx and invoke code generation that builds a DbContext derived class and POCO classes for each table in your database.

You can now add Merge Replication to your solution, you can start by installing my Merge Replication client helper library via NuGet, http://www.nuget.org/packages/ErikEJ.SqlCeMergeLib/, read more about it here: https://sqlcemergelib.codeplex.com/

In order to configure Merge Replication on your SQL Server database and web server, I have a brief blog post here, but otherwise I can highly recommend the book by Rob Tiffany.

Notice that if you want to add Merge Replication to a SQL Server 2012 database, you need SP1 and CU4 or later, and you will need a recent build (8088 or newer) of the SQL Server Comapct 3.5 runtime installed, as listed in my blog post here.

Hopefully you will now be able to get started with this combination of the latest Microsoft data access technology and  “legacy” technologies like Merge Replication and Sync Framework. If you have any questions, please ask in the MSDN forum or Stack Overflow.

SQL Server Compact Toolbox 3.7–Visual Guide of new features

After more than 235.000 downloads, version 3.7 of my SQL Server Compact Toolbox extension for Visual Studio 2013, 2012 and 2010 is now available for download (and available via Tools/Extensions in Visual Studio). 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. A major “theme” for this release has been to improve the experience for Visual Studio 2013 users, which has no Server Explorer/DDEX support for SQL Server Compact at all.

Add Column, Index and Foreign Key to table

As Server Explorer support is completely missing in VS 2013, and only available for 4.0 in VS 2012, I have added dialogs for building script to add columns, indexes and foreign keys to a table. Right click any table, and select the desired dialog:

 

Note that currently the Index and Foreign Key dialogs only support a single column, I am aware of this, so please vote here and/or here.

Generate Entity Framework 6 Entity Data Model with VS 2012 and SQL Compact 3.5

The code generation menu items have now been moved to a sub-menu:

In addition, as the Entity Framework Tools are now available for Visual Studio 2012 as well as Visual Studio 2013, it is now possible to generate a “database first” Entity Framework model (EDMX) based on a SQL Server Compact database in VS 2012. And I have just contributed an Entity Framework SQL Server Compact 3.5 provider (available as prerelease on NuGet), that allows you to use SQL Server Compact 3.5 with Entity Framework 6. 

Copy database file

It is now possible to copy a database file, and then for example paste it into your project, should you so desire, simply by pressing Ctrl+C on the selected database, or selecting the context menu item:

Data only export to SQL Server

The current data export feature has been enhanced to support SQL Server and IDENTITY columns by using DBCC CHECKINDENT instead of the unsupported ALTER TABLE statements currently used.

New "ALTER column" context menu item

To help you modify columns, a new context menu to script ALTER COLUMN statements has been added:

“Database designer”

I am now hosting a web based database designer based on http://code.google.com/p/wwwsqldesigner/ (which implements only the SQL Server Compact column data types). Current state is “alpha” (and there are known issues with IE 11)!

Server DGML now has table selection

When generating a database diagram for a SQL Server database, you can now select which tables to include in the graph, which can be helpful when diagramming databases with many tables. I have already blogged about this earlier.

Other improvements and bug fixes

Missing database files indicated in tree view
No longer depends on the SQL Server Compact 3.5 runtime, and no longer depends on DbProviderFactory, simply requires either 3.5 or 4.0 in GAC
Each Query opens in new window
BUG: "Upgrade to 4.0" was blocked in Visual Studio 2013
BUG: Not ideal support for Dark Theme in VS 2012/2013 (improved slightly)
BUG: EF6 EDMX code generation with VB was broken

SQL Server - using newsequentialid() as a function, similar to newid()

This blog post shows how you can use newsequentialid() as a function in scripts etc., not only as a column default value.

In many scenarios, unique identifiers are used a clustered, primary keys in database tables for various reasons. This blog post will not discuss the pros and cons of doing this.

Usage of GUID/uniqueidentifer and it’s implication on fragmentation, and how newsequentialid() can help improve this, has been documented in various places

A limitation of newsequentialid() is that it can only be used as a default value for a column, not as a function, in for example ad-hoc INSERT scripts. By taking advantage of SQLCLR, this situation can be changed.

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

public class SqlFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlGuid newsequentialid()
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            var sql = @"
DECLARE @NewSequentialId AS TABLE (Id UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID()))
INSERT INTO @NewSequentialId DEFAULT VALUES;
SELECT Id FROM @NewSequentialId;";
            using (SqlCommand cmd = new SqlCommand(sql, connection))
            {
                object idRet = cmd.ExecuteScalar();
                return new SqlGuid((Guid)idRet);
            }
        }
    }

}

The code above implements a SQLCLR function named newsequentialid(), To build this code, simply create a C# class library, include the code, and build. The code is inspired by this thread on SQLServerCentral: http://www.sqlservercentral.com/Forums/Topic1006731-2815-1.aspx

To make deploying the function even simpler, the script outlined below can add the assembly code to your database and register the function:

EXEC sp_configure @configname=clr_enabled, @configvalue=1;
GO
RECONFIGURE;
GO

IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SqlFunctions' and is_user_defined = 1)
CREATE ASSEMBLY [SqlFunctions]
FROM 0x4D5A… (rest omitted, use full script)
WITH PERMISSION_SET = SAFE

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newsequentialid]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
    CREATE FUNCTION [dbo].[newsequentialid]()
    RETURNS uniqueidentifier
    AS EXTERNAL NAME [SqlFunctions].[SqlFunctions].[newsequentialid];
' 
END
GO

You can download the full script from here: http://sdrv.ms/1hhYDY1

Testing with 50.000 inserts, like in the CodeProject article reveals the following figures:

Newsequentialid as DEFAULT:
Run time: 1:18, pages: 1725, fragmentation: 0,7 %

Newsequentialid as function in INSERT statement, no default value on table:
Run time: 2:03, pages: 1725, fragementation: 0,7 %

To use the function as a replacement for newid(), simply use dbo.newsequentialid() instead. But please also consider using another column as clustering key in your table…