CodeFirst with SQLite using Entity Framework 7

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.

• Visual Studio 2015 - No updates needed, a compatible version of NuGet is included
• Visual Studio 2013 - Install the latest NuGet for VS2013
• Visual Studio 2012 - Install the latest NuGet for VS2012

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