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
{
public DbSet<MyEntity> MyTable {get;set;}
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
