Click here to Skip to main content
13,739,987 members
Click here to Skip to main content
Add your own
alternative version

Stats

67.5K views
30 bookmarked
Posted 16 Sep 2015
Licenced CPOL

CodeFirst with SQLite using Entity Framework 7

, 16 Sep 2015
Rate this:
Please Sign up or sign in to vote.
This tip demonstrates how to use the buildin SQLite stack of Entity Framework 7 on desktop and mobile applications.

Introduction

This tip demonstrates the CodeFirst pattern with an SQLite database.

Until Microsoft Entity Framework 7, a builtin support of SQLite was missing in all prior releases. Although SQLite supported EntityFramework with an own extension, some functionalities were always missed compared with the wonderful MS SQL Server Support. CodeFirst is only one example.

Background

The Entity Framework 7 is a very special release, because it is based on a complete redesigned code base. Though, it does not only support more databases and platforms (including Xamarin). The setup has become simple and straight forward. The idea of this tip is therefore not to provide a running solution, but to support you with a step by step instruction to use Entity Framework 7 with SQLite.

Setup

To setup an EF7 solution, just create a C# Class Library, WPF Application or Windows Forms Application.

Click on File > New > Project and select the project pattern you like to use or just use an existing solution.

Entity Framework 7 has become a modular code base. To start coding, download the latest version with the NuGet Package Manager. Installing EF7 requires NuGet 2.8.6 (or higher). Make sure you restart Visual Studio after installing the update.

Now you are ready to install EF7:

  • Right click on your solution within the solution explorer.
  • Select "Manage NuGet Packages for solution".
  • Click "Online" within the navigation and be sure nuget.org is selected.
  • Replace the "stable only" filter with "include prereleases".
  • Search for "EntityFramework.SQLite" and install the package within your project.

The last step is the SQLite library itself. In the past, there were special ADO.NET providers on system.data.sqlite.org. With EF7, you can use the default precompiled SQLite library on www.sqlite.org/download.html. Add this library to your project:

  • Right click on your project within the solution explorer and choose "Add > Existing item"
  • Choose the SQLite.dll and click "Ok".
  • Right click on the now added file within the solution explorer and select "Properties".
  • For the "Build Action" choose Content and for the "Copy to Output Directory" choose Copy if newer.

Coding

EntityFramework is an ORM Framework. That means, it maps database elements onto classes which represent the database. The Entity Framework maps the database on two major classes:

  • The Entity class represents a table structure within your database.
  • The Context class represents your database.

These classes are the minimum set to start working.

A first simple table can be defined using the following code. This is our first entity:

public class MyEntity
{
    public string MyColumn { get; set; }
}

The context is a class that inherits from DbContext. The property MyTable defines the class MyEntity to be a table of the database. The method OnConfiguring connects the context with the database:

public class MyContext: DbContext
{
    // This property defines the table
    public DbSet<MyEntity> MyTable {get;set;}

    // This method connects the context with the database
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connectionStringBuilder = new SqliteConnectionStringBuilder {DataSource = "test.db"};
        var connectionString = connectionStringBuilder.ToString();
        var connection = new SqliteConnection(connectionString);

        optionsBuilder.UseSqlite(connection);
    }
}

Now the context is connected to a database test.db which will be located within the same directory where the application binary is stored. But of course, the database does not yet exist. Of course, this is where the CodeFirst pattern got its name from. The following code snippet creates the database:

using (var db = new MyContext())
{
    db.Database.EnsureCreated();
}

In result, a SQLite file named test.db is created. That's all.

Points of Interest

We now created a very simple database with a single table containing one column. What is interesting now is how to add data, define the primary key, create indexes, ...

History

  • First revision

License

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

Share

About the Author

Nils Paluch
Germany Germany
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionHow do you create seeders and migrations? Pin
vizimuchi27-Apr-17 8:10
membervizimuchi27-Apr-17 8:10 
QuestionAdd 2 foreign key on the same table with Parent Child relationship Pin
18-May-16 1:59
member18-May-16 1:59 
QuestionValue cannot be null. Parameter name: builder Pin
TrevyBurgess6-Apr-16 10:28
memberTrevyBurgess6-Apr-16 10:28 
Questionentityframework.sqlite.design Pin
Davut Gürbüz26-Feb-16 8:57
memberDavut Gürbüz26-Feb-16 8:57 
GeneralMy vote of 5 Pin
DontSailBackwards9-Jan-16 12:19
memberDontSailBackwards9-Jan-16 12:19 
GeneralRe: My vote of 5 Pin
AniMatrix13-Feb-16 21:51
memberAniMatrix13-Feb-16 21:51 
QuestionI can't seem to get this to work! Pin
6-Jan-16 20:37
member6-Jan-16 20:37 
GeneralHelpful tip Pin
Lus Oliveira13-Dec-15 0:25
memberLus Oliveira13-Dec-15 0:25 
QuestionOne detail missing Pin
Member 104696973-Dec-15 10:30
professionalMember 104696973-Dec-15 10:30 
Bug[My vote of 1] There is no Entity Framework 7 Pin
16-Nov-15 18:18
professional16-Nov-15 18:18 
GeneralRe: [My vote of 1] There is no Entity Framework 7 Pin
JaredThirsk26-Dec-15 20:16
memberJaredThirsk26-Dec-15 20:16 
Questionsqlite.dll Assembly Pin
chris.mcginty@live.com26-Oct-15 23:45
memberchris.mcginty@live.com26-Oct-15 23:45 
AnswerRe: sqlite.dll Assembly Pin
zhosn29-Nov-15 20:18
memberzhosn29-Nov-15 20:18 
GeneralRe: sqlite.dll Assembly Pin
zhosn3-Dec-15 18:01
memberzhosn3-Dec-15 18:01 
QuestionHow to config string Encoding? Pin
bfbd16-Sep-15 19:35
memberbfbd16-Sep-15 19:35 

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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.180920.1 | Last Updated 16 Sep 2015
Article Copyright 2015 by Nils Paluch
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid