Click here to Skip to main content
14,574,881 members

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

Rate this:
4.86 (11 votes)
Please Sign up or sign in to vote.
4.86 (11 votes)
16 Nov 2015CPOL
How to get SQLite 1.0.98.1 working with Entity Framework 6.1.3 without a massive headache using the Database First Model and Code First Model.

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

Image 1

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.

Image 2

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.

Image 3

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:

Image 4

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

Image 5

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.

Image 6

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).

Image 7

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:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author


Comments and Discussions

 
QuestionIts Now Showing Sqlite Database file option in data source dialogue Pin
Member 1379401417-Sep-18 3:52
MemberMember 1379401417-Sep-18 3:52 
AnswerRe: Its Now Showing Sqlite Database file option in data source dialogue Pin
Nadir Abrar3-Jun-20 6:44
MemberNadir Abrar3-Jun-20 6:44 
QuestionCode First Pin
DontSailBackwards23-Jul-17 1:38
MemberDontSailBackwards23-Jul-17 1:38 
Praisethanks Pin
Ivan Ferrer6-Jun-17 4:52
MemberIvan Ferrer6-Jun-17 4:52 
PraiseVery well explained Pin
shaijujanardhanan16-Jun-16 19:18
Membershaijujanardhanan16-Jun-16 19:18 
QuestionSystem.Data.SQLite Database File is not found on data source list Pin
Member 1163917321-Nov-15 9:22
MemberMember 1163917321-Nov-15 9:22 
AnswerRe: System.Data.SQLite Database File is not found on data source list Pin
User 1066841024-Nov-15 16:00
MemberUser 1066841024-Nov-15 16:00 
GeneralRe: System.Data.SQLite Database File is not found on data source list Pin
hhenn9-May-16 2:12
Memberhhenn9-May-16 2:12 
GeneralRe: System.Data.SQLite Database File is not found on data source list Pin
Batmaci29-Nov-16 10:14
MemberBatmaci29-Nov-16 10:14 
QuestionVote of 5 Pin
Rahul Pawar LogicMonk16-Nov-15 20:30
professionalRahul Pawar LogicMonk16-Nov-15 20:30 
Praise4! Pin
Krunal Rohit16-Nov-15 18:02
professionalKrunal Rohit16-Nov-15 18:02 
GeneralRe: 4! Pin
User 1066841016-Nov-15 18:04
MemberUser 1066841016-Nov-15 18:04 
GeneralMy vote of 4 Pin
Santhakumar M16-Nov-15 17:02
professionalSanthakumar M16-Nov-15 17:02 
GeneralRe: My vote of 4 Pin
User 1066841016-Nov-15 18:05
MemberUser 1066841016-Nov-15 18:05 
GeneralRe: My vote of 4 Pin
Santhakumar M16-Nov-15 20:15
professionalSanthakumar M16-Nov-15 20:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 16 Nov 2015

Tagged as

Stats

102.8K views
40 bookmarked