Monday, December 29, 2014

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.

image

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.

image

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.

Tuesday, December 2, 2014

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;

image

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:

image

Enumerate SQLite views and triggers

image

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)

image

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"

image

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