Entity Framework Core Extensions

This is a list of some of the available Entity Framework Core extensions out there that expand the functionality of the Entity Framework Core runtime. Only libraries that extend DbContext or similar are included, and they must be published on NuGet.org.

The EF team has lists of third party EF Core providers here: https://docs.microsoft.com/en-us/ef/core/providers/

For a similar list of EF 6.x extensions, see my blog post here

Please let me know if I have missed anything, and I will add it to the list.

EFSecondLevelCache.Core

Entity Framework Core Second Level Caching Library.

Second level caching is a query cache. The results of EF commands will be stored in the cache, so that the same EF commands will retrieve their data from the cache rather than executing them against the database again.

Install-Package EFSecondLevelCache.Core

EntityFrameworkCore.Detached

Loads and saves entire detached entity graphs (the entity with their child entities and lists). Inspired by GraphDiff. The idea is also add some plugins to simplificate some repetitive tasks, like auditing and pagination.

Install-Package EntityFrameworkCore.Detached –Pre

EntityFrameworkCore.Triggers

Add triggers to your entities with insert, update, and delete events. There are three events for each: before, after, and upon failure.

Install-Package EntityFrameworkCore.Triggers

EntityFrameworkCore.Rx

Reactive extension wrappers for hot observables of Entity Framework entities.

Install-Package EntityFrameworkCore.Rx

EntityFrameworkCore.PrimaryKey

Retrieve the primary key (including composite keys) from any entity as a dictionary.

Install-Package EntityFrameworkCore.PrimaryKey 

EntityFrameworkCore.TypedOriginalValues

Get typed access to the OriginalValues of your entity properties. Simple and complex properties are supported, navigation/collections are not.

Install-Package EntityFrameworkCore.TypedOriginalValues

EFCore.Practices

Attempt to capture some good or best practices in an API that supports testing – including a small framework to scan for N+1 queries

Install-Package EFCore.Practices

Getting started with the SQL Server Compact and other providers for Entity Framework 7 beta 6

In connection with the recent release of Entity Framework 7 (EF7) beta 6 on NuGet, I have contributed two SQL Server Compact providers that work with this beta release (in close collaboration with and with great support from the EF7 team.)

Notice that the general “EntityFramework” package for EF7 is no longer available. Going forward you always install the desired provider packages as needed. For example EntityFramework.SqlServer

The SQL Server Compact providers

The SQL Server Compact providers are fully featured providers that support all required Core APIs (maybe with the exception of provider specific metadata). This includes support for returning IDENTITY values after INSERTs, Migrations, database object creation, and modelling via both the fluent API and Data Annotations. It also includes support the for Reverse-Engineer command, allowing you to create an EF7 model from an existing database.

For a walkthrough of how to get started with the SQL Server Compact providers, see the wiki article here. Initial Entity Framework 7 documentation is available here (a work in progress). As always feedback is very much appreciated, suggest you use the Github issue tracker for bug reports, feature suggestions or even Pull Request discussions and submissions!

Other providers

With the current EF7 beta, the following providers are available:

EntityFramework.SqlServer Provider for SQL Server/Azure SQL Database - supplied by Microsoft. This provider will be able to take advantage of the .NET Core support for System.Data.SqlClient on Unix and Mac. Currently only SQL Server 2012 and later is supported, but support for SQL Server 2008 is planned

EntityFramework.Sqlite Provider for SQLite, mainly for devices (tablet and phone) - supplied by Microsoft. Will also work with Xamarin (Andriod and IOs)

EntityFramework.InMemory In-memory provider for use with testing, supplied by Microsoft

All the providers above are hosted here on Github.

EntityFramework.Npgsql Provider for PostgressSQL - also works on Mono. Supplied by Shay Rojansky and others (expected to be released with beta7) – Github

EntityFramework.SqlServerCompact40 Provider for SQL Server Compact 4.0, supplied by yours truly – Github

EntityFramework.SqlServerCompact35 Provider for SQL Server Compact 3.5 SP2, also supplied by yours truly – Github

The current plan is to release EF7 at the same time as ASP.NET 5, which is currently scheduled (no guarantees) to beginning of next year.

SQL Server Compact & SQLite Toolbox 4.2 – Visual Guide of new features

After more than 370.000 downloads, version 4.2 of my SQL Server Compact & SQLite Toolbox extension for Visual Studio 2010 and later is now available for download and available via Tools/Extensions and Updates in Visual Studio. This blog post is a visual guide to the new features, improvements and bug fixes included in this release, many suggested by users of the tool via the CodePlex issue tracker

The main features in this release have been improved integration with SQL Server & SQLite, and a number of improvements to the SQL editor.

This version is a recommended update for anyone using this tool.

Improved SQL Server integration

I have previously used this old tip from Jon Galloway for prompting users for a connection to a SQL Server/SQL LocalDB/SQL Azure database, but this caused a number of issues when connecting to LocalDB and SQL Azure, so for this release, you will use Server Explorer to create SQL Server connections, and then I will refer to these from the Toolbox.
So to interact with SQL Server from the Toolbox (for example for scripting, generating DGML files or Exporting a full database for SQL Server to SQLite, first connect to the SQL Server via Server Explorer:

Then launch the Toolbox (you can do that directly from Server Explorer), and select the relevant connection from Server Explorer from the dropdown list:

Export SQLite to SQL Server (and LocalDB/Express)

It is now possible to migrate a SQLite database to SQL Server without running creating any intermediate scripts. Simply right click your SQLite database in the Toolbox , and select the “Migrate to SQL Server” option:

Select your Server Explorer SQL Server connection (as described above), and you can follow the progress of the export via the Visual Studio status bar:

Many thanks to @thughesit for help with improving this feature.

As SQLite is much more loosely type than SQL Server, a new option have been added that will truncate string (causing data loss) if a string has been defined with a length like nvarchar(255) and the length of the data stored in SQLite actually exceeds this. This option is disabled by default. Any truncations will be logged to %temp%\SQLiteTruncates.log

You can now also migrate the other way, from SQL Server to SQLite, without any intermediate scripts. Right click the root “Data Connections” node of the Toolbox and select “Export SQL Server to SQLite (beta)”

Choose the Server from the dropdown list:

Choose the tables to Export:

And specify the name for the new SQLite database file, and you can follow the progress of the Export via the Visual Studio status bar.

SQL editor improvements

The query editor has received a number of highly requested improvements:

Improved document handling: There is now both a “Open” (1) “Save” and “Save As” (2) buttons, and each editor window is aware of its underlying document. If there is a document open, it is indicated in the window caption, and a * symbol indicates if the document has unsaved changes:

Keyboard shortcuts: Keyboard shortcuts have been added for "Execute" query: F5, Save: Ctrl+S
and Open: Ctrl+O – a long standing request finally implemented
Export results as CSV: A new button has been added, that will export the first results as a csv (Excel) file, saving you having to copy and paste the results to Excel.

SQLite PRAGMAs: It is now possible to execute PRAGMA commands with SQLite

Other Improvements

Migrating a SQL Compact or SQLite database to SQL Server no longer blocks the UI.
Exporting a database from SQL Server no longer blocks the UI.
No more Error Reporting dialog, I am now using Exceptionless for error reporting instead.
Improved formatting of DataAcccess.cs sqlite-net code
Improved initial directory when adding SQLite and SQL Compact connections
Improved error information when SQLite datetime parsing errors occur (SQLite allows you to put anything in a column defined as datetime, apparently!)
Improved error information when SQL Server script execution errors occur (would mainly happen when exporting from SQLite to SQL Server)
Closing the Edit Data grid now closes the connection to the database, meaning that the Toolbox will have no open connection to the database file when all Edit Grids are closed.
Update to version 1.0.96 of SQLite ADO.NET provider

Bug fixes

Migrate to SQL Server feature was broken
Merge Replication Subscription creation was broken
Edit column should not show "Primary Key" column, and not allow editing of column name
Opening the SQL Editor sometimes crashed VS
FormatException in Explorer window

Entity Framework 6 (and SQL Server Compact) (5)–Entity Framework 6 extensions

This is a list of some of all the nice Entity Framework 6 extensions out there that expand the functionality of the Entity Framework 6 runtime. Code generator tools and Frameworks using Entity Framework are not included here, only libraries that extend DbContext or similar.

The EF team has lists of Tools and 3rd party EF providers here: http://msdn.microsoft.com/en-us/data/ee712907

Please let me know if I have missed anything, and I will add it to the list.

Store Functions for EntityFramework CodeFirst 

Why: This project uses the basic building blocks to build end to end experience allowing using TVFs in Linq queries and invoking stroed procedures without having to drop down to SQL. (By EF Team member)
Project: https://codefirstfunctions.codeplex.com/
NuGet: Coming soon

Interactive Pre-Generated Views for Entity Framework 6

Why: An alternative is a solution where views are created dynamically only when needed (i.e. views don't exist or are out dated) and then persisted and made available for use by other instances of the same application (be it running in parallel or the same application after a restart). This is exactly the problem Interactive Pre Generated Views project is trying to solve. (By EF Team member)
Project: https://efinteractiveviews.codeplex.com/
NuGet: https://www.nuget.org/packages/EFInteractiveViews

Second Level Cache for Entity Framework 6.1

Why: Entity Framework does not currently support caching of query results. A sample EF Caching provider is available for Entity Framework version 5 and earlier but due to changes to the provider model this sample provider does not work with Entity Framework 6 and newer. This project is filling the gap by enabling caching of query results for Entity Framework 6.1 applications.  (By EF Team member)
Project: https://efcache.codeplex.com/
NuGet: http://www.nuget.org/packages/EntityFramework.Cache

Entity Framework 6 Contrib

Why: https://ef6contrib.codeplex.com/documentation
Project: https://ef6contrib.codeplex.com/
NuGet: https://www.nuget.org/packages/EF6.Contrib/

Entity Framework Extended Library

Why: Batch Update and Delete, Future Queries, Query Result Cache, Audit Log

Nuget: http://www.nuget.org/packages/EntityFramework61.Extended (forked)
and http://www.nuget.org/packages/EntityFramework6.Extended (forked)
and https://www.nuget.org/packages/EntityFramework.Extended/
Project: https://github.com/loresoft/EntityFramework.Extended and https://github.com/AdvancedREI/EntityFramework.Extended


EntityFramework.Utilities

Why: EntityFramework.Utilities provides some batch operations for using EF that the EF team hasn't yet added for us. (CUD)
Project: https://github.com/MikaelEliasson/EntityFramework.Utilities
NuGet: https://www.nuget.org/packages/EFUtilities/

EntityFramework.BulkInsert

Why: Bulk insert extension for EntityFramework. Insert large amount of data over 20 times faster than regular insert. Supports DB first and code first.
Project: https://efbulkinsert.codeplex.com/
NuGet: https://www.nuget.org/packages/EntityFramework.BulkInsert-ef6

Trackable Entities

Why: Visual Studio 2012 and 2013 project templates and NuGet packages for client-side entity change-tracking and server-side persistence with Entity Framework extensions.
Project: http://trackableentities.github.io/
NuGet: https://www.nuget.org/packages/TrackableEntities.EF.6/

EntityFramework.MappingAPI

Why: EntityFramework mapping API gives you ability to know the database info behind the context.
Project: https://efmappingapi.codeplex.com/
NuGet: https://www.nuget.org/packages/EntityFramework.MappingAPI

EntityFramework.Filters

Why: Create and configures filters applied to queries across a DbContext
Project: https://github.com/jbogard/EntityFramework.Filters
NuGet: https://www.nuget.org/packages/EntityFramework.Filters/

EntityFramework.DynamicFilters

Why: allow you to create global and scoped filters for Entity Framework queries. The filters are automatically included in every query and can be used to support use cases such as Multi-Tenancy, Soft Deletes, Active/Inactive, etc.
Project: https://github.com/jcachat/EntityFramework.DynamicFilters
NuGet: https://www.nuget.org/packages/EntityFramework.DynamicFilters/

EntityFramework.Seeder

Why: Using EntiyFramework.Seeder, you can seed your database from CSV files.
Project: https://github.com/dpaquette/EntityFramework.Seeder/
NuGet: https://www.nuget.org/packages/EntityFramework.Seeder.EF6/

EntityFramework.Triggers

Why: Add triggers to your entities with insert, update, and delete events. There are three events for each: before, after, and upon failure.
Project: https://github.com/NickStrupat/EntityFramework.Triggers
NuGet: https://www.nuget.org/packages/EntityFramework.Triggers/

EntityFramework.IndexingExtensions

Why: Adds fluent API to model indexes
Project: https://github.com/mj1856/EntityFramework.IndexingExtensions
NuGet: https://www.nuget.org/packages/EntityFramework.IndexingExtensions/

Z.EntityFramework.Plus.EF6

Why: Entity Framework Bulk Operations & Utilities (EF Bulk SaveChanges, Insert, Update, Delete, Merge | LINQ Query Cache, Deferred, Filter, IncludeFilter, IncludeOptimize | Audit)
Project: https://github.com/zzzprojects/EntityFramework-Plus
NuGet: https://www.nuget.org/packages/Z.EntityFramework.Plus.EF6/

Using SQLite with the Entity Framework 6.1.x designer - a troubleshooting guide

Many users would like to be able to use the SQLite database with the Entity Framework 6 EDM Wizard and Designer, but looking at Stack Overflow, they often face problems attempting to do that. This blog post describes the steps required to do that, and gives some troubleshooting advice and tips.

Install the SQLite DDEX provider

UPDATE: This blog post describes how to generate and use an EDMX file with SQLite. I have not tested “Code First from Database”, and “Generate Database from Model” does not work with SQLite. In addtion, Code First Migrations are not supported with SQLite. If you want these additional capabilities in an embedded database, use the SQL Server Compact provider. If you encounter issues with the SQLite provider, suggest you post an issue here.

In order to use the Entity Data Model Wizard to generate an EDMX Model from an existing database, you must be able to connect to the database from Server Explorer in Visual Studio. In order to do that, a so called DDEX provider for the database and Visual Studio version in use must be installed. 

For SQLite you must download the provider from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki - this page is an unreadable mess, sadly.

For Visual Studio 2013, the download you need is named: sqlite-netFx451-setup-bundle-x86-2013-1.0.96.0.exe  for the current version of the ADO.NET provider, so 1.0.96 might change to 1.0.97 or higher in the future.

It is important that the DDEXprovider version matches the version of the current SQLite EF6 NuGet package that you use in your project!

Make sure to enable Visual Studio integration and GAC registration during installation.

You can check which version of the DDEX provider you have installed in “Add or Remove Programs”, and also check that no older versions of the DDEX provider are present:

Once installed, you can add a connection to your SQLite database from Server Explorer:

Add the correct SQLite EF6 NuGet package to your project

To demonstrate using the SQLite EF6 provider, let’s go through creating a console app in Visual Studio 2013, that uses the Chinook sample database that we connected to above.

To add the SQLite EF6 provider to your project, select the System.Data.SQLite.EF6 package:

This package will install all other required dependencies, including Entity Framework 6.1.2.

After installation of this package in a project that target .NET 4.5, your packages.config should look like this (notice that with version 1.0.95 of the SQLite provider, only a single package is required);

And the system.data section of your app.config file should be modified to look like this (notice the added <remove> and <add> statements);

<remove invariant="System.Data.SQLite" />

<add name="SQLite Data Provider"
                     invariant="System.Data.SQLite"
                     description=".NET Framework Data Provider for SQLite"
                     type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />

Important: Now BUILD your project!

You should now be able to add a EDMX based model to your project, using Add New Item. Data, ADO.NET Entity Data Model:

Add some code to the Main method to test that the application also works in runtime:

            using (var db = new ChinookEntities())
            {
                var albums = db.Albums.ToList();
                foreach (var item in albums)
                {
                    Console.WriteLine(item.Title);
                }
            }
            Console.ReadKey();

If you are unable to see the connection to the SQLite database we made earlier, here are a couple of tips:

Make sure to build the project and possibly changing the build configuration to x86 has worked for me.

Installing the latest Entity Framework 6.1.2 Visual Studio tools have worked very well for me (the version 6.1.1 Tools are included in Visual Studio 2013 Update 4) – download link in the blog post here. I think it is due to this fix in the Tools.

Hope this helps! 

“SQL Server Compact & SQLite Toolbox” related news

This blog post contains three bits of news, that all relate to my SQL Server Compact / SQLite Toolbox Visual Studio add-in

Visual Studio 2013 Community Edition

Microsoft recently released Visual Studio 2013 Community, a free, full featured edition of Visual Studio, has essentially all features found in VS Professional, expect some Office/SharePoint project templates, and is free for many scenarios (please check the licensing requirements). Previously, users of the SQL Server Compact Toolbox with VS Express had to use the standalone edition of the Toolbox. If you replace your Express edition with Community, this is no longer the case, as it supports all VSIX extensions from the VS Gallery / Extensions and Updates dialog in Visual Studio. A very bold move from Microsoft, and a major  boost for open source/hobby developers. If you have any questions about this VS edition, start by reading the Q&A here

Visual Studio Auto Updater

Mads Kristensen, a Microsoft Program Manager (and fellow Dane), recently released the “one extension to rule them all”, the Visual Studio Auto Updater extension. It allows you to specify which of your Visual Studio extensions you want to automatically update when a new version is released. I have submitted a pull request to have the SQL Server Compact / SQLite Toolbox included in the list of extensions that are always updated. Highly recommended, ensures that you Visual Studio installation is always fresh.

SQLite Toolbox on the Channel 9 “Visual Studio Toolbox” show

In early November last year I had the opportunity to appear on the popular Channel 9 show “Visual Studio Toolbox” hosted by Robert Green. In the show, I present the new SQLite support in the Toolbox. A good intro if you want to get started using the Toolbox.

Version 4.2 preview

The next release of the Toolbox, version 4.2, is currently available in preview from CodePlex. The main focus for this release is a number of (overdue) improvements to the SQL query editor:

1: The editor now has proper “file” handling, with Save and Save As buttons, and the saved file name appearing in the tab caption.

2: Keyboard shortcuts have been enabled, allowing you to use F5 to execute queries, and use Ctrl+O to open scripts, and Ctrl+S to save scripts.

3: A button to export the current result as CSV (Excel) format has been added.

Try out the preview, and let me know if you have any suggestions or find any issues via the CodePlex issue tracker.

A breaking change in Entity Framework 6.1.2 when using EDMX and SQL Server 2008/2005

Version 6.1.2 of Entity Framework has just been released, as noted on the ADO.NET blog. One of the new features in 6.1.2 is support for the OFFSET..FETCH SQL Server 2012+ paging syntax. This is used when you have LINQ queries with syntax similar to:

db.Albums.OrderBy(a => a.Name).Take(20).Skip(80).ToList();

The issue

But if you are using EDMX based Entity Framework development (and not Code First), this new feature can cause your application to break at runtime. This can happen under the following scenario:

You use SQL Server 2012 (or later, including LocalDb) for development, with Entity Framework 6.1.2 and generate the EDMX model based on a database hosted on that server. This sets the value of ProviderManifestToken="2012" in your EDMX file.

You then run the application against a SQL Server 2008 or 2005 instance (for example in test or production), and will get errors like the one reported here: http://entityframework.codeplex.com/workitem/2619:
Incorrect syntax near 'OFFSET'.
Invalid usage of the option NEXT in the FETCH statement.

I will consider this a breaking change, as before EF 6.1.2 the same paging SQL was generated against all SQL Server versions, but in 6.1.2, special T-SQL using the new OFFSET..FETCH syntax is used when running against SQL Server 2012 or later.

The fix

The fix is to modify your EDMX file, using the XML editor, and change the value of ProviderManifestToken from 2012 to 2008. I found that on line 7 in my EDMX file. After saving that change, the paging SQL will be generated using the “old”, SQL Server 2008 compatible syntax.

Whether a EF code change, which would include SQL Server version detection, is the “real” fix is debatable, as it could break expectations of a desired behaviour from the runtime.

SQLite & SQL Server Compact Toolbox 4.1 – Visual Guide of new features

After nearly 340.000 downloads, version 4.1 of my SQLite & SQL Server Compact Toolbox extension for Visual Studio  2015, 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.

Improved SQLite integration

"Add SQLite Connection" dialog improved

The “Add SQLite Connection” dialog has been enhanced to include all the available ADO.NET connection string options, by using SQLiteConnectionStringBuilder. This allows you to easily specify advanced “PRAGMA” statements to be configured, for example:

PRAGMA foreign_keys=ON;

Enumerate SQLite Server Explorer connections

If you have the SQLite support for Server Explorer installed (requires Visual Studio Community or higher, will not work with Express). See my blog post here for more info on the SQLite DDEX provider. Those connections are now also listed in the Toolbox list of connections:

Enumerate SQLite views and triggers

SQLite allows you to define triggers and views, and these are now listed under each SQLite database file, and you can create CREATE and DROP statements for these object types.

SQL Compact runtime no longer required

The Toolbox no longer requires any version of SQL Server Compact to be installed (useful if you only work with SQLite database files)

Other

Added button to "About" to re-register DDEX providers (see screenshot above)

Added option to ask for modified scripts to be saved
Added option to enable using multi-line text in "Edit Table Data"

Fixes and improvements

- horisontal scrollbar in Explorer window

- less obtrusive handling of DDEX providers installation

- no longer using MessageBox.Show

- update to latest SQLite ADO.NET provider (1.0.94)

- improved parsing/separation of SQL statements spilt with GO in Editor

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!

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.

Comparison of SQL Server Compact, SQLite, SQL Server Express and LocalDB

Now that SQL Server 2014 and SQL Server Compact 4 has been released, some developers are curious about the differences between SQL Server Compact 4.0 and SQL Server Express 2014 (including LocalDB)

I have updated the comparison table from the excellent discussion of the differences between Compact 3.5 and Express 2005 here to reflect the changes in the newer versions of each product.

Information about LocalDB comes from here and SQL Server 2014 Books Online. LocalDB is the full SQL Server Express engine, but invoked directly from the client provider. It is a replacement of the current “User Instance” feature in SQL Server Express.

Deployment/ Installation Features	SQL Server Compact 3.5 SP2	SQL Server Compact 4.0	SQLite, incl. ADO.NET Provider	SQL Server Express 2014	SQL Server 2014 LocalDB
Installation size	2.5 MB download size 12 MB expanded on disk	2.5 MB download size 18 MB expanded on disk	10 MB download, 14 MB expanded on disk	120 MB download size > 300 MB expanded on disk	32 MB download size > 160 MB on disk
ClickOnce deployment	Yes	Yes	Yes	Yes	Yes
Privately installed, embedded, with the application	Yes	Yes	Yes	No	No
Non-admin installation option	Yes	Yes	Yes	No	No
Runs under ASP.NET	No	Yes	Yes	Yes	Yes
Runs on Windows Mobile / Windows Phone platform	Yes	No	Yes	No	No
Runs on WinRT (Phone/Store Apps)	No	No	Yes	No	No
Runs on non-Microsoft platforms	No	No	Yes	No	No
Installed centrally with an MSI	Yes	Yes	Yes	Yes	Yes
Runs in-process with application	Yes	Yes	Yes	No	No (as process started by app)
64-bit support	Yes	Yes	Yes	Yes	Yes
Runs as a service	No – In process with application	No - In process with application	No - In process with application	Yes	No – as launched process
Data file features	SQL Server Compact 3.5 SP2	SQL Server Compact 4.0	SQLite, incl. ADO.NET Provider	SQL Server Express 2014	SQL Server 2014 LocalDB
File format	Single file	Single file	Single file	Multiple files	Multiple files
Data file storage on a network share	No	No	No	No	No
Support for different file extensions	Yes	Yes	Yes	No	No
Database size support	4 GB	4 GB	140 TB	10 GB	10 GB
XML storage	Yes – stored as ntext	Yes - stored as ntext	Yes, stored as text	Yes, native	Yes, native
Binary (BLOB) storage	Yes – stored as image	Yes - stored as image	Yes	Yes	Yes
FILESTREAM support	No	No	No	Yes	No
Code free, document safe, file format	Yes	Yes	Yes	No	No
Programmability	SQL Server Compact 3.5 SP2	SQL Server Compact 4.0	SQLite, incl. ADO.NET Provider	SQL Server Express 2014	SQL Server 2014 LocalDB
Transact-SQL - Common Query Features	Yes	Yes	No	Yes	Yes
Procedural T-SQL - Select Case, If, features	No	No	Limited	Yes	Yes
Remote Data Access (RDA)	Yes	No (not supported)	No	No	No
ADO.NET Sync Framework	Yes	No (not supported)	No	Yes	Yes
LINQ to SQL	Yes	No (not supported)	No	Yes	Yes
ADO.NET Entity Framework 4.1	Yes (no Code First)	Yes	Yes	Yes	Yes
ADO.NET Entity Framework 6	Yes (fully)	Yes (fully)	Yes (limited)	Yes	Yes
Subscriber for merge replication	Yes	No	No	Yes	No
Simple transactions	Yes	Yes	Yes	Yes	Yes
Distributed transactions	No	No	No	Yes	Yes
Native XML, XQuery/XPath	No	No	No	Yes	Yes
Stored procedures, views, triggers	No	No	Views and triggers	Yes	Yes
Role-based security	No	No	No	Yes	Yes
Number of concurrent connections	256 (100)	256	Unlimited	Unlimited	Unlimited (but only local)

There is also a table here that allows you to determine which Transact-SQL commands, features, and data types are supported by SQL Server Compact 3.5 (which are the same a 4.0 with very few exceptions), compared with SQL Server 2005 and 2008.

Getting and building the Entity Framework 7 alpha bits – step by step

The Entity Framework version 7 bits are slowly coming together, for a demo of some features, look at the presentation by Rowan Miller from the Entity Framework Team at the recent dotNetConf.

This blog post will show you how to get the source code and build it on your own machine, in order to better understand EF 7, and maybe give it a early run. Keep in mind that a number of features are still broken/not implemented, keep an eye on the list: https://github.com/aspnet/EntityFramework/wiki/Getting-Started-with-Nightly-Builds

There are instructions on Getting and Building the Code available on the EF7 Wiki: https://github.com/aspnet/EntityFramework/wiki/Getting-and-Building-the-Code

But I have stumbled upon a few issues, that might also hit you.

1: Get the code

You can either do this as clone of the repository as stated in the link above, you you can simply download a .zip file via the Download .zip button on this page.

If you download a .zip, DO use a tool like 7Zip to unpack the file to a folder, do NOT use the built-in Windows zip extractor, in order to avoid files being marked as “Blocked”.

Either way, you will now have a folder called “EntityFramework” on your system, with contents like this:

2: Initialize

This will download all the required NuGet packages that EF7 depends on, and reference them from the projects.

Before you do this, launch Visual Studio 2013 and verify that:

You are running VS 2013 Update 2 (check Help, About):

In Package Manager Settings, check that the official NuGet feed is configured and enabled (the AspNetVNext feed is added by the build process)

Now launch a VS 2013 Developer Command Prompt as Administrator:

Now navigate to the “EntityFramework” folder and run:

build initialize

If the build initialize process succeeds, you will see this message:

Build succeeded.

If the message does not appear, double check the VS 2013 required settings.

3: Build and run tests

The next step will build the EntityFramework projects, and run all the tests in the solution. Before today, running unit tests were not possible on non-US systems, but I and MrJingle have had a few pull requests accepted to enable this (this, this and this).

Let me know if you encounter any related issues, and I will be happy to submit a pull request to get it fixed.

Again, from a VS 2013 Administrator command prompt, run:

build

If the build process succeeds, you will see this message:

4: Work in Visual Studio

You can of course also open the solution in Visual Studio and build there.

In order to run tests in Visual Studio, I found that the built-in Test Window did not detect any tests on my PC, but TestDriven.NET worked well.

You can now add some unit tests of your own in order to give EF7 a run!

Hope you manage to build, and good luck exploring the EF7 source.

SQL Server Compact Toolbox 3.7.3 – Visual Guide of new features

After nearly 290.000 downloads, version 3.7.3 of my SQL Server Compact Toolbox extension for Visual Studio  “14”, 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.

New features

Export SQL Server database to SQLite script (beta)

This feature enables you to create a script of all tables and data in an existing SQL Server database, that can be run using the SQLite3.exe tool as described in my blog post here, allowing you to quickly migrate to SQLite, for example for use in Windows Store/Phone Apps.

“Set Password” option added to Maintenance menu

This feature enables you to set or change the password of an existing database.

Enable entry of multiline text in DataGridView

This feature allows you to enter multi line text in the data edit grid, by pressing Shift+Enter to get to a new line.

Support for VS “14” CTP

Support for VS “14” was actually already enabled, but there was a minor issue with registration of the simple DDEX providers, that has now been fixed.

UPDATE: Microsoft has blocked add-ins that claim to support VS 14 from VS 2013, so in the meantime you can download a build for VS “14” from here:

https://sqlcetoolbox.codeplex.com/releases/view/123666

 

Bug Fixes

Result Grid column headers were displayed without the first _ (underscore) character if they had one



Download Count now fetched async, so the About dialog opens faster.

Add Column - defaults to Allow Null

"Migrate" and "Export" features broken for large databases with multiple script files

Entity Framework 6 and SQL Server Compact (9) –SqlCeFunctions and DbFunctions

One of the major improvements to the SQL Server Compact Entity Framework provider in version 6 is the addition of the SqlCeFunctions class, and enhanced support for the so-called “canonical” database functions (DbFunctions/EntityFunctions).

Just to repeat, the SQL Server Compact providers are delivered in the EntityFramework.SqlServerCompact (for 4.0) and EntityFramework.SqlServerCompact.Legacy (for 3.5 SP2) NuGet packages.

The DbFunctions (previously named EntityFunctions) in the System.Data.Entity namespace define a set of (CLR) methods that expose conceptual model canonical functions in LINQ to Entities queries.

Before EF6.1, the SQL Server Compact provider only supported the functions defined for Entity Framework 1, not it supports all the functions listed here, except the following: Date/Time functions with micro and nanosecond precision (as only datetime exists as a data type in SQL Server Compact), StDev, StDevP, Var, VarP, Reverse, CurrentUtcDateTime, CurrentDateTimeOffset, GetTotalOffsetMinutes.

This means you can now have the SQL Compact engine excute LINQ to Entities expressions like String.Contains, String.EndsWith, String.Left etc.

The System.Data.Entity.SqlServerCompact.SqlCeFunctions class allows you to call database specific functions directly in LINQ to Entities queries, and the following functions have been implemented (for documentation of these, see the equivalent functions for SQL Server listed here):

String functions
CharIndex
NChar
PatIndex
Replicate
Space
StringConvert
Stuff
Unicode

Math functions
Acos
Asin
Atan
Atan2
Cos
Cot
Degrees
Exp
Log
Log10
Pi
Radians
Rand
Sign
Sin
SquareRoot
Tan

Date functions
DateAdd
DateDiff
DateName
DatePart
GetDate,

Other
DataLength

So you can compose LINQ to Entities queries like:

var result = db.Album.Where(a => SqlCeFunctions.DataLength(a.Title) > 20).ToList();

And the resulting SQL will look like this:

SELECT
    [Extent1].[AlbumId] AS [AlbumId],
    [Extent1].[Title] AS [Title],
    [Extent1].[ArtistId] AS [ArtistId]
    FROM [Album] AS [Extent1]
    WHERE (DATALENGTH([Extent1].[Title])) > 20

Entity Framework 6 & SQL Server Compact (8) –Logging SQL statements

I previously blogged about how to enable logging of INSERT/UPDATE/DELETE statements with SQL Server Compact and Entity Framework 4. Keep in mind that there is no “SQL Profiler” equivalent for SQL Server Compact, which makes this feature a vital tool for debugging, understanding and improving queries and CUD operations.

In Entity Framework version 6.1, this has been made much simpler, thanks to the implementation of the new System.Data.Entity.Infrastructure.Interception.DatabaseLogger class. In version 6.0, you could enable logging in code by using

db.Database.Log = Console.Write;

In version 6.1, it is now possible to enable logging by adding entries to your app.config file, making it possible to add logging to a deployed application.

Enabling logging is as simple as adding the following section to your app.config/web.config file in the entityFramework section.

<interceptors> 
  <interceptor type="System.Data.Entity.Infrastructure.Interception.DatabaseLogger, EntityFramework"> 
    <parameters> 
      <parameter value="C:\Temp\LogOutput.txt"/> 
      <parameter value="true" type="System.Boolean"/> 
    </parameters> 
  </interceptor> 
</interceptors>

The first parameter is the name of the file to log to (if this is not specified, logging will be sent to Console.Out). The second parameter specifies that the file should be appended to rather than being overwritten (default).

I am sure you will find this new feature useful.