Thursday, January 23, 2014

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:

image

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…

image

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”

image

Just click OK:

image

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.

20 comments:

jamome said...

First, thanks for your blog!!

It seems like (years ago) SQLCE was vocalized pretty well by Microsoft, but since then... not much. Same for new SQLCE versions. (And SQLite was pushed by MSFT for Windows 8 and 8.1)

Do you know if there will be new versions of SQLCE? Or maybe 4.0 is the final/terminated version?

Thanks again,
Jared

ErikEJ said...

Jared: I think the last version will be the current 4.0 SP1

h4ppy4l said...

I'm following this blog entry to add an Entity Data Model to my project using version 3.7.1.0 of the SQL Server Compact Toolbox in VS2012. On the point of adding the edmx clicking the option in the menu generates this error message.

System.NullReferenceException: Object reference not set to an instance of an object.
at ErikEJ.SqlCeToolbox.Helpers.EnvDTEHelper.GetVisualStudioInstallationDir()

I've tried reinstalling the packages and the SQLCE runtime but nothings seems to get me past this step. Any ideas what I might doing wrong?

Thanks

Al

ErikEJ said...

Al: It is a bug, a updated version of the Toolbxo add-in is available here with a fix: https://sqlcetoolbox.codeplex.com/releases/view/118654 - let me know if this Works for you.

h4ppy4l said...

Nice! That worked a treat, I have my edmx back now. Thanks for your efforts.

Al

h4ppy4l said...

I have moved on to setting up SQL Sync with my 3.5 database. After successfully synchronizing my database I thought I would go back and update my edmx from the database using the right click option on the edmx file in VS2012. This was to add the sql sync metadata tables to the model. When I tried I got the following message.

Unable to convert runtime connection string to its design-time equivalent

Reading around the web suggests that this option should work with VS2012 but have I got that confused?

Thanks

Alan

ErikEJ said...

Alan: Install the latest version of my add-in, which adds DDEX providers for 3.5 to VS 2012 and 213, and wait for the 6.1 Tools to release, as described in my blog post here: http://erikej.blogspot.dk/2014/02/sql-server-compact-toolbox-371visual.html (and related)

ErikEJ said...

Alan: Correction, you just need to get the latest nightly myget build of the EntityFramework.SqlServerCompact.Legacy provider

h4ppy4l said...

I tried as you suggested but no luck. clicking the help icon in the toolbox explorer tells me I don't have the DDEX installed for 3.5, just the simple one is that correct?

I tried re-installing the toolbox from your earlier response but that didn't help.

Thanks for your help!

Alan

ErikEJ said...

Alan: Please create an issue on Codeplex, and provide me the EF provider version you use and a screenshot of your about box.

Matthew Foos said...

Why does it require .NET 4?.....

We are trying to use this on a wimo 6.5.3 device to no avail..... Thoughts?

The wimo project doesn't know about the data contract serializer. What piece am I missing here.....?

ErikEJ said...

Matthew: Many piceces are missing - a WiMo 6.5 device runs ".NET Compact Framework" and many desktop APIs are not available there. But you can use ADO.NET and Merge replication with SQL Compact, just not EF.

Matthew Foos said...

Hey Eric, thanks for the quick response. So EF on wimo 6.5.3 is a no go then, correct?

I inherited a train wreck and have been cleaning it up day by day. I have 10 days to add this new feature so I Added linqbridge which was fantastic and put in a nice business layer. (We have to demo August 31st)

Now I'm looking at the DAL which is currently inline SQL all over the place free for all. (Utter disaster....)

Do you have any suggestions for a wimo 6.5 ORM? I'll shoot you an Amazon gift card if you can help me on this! :) Thanks, Matt

ErikEJ said...

Matthew: Inline SQL is the norm on Windows Mobile! But you can use the nice http://orm.codeplex.com - suggest you write a review of my tools here as thanks rather than a gift card: https://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1/

Matthew Foos said...

Unfortunately orm.codeplex.com (OpenNETCF.ORM) is exclusively code first and does no table reflection to build the EDM. I am going into work tomorrow to give this a spin: http://sqlcecodegen.codeplex.com/ (any thoughts?)

Once this DAL is done I can clean up up the UI. I'm looking at: http://www.resco.net/mobileformstoolkit/ OR
http://beemobile4.net/products/ipack (ever use either?)

This looks REALLY fancy.... but I'm worried about the HW Acceleration:
http://silvermoon.codeplex.com/

And I will head over and review tomorrow.... (Note: I actually installed your tools on Wednesday - Fantastic, fantastic stuff!)

ErikEJ said...

Orm.codeplex.com actually has a desktop reverse engineer to, so it is not "exclusively code first". http://sqlcecodegen.codeplex.com (maybe by a former colleague of mine) is not an ORM, but it will give you a strongly type DAL, where you can extend with what you may be missing (in handwritten SQL!) I have never used any of the UI libs, always used standard MS controls or my own.

Matthew Foos said...

Ok, one last set of questions:
Can you send a link to the Orm.codeplex.com desktop reverse engineer?
And can then be used on the windows mobile side? (I'd like to use that library)
(Note: sqlcecodengen says it generates CRUD... I'm looking more for basic CRUD than a full featured ORM - thoughts?)

You have to take a look: (really impressive... but will it hook into the regular screens nicely - that main menu is awesome! Take that iPhone)
https://www.youtube.com/watch?v=0OQwo0nt_ek
https://www.youtube.com/watch?v=C3NyG4-wt6U

Thanks Again Erik

ErikEJ said...

Matthew: It is just part of the codebase, but currently down for maintenance. Yes, it just generates code! You know better than I what your requirements are.

Matthew Foos said...

I DO!.... (well, maybe.... Again, this WiMo 6.5 mess was handed to me a week ago so I've just jumped into this arena)

Anyway, I can't find anywhere showing Orm.Codeplex.Com can reverse engineer - if you find something that shows it generating classes from the sdf and running on windows mobile I'd super appreciate brother.

Thanks Again Erik, Matt

ErikEJ said...

http://orm.codeplex.com/releases/view/117097 - it is called EntityGenerator - you run it on the desktop to generate code, not on a device