Saturday, March 31, 2018

Using Entity Framework 6 and SQLite with Visual Studio 2017

The System.Data.SQLite DDEX provider does not support Visual Studio 2017. This post describes how to work with SQLite and Entity Framework 6 in Visual Studio 2017, using the "SQLite Toolbox" DDEX provider for EF6. Notice that this provider only supports the EF 6 Tools, and not other Data Source scenarios, for example Typed DataSets.
  • Install Toolbox
  • Install SQLite in GAC
  • Install SQLite EF provider in project
  • Run EDM Wizard

Install latest Toolbox

Once per Visual Studio edition (daily build at https://github.com/ErikEJ/SqlCeToolbox/wiki/Release-notes )

Install SQLite in GAC

Once per machine. Download sqlite-netFx46-setup-bundle-x86-2015-1.0.108.0.exe (from https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki)
Select "Full Installation"
Select: Install the assemblies into the global assembly cache - Install VS designer components
sqliteddex
Restart Visual Studio
Verify that the EF6 provider is installed in GAC from the Toolbox "About" dialog:
sqliteddex4
sqliteddex
If the EF6 provider is not in GAC, this may be due to an invalid entry in machine.config, located in the C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\Config folder. The only SQLite related entry should look like this, with this exact version number:
<system.data>
    <DbProviderFactories> 
        <add name="SQLite Data Provider" 
             invariant="System.Data.SQLite.EF6" 
             description=".NET Framework Data Provider for SQLite" 
             type="System.Data.SQLite.EF6.SQLiteProviderFactory, 
                    System.Data.SQLite.EF6, 
                    Version=1.0.108.0, 
                    Culture=neutral, 
                    PublicKeyToken=db937bc2d44ff139" 
        />  
    </DbProviderFactories> 
</system.data>

Install System.Data.Sqlite NuGet package

Install using Package Manager Console or NuGet Manager in each project.
PM> Install-Package System.Data.SQLite
Make sure to install the same version as the tools package above.
Build project!
Packages.config should look like this after install:

<packages>
  <package id="EntityFramework" version="6.2.0" targetFramework="net461" />
  <package id="System.Data.SQLite" version="1.0.108.0" targetFramework="net461" />
  <package id="System.Data.SQLite.Core" version="1.0.108.0" targetFramework="net461" />
  <package id="System.Data.SQLite.EF6" version="1.0.108.0" targetFramework="net461" />
  <package id="System.Data.SQLite.Linq" version="1.0.108.0" targetFramework="net461" />
</packages>
App.config should look like this:
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <entityFramework>
    <providers>
      <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
    <remove invariant="System.Data.SQLite" />
   </DbProviderFactories>
  </system.data>
</configuration>

Run Entity Data Model Wizard

Add, New Item, Data, ADO.NET Entity Data Model. Choose "EF Designer from Database" or "Code First from Database"
Use "SQLite Provider (Simple for EF6 by ErikEJ)" when creating a connection to your SQLite database file.
sqliteddex3

Wednesday, January 4, 2017

Downloading SQL Server Compact 3.5 SP2 for Devices

A number of developers are still, in 2017, developing new solutions for Windows CE / Windows Mobile using Visual Studio 2008 and SQL Server Compact 3.5 SP2. Unfortunately, Microsoft has removed the official download of the SQL CE 3.5 SP2 package for devices, which contains the cab files for installing the SQL CE runtime on various processor flavors of Windows CE / Windows Mobile and also contains a cab file for installing a System.Data.SqlClient (ADO.NET provider for SQL Server), that allows you to access SQL Server directly from a Windows Mobile device.

In this blog post I will describe the steps required to get the Device MSI installed on your development PC. Luckily, it is still currently possible to request the latest updates/hotfixes of the 3.5 SP2 runtime – I have a comprehensive list here.

We can take advantage of the fact, that each “patch” is essentailly a full release of all applicable packages, and that build 8109 is a full re-release of all possible packages.

1: Navigate to the KB page for hotfix 8109: https://support.microsoft.com/en-us/kb/2860310

2: Click the “Hotfix Download Available” link (blue button)

3: Accept the license agreemnt if prompted

4: Expand the list of hotfixes to show all 52 packages by clicking the link “Show hotfixes for all platforms and languages” (this text may be localized)

5: Select (put a checkmark next to) the file named: SSCEDeviceRuntime_ENU

6: Enter your email twice, and press the “Request hotfix” button (button text may be localized)

7: You will now receive an email with a link to the hotfix, download the linked file, named: 465553_ENU_ce_zip.exe

8: Double click the file, and extract it’s compressed contents:
SSCEDeviceRuntime-ENU.msi

9: Double click  SSCEDeviceRuntime-ENU.msi  to install the Devices SDK:

image

10: Complete the setup, the cab files are now present in the C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\Devices folder

Wednesday, December 14, 2016

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

Sunday, August 28, 2016

In the meantime

Since may last post in February, as few things have happended in the world of Microsoft Data Acces Development:

Entity Framework Core 1.0 has been released at the end of May, including the providers for SQL Server Compact 4.0 and 3.5 (supplied by yours truly). I plan to blog in more detail about using EF Core with this new provider.

A useful SQLite  NuGet package has been released, for use with the .NETStandard 1.0 portable platform.

SQL Server 2016 was released, many highlights and improvements to the relational engiene, as highlighted in the “It just runs faster” blog series.

And SQL Server 2016 Developer Edition is now free (was 50 USD earlier)!

Friday, February 12, 2016

SQL Server Compact 4.0 SP1 hotfix available

I was recently made aware that a hotfix for SQL Server Compact was made available in 2014 –  but better late than never.

The hotfix can be requested via the Microsoft support knowledgebase here: https://support.microsoft.com/en-us/kb/2960153

The file/build version of the hotfix is: 4.0.8902.0 (The .NET assembly version stays at either 4.0.0.0 (or 4.0.0.1 for the “Private” .NET Assembly)

The hotfix includes the following fixes:

2984052
FIX: Unexpected ordering when you run ORDER BY statement on a column that contains "-" in SQL Server Compact 4.0

2984053
FIX: "ConnectionString cannot be null" when you use folder redirection with offline files

2979868
FIX: It takes longer time than flush interval to flush committed transactions in SQL Server Compact 4.0

Sunday, January 24, 2016

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

After more than 470.000 downloads, version 4.5 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 GitHub issue tracker

I have moved the source code and issue tracker to GitHub, and I am planning to move the remaining CodePlex content “soon”. I have also moved the source of the scripting API that the Toolbox uses extensively to the same GitHub repository. As always, and suggestion, bug reports and pull requests are welcome!

New icons

The icons of the extension have all been overhauled, and updated to use the latest VS 2015 icons/images. From users already trying out the latest bits, I have received positive feedback on this visual change.

image

Query editor toolbar redesign and more

I have also started using Azure AppInsights to track (anonymously) feature usage etc. This revealed (maybe not surprisingly) that the most use feature is the query editor. So for this release, the query editor has received a lot of attention. In addtion to the changes below, the general layout of the editor has been streamlined (no more rounded corners).

image

The first button is new, and will open a “New Qery” window against the same database file. An options dropdown have been added to expose some of the (maybe overlooked) options that are available for the query editor. Setting these only affects the current query window, use the general settings (which you can reach via the Setting button in the query editor now) to change the “default” value for a setting.

Lets have a quick look at each of the options:

“Show result in Grid”: By default, the query results (from a SELECT statement) will be displayed as text for performance reasons, but you can opt-in to have the results displayed in an Excel like grid instead.

“Show Binary Values in Result”: By default, binary values (from for example “image” or “rowversion” columns) will be displayed a “System.Byte[]”. Enabling this will instead display the actual value as a hexadecimal string – but may affect performance.

“Show null values as NULL”: Until recently, NULL values were displayed as an empty string, not as “NULL”. Disabling this reverts to the old behaviour.

“Ignore DDL errors”: Enabling this (per query window only) feature, allows you to run the database creation scripts generated by the Entity Framework “Generate Database from Model” feature, and ignore errors caused by for example pre-existing tables.

“Use classic (plain) grid”: This will revert to using the “old” grid for grid results – about the “new” grid, see below!

New result grid in query editor

A new grid that allows you to further drill into the results of a query without writing any SQL is now the “default” result grid in the query editor. The grid uses http://wpfextendeddatagrid.codeplex.com/

image

“Autofilter” :The grid has built in filters, in the example above to filter on invoices for a particular customer (or even set of customers).

image

“Grouping”: Add columns you want to group by to the surface above the grid. In the example above, Invoices are grouped by Customer Id.

Options now part of Visual Studio options dialog

image

The options for the Toolbox have finally been integrated in the standard Visual Studio Tools/Options pages.

Improved SQL Server support

SQL Server Connections defined in Server Explorer now appear in the Toolbox, in order to better expose the SQL Server releated features of the Toolbox:

image

And scripting of a SQLite schema (only) based on a SQL Server database is now possible:

image

Improvements

A number of smaller improvements are also included in this release:

Improve format of INSERT statements generated by CSV import (to make the more readable)
Added latest System.Data.SQLite version (1.0.99)
Added link to blog post to DataAccess.cs to better explain how to use it.
Support for row-returning SQLite statements beginning with "WITH"
Added "Feedback" button
Using Azure Application Insights for metrics and crash reports
Ability to remove password from password protected SQL Compact file (by leaving password box blank)
Progress messages during export now displayed in Output window
Enable scripting of hierarchyid as varbinary(892)
Now able to script Adventureworks2014 schema and data to both SQL Compact and SQLite without errors
Table Data script (INSERTs) now have "RESEED" command at end always
Option to Opt out of Telemetry added

Bug fixes

In addition to the above improvements and features, and number of bug fixes are included in this release:

API:
Fix bug in SQLMetal.exe detection (on some configurations)
SQLite CREATE TABLE datetime default should be current_timestamp, not GETDATE()
Improved SQLite datetime handling in SELECT and Edit in Grid

User Interface:
CSV export from the SQL editor included one separator too many in the heading
Editor keyboard bugs fixed
Scroll issues in treeview fixed
Autohide of Toolbox bug fixed

Table builder:
Table builder was not scripting default values
Table builder should not script default value for IDENTITY columns

Thursday, August 20, 2015

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

After more than 415.000 downloads, version 4.3 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

This new version was released in July at the time that Visual Studio 2015 went live, and is of tested with Visual Studio 2015 – keep in mind that all extensions now work with the free Visual Studio Community Edition. This release has a couple of new features that improves on the SQLite support originally added in version 4.0

Scan solution for SQLite database files

image

The “Add Connections from Solution” feature has been enhanced to scan all projects in the current solution for any SQL Server Compact and SQLite files. If any are found (based on the file patterns defined in Options), the Toolbox will attempt to add them as new connections.

The current file patterns are defined as shown below, but you can change them to anything you like:

image

Rename Connection name

The names of connections currently defaults to simply the file name of the database file. This becomes an issue, if you have many same named files in different folders connected. Therefore, you can now rename the Connection name:

image 

SQLite-net DataAccess.cs improvements

The SQLIite-net code generation feature has received a couple of small updates based on user feedback:

image

The generated classes will also contain any views defined in the SQLite database, and nullable values will be mapped accordingly (for example int?).

NULL value display

image

NULL values are now displayed as 'NULL' in Query results text and grid. Remember that you can see query results both as text and in a grid, the default is text for performance reasons, but this can easily be changed in Options:

image

Also notice that if you prefer the previous behaviour, this can be changed here, via the ShowNullValuesAsNULL option.

Other improvements

Unique indexes now always scripted as UNIQUE constraints (a long standing omission, which hit me when I was creating the reverse engineering for the SQL Server Compact providers for Entity Framework 7)

As usual, any feedback and comments are welcome, and if you like my free tools, I am always very grateful for a review on Visual Studio Gallery