Monday, October 20, 2014

Entity Framework 6 and SQL Server Compact 4.0 – “Proper” private desktop deployment

About a year after I published the blog post Entity Framework 6 & SQL Server Compact 4 (2)–Easy Private Desktop Deployment it has occurred to me that the solution proposed in that blog post is far from perfect – my apologies! The suggestions for location of the database file etc. are still valid points, however.

TL;DR – Use my new NuGet package for Private Desktop deployment with SQL Server Compact 4.0 and Entity Framework 6, and make sure that:
- The binding redirect is set to: oldVersion="4.0.0.0-4.0.0.1"
- Prefer32Bit project option is disabled

The goal

First, let’s clarify what the goal behind “private desktop deployment” with SQL Server Compact is:

The goal is to be able to drop a folder of files on any PC and just run  a .NET application that includes a self-contained relational database and a sophisticated ORM over that database.

This goal is achievable with SQL Server Compact 4.0 and Entity Framework 6, as long as the PC in question has .NET Framework 4.0 installed.

The issues


First, let us look at the issues with the approach in the above blog post:
1: This approach uses the Microsoft.SqlServer.Compact package, which explicitly uses the non-private assembly version of System.Data.SqlServerCe.dll (which is 4.0.0.0). And it assumes that your project targets AnyCPU. This means that if you have SQL Server Compact installed in GAC, then the version from there will be picked up. That will work fine, as long as you have the exact same build of SQL Server Compact in both GAC and your application folder. But this leads us to issue number 2!
2: The EntityFramework.SqlServerCompact package does not depend explicitly on a release version of the Microsoft.SqlServer.Compact NuGet package and will pick up build 8854, which is 4.0 SP1 CTP1, instead of build 8876, which is 4.0 SP1 proper. (I wish that the 8854 builds were no longer visible on NuGet) – I am working on a fix for this, which is planned for EF 6.1.2. So the chances of not having the same version in GAC and locally are relatively big, and this will cause an error to be thrown, preventing your app from working:
Possible file version mismatch detected between ADO.NET Provider and native binaries of SQL Server Compact which could result in an incorrect functionality.


Manual fix

If you would like to ensure that your desktop application (console, Windows Forms or WPF) app can run anywhere use proper private deployment, follow these steps (in outline):

1: Install EntityFramework.SqlServerCompact

2: Copy files from Private folder in C:\Program Files (x86) to the project folder (as described in my Private deployment with LINQ to SQL blog post)

3: Make project x86 only (or at least remove the “Prefer 32 bit” option if you include all unmanaged dll files 

4: Disable post build event (via Project Properties/Build events tab) to prevent the 4.0.0.0 dll from being deployed

5: Modify app.config

a) add assembly redirect, as the EntityFramework.SqlServerCompact.dll references version 4.0.0.0, and we want to use 4.0.0.1:

<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" culture="neutral"/>
<bindingRedirect oldVersion="4.0.0.0-4.0.0.1" newVersion="4.0.0.1"/>
</dependentAssembly>
</assemblyBinding>
</runtime>

b) With version 4.0.0.0 => 4.0.0.1 in DbProviderFactories section:

 

“Automatic” fix

I have published a NuGet package EntityFramework.SqlServerCompact.PrivateDeployment  that performs most of the actions above, specifically 1, 2 (via a clever package that performs a build step), 3 (via an additional command in install.ps1), 4, and partly 5 (for some reason NuGet generates an incorrect bindingRedirect, so it must be fixed manually). Currently in pre-release, please provide feedback!

image

This package includes both the EntityFramework.SqlServerCompact provider for EF 6.1.1 and the SQL Server Compact 4.0 files for private deployment. I intend to update the package with the upcoming version 6.1.2 release of Entity Framework.

35 comments:

driv said...

We are currently trying to do something similar to your new package. Though we have a separate custom version of the SQL CE package containing the 4.0.0.1 assembly. We also want to use it without EF.

AFAIK Nuget just tells Visual Studio to add a reference to the assembly. So it is Visual Studios responsibility to add a binding redirection to app.config.

Is it really important that we fix 0.0.0.0-4.0.0.1? What problem scenarios could be caused by this?

It is a problem to fix this for all dependent projects including indirectly dependent and unit test projects. In our setup this is way over 100 files!

ErikEJ said...

Driv: An EF error occurs, I think it is due to the precense of the SQL CE 3.5 provider in my machine.config, where EF finds the first one:








So if you do not have 3.5 installed, or do not use EF, I think it will not be an issue.

ErikEJ said...

Driv: Logged an issue here - https://entityframework.codeplex.com/workitem/2562

Steve C said...

Great blog and a really useful Nuget package. I am trying to write a private deployment in a windows service.

I have installed the package in the service and checked that the files are located in same folder as the service once deployed.

I keep getting the error "Failed to find or load the registered .Net framework Data provider" Any ideas? I'm stumped so any pointers would be greatly appreciated

ErikEJ said...

Have you tried with a console app? If that Works, check the rights for the service account

The Crotchety Old Bastard said...

Do you have a version to support ef 6.1.2?

ErikEJ said...

Working on it.., but are you facing any issues with 6,1,1? Not sure 6,1,2 will improve on that...

ErikEJ said...

Pre-release version for 6.1.2 now available

ErikEJ said...

RTM version for 6.1.2 now available

EJ said...

Hi,

Thank you for your help and congratulations for the great job.

I'm finally able to use the Entity Designer to generate my SQL code.

However I still have the following error when running the application:

'System.BadImageFormatException' occurred in System.Data.SqlServerCe.dll

Any ideas?

PS: After I generate the SQL Code, do I need to paste it in the SQL Server Compact Toolbox or there is a way to execute it directly?

Thanks

ErikEJ said...

EJ: You must extecute in the Toolbox editor, and possibly use the "Ignore DDL errors" button. BadImageException: Change your app from AnyCPU to x86.

Mike said...

1) Created a Class Library project
2) Added your EF for Compact Private Deployment package
3) Set
4) "Add" > "New Item" > "ADO.NET Entity Data Model"
5) "Code First from Database"
6) "New Connection..." > point to Private Deployment provider and path to my SQLCE4.0 database file
7) "Next"...

I get an error on "Which version of Entity Framework do you want to use?" page saying "Your project references the latest version of Entity Framework; however, an EF database provider compatible with this version could not be found for your data connection..."

What am I doing wrong?

ErikEJ said...

Mike: Build the project before running the EDM wizard. And install the EF 6,1,3 Tools

ErikEJ said...

I will publish a 6,1,3 package this weekend

Mike said...

Fixed the error I was getting, but now nothing happens upon completing the wizard. Have tried different SDF's and let the system wait for several minutes (in case it was just slow) but nothing gets generated (no code, no connection string in config) and no indication of errors.

ErikEJ said...

Mike: Suggest you as on CodePlex or StckOverflow or MSDN, blog comments are not ideal for support issues. And provide as much detail as possible, please

Brian S said...

I am getting the same error as mike. "nothing happens upon completing the wizard. Have tried different SDF's and let the system wait for several minutes (in case it was just slow) but nothing gets generated (no code, no connection string in config) and no indication of errors." Mike did you find a solution?

ErikEJ said...

Brian S: Did you try the equivalent feature in the Toolbox? Did you install the 6.1.3 Tools (not only runtime) ?

ErikEJ said...

Brian S: Check in the Toolbox About dialog that your SQL Compact runtime is properly installed

Brian S said...

Thank you for getting back to me.

I did try the equivalent function in the tool box and it did create the edmx files for me. However I was only able to select entire tables. I was expecting a designer interface somewhere I could select individual fields. When I double click on one of the edmx files I get an error can not load c:\my program path\myfile.edmx: exception has been thrown by the target of an invocation.


Not sure exactly what I am looking for but in the about dialog for the toolbox I have
SQL Server Compact 4.0 in GAC -Yes-4.0.8876.1
SQL Server Compact 4.0 DbProvider - Yes
SQL Server Compact 4.0 DDEX provider - No
SQL Server Compact 4.0 Simple DDEX provider - yes
SQL Server Compact 4.0 Engine test: PASS!

ErikEJ said...

Brian: Could you try to reinstall SQL CE 4?

Brian S said...

I went to my Nuget manager and removed EntityFramework.SqlServerCompact.PrivateDeployment

But leaving the 2 dependencies
Entityframework 6.1.3 and baseclass.contrib.nuget.output 1.0.7

Im guessing this removed only entityframwork.sqlserver.compact

And now things seem to be working. Any ideas? Is this going to cause me any issues down the road?

ErikEJ said...

Brian: you need the sql ce provider.. but please log an issue on codeplex, this is not a good forum for support

Unknown said...

Hey ErikEJ,was wondering if you could lend me your brain for a minute. I really want to password protect my sdf and also use entity. How do I accomplish using the entity and designer tools but having the password outside of the app.config file? Also, if not the app.config file then where can it go and securely? Currently I have a singleton class that handles the opening/closing of the SDF but with Entity could I still use something like that? Any thoughts would truly be appreciated, Thank you so much for your personal time invested in this system. It has been a lifesaver.

ErikEJ said...

Unknown: You simply need to build the connection string during runtime, but securing the password is another (complex) issue - this book has some ideas: https://www.microsoft.com/learning/en-us/book.aspx?id=10294

Jürgen Steinblock said...

FYI - I figured out why your transform does not work.

My app.config already contained a section

[runtime]
[assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"]
[dependentAssembly]
[assemblyIdentity name="EntityFramework" publicKeyToken="b77a5c561934e089" culture="neutral" /]
[bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0" /]
[/dependentAssembly]
[/assemblyBinding]
[/runtime]

And this is messed up like this

[runtime]
[assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"]
[dependentAssembly]
[assemblyIdentity name="EntityFramework" publicKeyToken="b77a5c561934e089" culture="neutral" /]
[bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0" /]
[assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" culture="neutral" /]
[bindingRedirect oldVersion="4.0.0.0-4.0.0.1" newVersion="4.0.0.1" /]
[/dependentAssembly]
[/assemblyBinding]
[/runtime]

It also includes an

[dependentAssembly]
[assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" culture="neutral" /]
[bindingRedirect oldVersion="0.0.0.0-4.0.0.1" newVersion="4.0.0.1" /]
[/dependentAssembly]

entry but I could bet this is done from nuget automatically.
So basically your transform appends the first entry, I am not sure you can achive what you want with a transform. Your best bet would be a hook in the install.ps1 to replace oldVersion="0.0.0.0-4.0.0.1" with oldVersion="4.0.0.0-4.0.0.1"

ErikEJ said...

JUrgen: I think I have a solution for that: https://sqlcetoolbox.codeplex.com/workitem/12075 - maybe time for an updated package

Jürgen Steinblock said...

@ErikEJ

Well the problem is the messed up app.config. You should at least remove the transform for your package.

I can't tell if removing System.Data.SqlServerCe.3.5" will solve the inital problem. I will try that next week.

The worst thing is, compiling and runnign an app works fine with the messed up app.config file with a dependentAssembly section with two bindingRedirect entries but in a test project this caused all tests to disappear from mstest test window.

running mstest from console showed an totally unrelated

File or assembly "Microsoft.VisualStudio.QualityTools.Tips.UnitTest.AssemblyResolver, Version=12.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" or a dependency not found Unknown error (HRESULT: 0x80004005 (E_FAIL))

error

ErikEJ said...

Jurgen: I am trying to understand your exact problem. Could you email me some info on what your app.config looks like before and after installing my package. TIA

bhaskar shelar said...

Hi,
I have to use code From Database of EF6.1.3 with SqlCE 4.0 but it gives a version compatibility issued I gone through your tutorial but still finding the exact solution Hoping For Your help

ErikEJ said...

Bhaskar: Suggest you post more info on a Github issue

Harry Patel said...

I am using SQL compact 3.5 db. my vb app I'd like to fill million row using ADO.net

For ex: CustomersTA.fill(Customers)

How would i do this faster way?

Pleaase advice me is there any other way to do it.

Thanks
Harry

ErikEJ said...

Hi Harry, you can use my SqlCEBulkCopy library

Harry Patel said...

Thanks for your reply.

Let me explain with real example. Local .sdf file. File as all data.

There is .sdf file in the solution windows app with 20 tables. I am filling all tables using Fill() on by one at once when application starts. The only reason I am doing this coz, then application run quick as all data is in object.

see example below
Public Contractors As New ds.ContractorsDataTable
Public ContractorsTA As New dsTableAdapters.ContractorsTableAdapter
ContractorsTA.Fill(Contractors)

If i try to ready using sql query program goes slower. When I test this in SSD drive its faster but normal drive is 4 times slower.

Can you also provide me URl to look at SQLbulkcopy code.

ErikEJ said...

harry -suggest we take this discussion on github instead: https://github.com/ErikEJ/SqlCeToolbox/issues/167