SQLite and Entity Framework 6.0 Setup in Visual Studio 2017

SQLite and Entity Framework 6.0 Setup in Visual Studio 2017
A guide to getting SQLite and Entity Framework 6 working together through Visual Studio 2017

SQLite is an embedded, single file database which is ideal for desktop applications and projects that require a single client/user side database to feed applications data. It prevents the need for clients to have any server/client database frameworks running such as SQL etc.

Entity Framework 6 simplifies working with databases, converting database schema into ‘business’ objects (essentially classes which are representative of tables).

Thus, the two are ideal for desktop/simple app development. However, getting the two to play nicely together was a bit of a pain, until I found the way to do it. This started from an article that was unfortunatley out of date by the time I read it, but gave the overview on how to achieve this:

https://github.com/ErikEJ/SqlCeToolbox/wiki/EF6-workflow-with-SQLite-DDEX-provider

Follow the updated steps below

A. Install the “SQLite & SQL Server Compact Toolbox”

These are visual studio extensions that allow direct interfacing with SQLite databases. I installed both of the files hosted here:

https://github.com/ErikEJ/SqlCeToolbox/wiki/Release-notes

B. Install SQLite in GAC

Download the file similar to “sqlite-netFx46-setup-bundle-x86-2015-1.0.108.0.exe” At the time of writing, this had been updated to 1.0.109.0 – so I used this one. I think the x86 version is the one that works, although the x64 may as well, I’m not sure. The link:

https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

And what you should see:

Select “Full Installation”

Select: Install the assemblies into the global assembly cache – Install VS designer components

sqliteddex

Verify that the EF6 provider is installed in GAC from the Toolbox “About” dialog:

sqliteddex4

sqliteddex

C. Choose the right .net version for your project

Make sure it matches the version of the downloaded file above (in this case, .net 4.6)

D. Check machine.config

I think the key here is to ensure there is an entry in the <System.data><DbProviderFactories> for “SQLite Data Provider” with the correct version number matching the one you downloaded in Step B (in this case 1.0.109.0). Machine.config can be found in:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config

An extract of how my working version looked:

The pertinent bit being line 8.

E. Install System.Data.Sqlite NuGet package and Build you project

Pretty straight forward…

F. Check config files

Your package.config in your project should look like this:

Note the correct version number again.

Also, check your app.config file. I had to add the line below to mine:

under the <providers> element to produce:

G. Create a SQLite database

In your project, open the SQLite/SQL Server Compact Toolbox. Select Add SQLite Connection and then Create. Create a folder in your project folder named “Data” and then give your database a name. Click Test Connection to ensure all’s well.

Go back to your project. Click Refresh at the top of solution explorer and then select the Show all Files toggle button. This will show your new Data folder. Right click on this and select Include in Project. 

Lastly, select your .db file and change its Build Action property to “Content” and Copy to Output Directory to “Copy if Newer” This ensures that your db file is built into your application folder for deployment.

H. Put some brief data in your new database

Back in the SQLite/SQL Server Compact Toolbox add a table to your db. You can always update this later with more:

I. Build your Entity Framework Model

Right click on your Data folder and select Add>New Item. Pick a name for your entity model:

On the next screen, select EF Designer from Database. Then Click New Connection. Change the Data Source to SQLite Provider (Simple for EF6 by ErikEJ):

Then enter the full file path to your db file in the Data Source property:

You can then choose what your ‘context’ name is going to be for the entity model. Make note of this, because you will need it for any data operations. In the example below, it’s “mainEntities1”:

Click Next and then select “Tables”:

Then click Finish. And that’s it. All set up.

J. Test the setup

You can put some test data into the databse via the Server Compact Toolbox by Right clicking on a table and selecting Edit Top 200 rows:

You can then test those entries via some simple code, using the ‘context’ name you set in step I above:

 

Warnings

I learned the hard way that you have to be careful of your table/entity names. Originally, I chose “System” as one table. This had a devastating effect on the code. After hours of searching, figured it’s because EF creates classes based on the table names. Thus a name class “System” was created – I’m sure you can imagine the conflicts this caused. In short: be careful of your table names – I’m sure there may be others like this (“Windows” for example?)

Final Thoughts

I’m yet to test this at deployment stage. I’m not sure how the absolute path will work when on client machines – it may be that you have to use absolute relative to your app root. Hope this works and if not, I’ll happily try and help (although I’m no expert in this at all!)

 

Share this:
0 votes

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these <abbr title="HyperText Markup Language">HTML</abbr> tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">