Setting up SQLite and Entity Framework 6 (Code First and Database First)

Introduction

I had been finding it rather hard to get SQLite working with Entity Framework 6 using a "Code First" method, so I decided to look into other approaches to get the same job done. This "Guide" isn't going to tell you how to do queries, etc. or manage updates because that is all rather straight forward. The goal here is to get your SQLite database working with Visual Studio 2015 using the Code First and Database First Models.

I'm not 100% sure about migrations, etc. with SQLite with this implementation. But I don't see why it wouldn't work.

Getting Started

Firstly, you're going to need to download and install some prerequisites.

Our provider is going to be using .NET 4.5, but the designer we need works with .NET4.6.1.

I know what you're thinking. This is about to become a bloody nightmare. But it's not. Chill.

Download and install this first selecting all the options: SQLite .NET4.6 Stuff

Download and install this last leaving the settings as they are and spam next: SQLite .NET4.5 Stuff

Making the Database

If you already have a SQLite File with Tables and Constraints, you can skip this section.

SQLite files can be connected in many different ways, but by far the easiest way to create/view them is DB Browser for SQLite. It will allow you to create/view/drop/query, etc. your SQLite file and set up its basic constraints.

You can download it here: DB Browser for SQLite.

Once you're happy with that, move your database into your project directory, be sure that if your database doesn't have .SQLite on the end, to add it before moving on to the next step.

Setting Up References

Go to Tools > NuGet Package Manager > Manage Packages for Solution

You want to install the following packages by searching (Ctrl + E):

• Entity Framework, Version (6.1.3) NuGet Link
• System.Data.SQLite, Version (1.0.98.1) NuGet Link
• System.Data.SQLite.Core, Version (1.0.98.1) NuGet Link
• System.Data.SQLite.EF6 (1.0.98.1) NuGet Link
• System.Data.SQLite.Linq (1.0.98.1) (will be downloaded with SQLite)

Let's Get Started!

Okay, now we have that done, we can start using our database we created earlier on (or had already).

Right click on your solution, and add a new Item.

We want to create a new ADO.NET Entity Data Model, name it whatever you want.

Click Add.

Now comes the fun part. You have an interesting decision to make, we are interested in both of the "From Database" options. These will automatically set up our connection string and other details for us and create our models in different ways.

Entity Framework from Database

Quote:

Creates a model in the EF Designer based on an existing database. You can choose the database connection, settings for the model, and database objects to include in the model. The classes your application will interact with are generated from the model.

Code First from Database

Quote:

Creates a Code First model based on an existing database. You can choose the database connection, settings for the model, and database objects to include in the model.

Which one you choose is purely up to you and for the purposes of the tutorial it doesn't really matter. But some people will find it VERY interesting. You can create a "Code First" model in this manner. This is the only method I have found to successfully use "Code First" with SQLite.

Connection Properties

The next step no matter which option you choose, is to set up the connection, and it's the same for both methods.

The first thing we are going to do is Change... the Data Source.

If you downloaded and installed the pre-reqs at the start of this guide, you will see the following:

Select System.Data.SQLite Database File and click OK.

Use Browse to find your Database, Test Connection and then click OK.

On the next page, make sure the tickbox to include to connection in your App.config is selected (Default).

The last step is to choose which tables you would like to include with your Model.

Keep in mind that "Model Namespace" will end up being your Context.

If everything has gone as per your plan, you should be seeing something like this (if you chose the Database First Model).

You Are Ready, Go Forth and Make Great Things That Do Stuff!

If you would like more details with more examples, but suited for a different version of SQL, then I seriously recommend you take the time to check this out:

• Simple Demo Entity Framework - The Beast Unleashed