In this article we’ll show how to use Entity Framework Core and SQLite in a UWP app. We’ll use the code-first approach to define and generate a local SQLite database with one table, and and then perform all CRUD operations against that table. For the data definition as well as the data manipulation, we’ll be using only the Entity Framework API – no native SQL involved.
I created a more or less representative sample XAML and C# UWP app that
- configures a SQLite database,
- creates an entity class with properties of different data types (including a date and an image) using the code-first approach,
- supports evolution of that data model,
- demonstrates selects, inserts, updates, and deletes against a table, and
- does all of that in a MVVM architecture.
Here’s how the sample app looks like in ‘browse’ mode, the uses can scroll through a list of Person entities and select one to Edit or Delete, or hit a New button:
And here’s the app ‘edit’ mode, where the user can update an entity or create a new one and Save it– with the option to Cancel the operation:
Entity Framework
A long time ago, in a .NET galaxy not far away, the Entity Framework (EF) became the de-facto standard object-relational mapper. EF enables developers to work with relational data by using domain-specific objects. Its most recent version –EF Core- is not an evolution from its previous versions, but a complete rewrite from scratch. The new EF Core not only runs on the full fledged .NET stack – where so you can use it in Windows Forms, ASP.NET, WPF, and/or WCF. EF Core also runs on the ‘lighter’ .NET versions: .NET Core, ASP.NET Core, and the Universal Windows Platform (UWP). The list of databases that you can connect to with EF Core includes all SQL Server editions, SQLite, PostgreSQL, MySQL, IBM Data Servers (DB2 and IDC) and even in-memory data (for testing). All of these totally different platforms can be accessed using the same C# code. For more info on Entity Framework, start reading here. The EF Core specific documentation is here, its source code lives here.
SQLite
SQLite is a free public-domain transactional SQL database engine. The SQLite engine runs embedded in your apps – there is no separate server or background process. A SQLite database can host multiple tables, indexes, triggers, and views, and is contained in one single disk file with a cross-platform format. On the Unified windows Platform, SQLite is the de-facto standard for local single-user databases. If you want to know more about SQLite, start reading here.
Enough introduction, let’s dive into Visual Studio.
Prerequisites
If you want to use SQLite and EF Core in a UWP app, you need to install the following NuGet packages:
- EntityFramework.SQLite, and
- EntityFramework.Commands
Both packages are currently in prerelease, so don’t forget to select the Include Prerelease checkbox, or use the –Pre flag if you prefer to type the commands manually:
- Install-Package Microsoft.EntityFrameworkCore.SQLite –Pre
- Install-Package Microsoft.EntityFrameworkCore.Tools –Pre
Building the Model
In the code-first EF approach, you start by building the model. The framework will create the code that generates the database schema at runtime. The model consists of a set of regular C# class definitions. It is possible to use attributes on top of class and property definitions to specify the name of the tables and columns that will be generated, and to specify primary keys, maximum lengths, foreign key relations, and even inheritance relations. All of these are documented here. On top of that, EF uses some conventions. An example: if you use Id as a property name, the corresponding column will be defined as primary key.
I personally prefer to keep the entity classes as simple as possible, hence without a dependency to the EF libraries. That makes them more reusable (e.g. as Data Transfer Object or as instance variable inside a ViewModel). So the Person entity from the sample app is nothing more than a Plain Old CLR Object:
/// <summary>
/// Represents a person.
/// </summary>
internal class Person
{
/// <summary>
/// Gets or sets the identifier.
/// </summary>
public int Id { get; set; }
/// <summary>
/// Gets or sets the name.
/// </summary>
public string Name { get; set; }
/// <summary>
/// Gets or sets the description.
/// </summary>
public string Description { get; set; }
/// <summary>
/// Gets or sets the day of birth.
/// </summary>
public DateTime DayOfBirth { get; set; }
/// <summary>
/// Gets or sets the picture.
/// </summary>
/// <remarks>Is a blob in the database.</remarks>
public byte[] Picture { get; set; }
}
Creating the DbContext
When you defined the model, you need to specify which of these classes will end up in the database schema. All entities of which you create a DbSet inside a DbContext will be picked up by EF and added to the schema.
Here are some DbContext methods that you may need or want to override:
- OnConfiguring allows you to specify the provider and its options, and
- OnModelCreating allows you to refine the physical data model through a fluent API (which I personally prefer over using data annotations in the model classes). The documentation on this is spread over all articles here (look in each article for the paragraph with the title ‘Fluent API’).
The following code snippet from the sample app specifies that we will use the SQLite engine, and makes the Id and Name columns NOT NULL allowed:
/// <summary>
/// The Entity Framework Database Context.
/// </summary>
public class PersonContext: DbContext
{
internal DbSet<Person> People { get; set; }
protected override void OnConfiguring
(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite("Filename=People.db");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// Make Id required.
modelBuilder.Entity<Person>()
.Property(p => p.Id)
.IsRequired();
// Make Name required.
modelBuilder.Entity<Person>()
.Property(p => p.Name)
.IsRequired();
}
}
[Making the Id property required is an obsolete action, since it will be defined through convention as a required auto-incrementing primary key.]
Code Generation and Database Migrations
At this stage, the physical database is still not created yet. We still need to generate the code for that. The database file is not part of the package that you deploy, but it is created by running the app. EF provides two approaches for this: a call to EnsureCreated (sorry: I found no URL to a description of this method) or using Migrations. It’s fairly safe to assume that in most apps the data model will evolve during the lifecycle. The preferred technique in this scenario is to rely on the Code First Migrations feature. Here’s how to enable and use this feature.
Make sure that you added the “EntityFramework.Commands” NuGet package to your solution, and click “Tools –> NuGet Package Manager –> Package Manager Console” to open the console. Run “Add-Migration MyFirstMigration” to generate the classes that will be used to create the initial set of tables for your model:
If you want to know more about it, here’s a tutorial on EF Core migration commands.
The Add-migration command does not create the database. It creates a snapshot of you current model:
[DbContext(typeof(PersonContext))]
partial class PersonContextModelSnapshot : ModelSnapshot
{
protected override void BuildModel(ModelBuilder modelBuilder)
{
modelBuilder
.HasAnnotation("ProductVersion", "7.0.0-rc1-16348");
modelBuilder.Entity(
"XamlBrewer.Uwp.SqLiteEntityFrameworkSample.Models.Person",
b =>
{
b.Property<int>("Id")
.ValueGeneratedOnAdd();
b.Property<DateTime>("DayOfBirth");
b.Property<string>("Description");
b.Property<string>("Name")
.IsRequired();
b.Property<byte[]>("Picture");
b.HasKey("Id");
});
}
}
It also generates (but does not execute!) the code that upgrades the model to a newer version, or downgrades it to a previous version:
public partial class MyFirstMigration : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Person",
columns: table => new
{
Id = table.Column<int>(nullable: false)
.Annotation("Sqlite:Autoincrement", true),
DayOfBirth = table.Column<DateTime>(nullable: false),
Description = table.Column<string>(nullable: true),
Name = table.Column<string>(nullable: false),
Picture = table.Column<byte[]>(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Person", x => x.Id);
});
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable("Person");
}
}
You want to make sure that your end users generate their first version of their local database when they run the app for the first time. You also want to make sure that they migrate to the new data model after they installed an upgrade of your app. I’m pretty sure that you don’t want your end users to run install and upgrade scripts, so your app needs to automagically take care of the migration.
All you need to do to install or upgrade the database, is call DataBase.Migrate() e.g. when the app starts up. I put that code in the static constructor of the DbContext class, so it will be called the first time that the user tries to touch the database:
static PersonContext()
{
using (var database = new PersonContext())
{
database.Database.Migrate();
}
}
The database and the tables now exist, let’s now play with the content.
CRUD Operations
Every entity in the data model is represented in the DbContext by a DBSet<TEntity> class. You can use LINQ or SQL to get data from these collections. Here are some typical SELECT operations, from the sample app:
internal static List<Person> GetAllPersons()
{
using (var db = new PersonContext())
{
return db.People.ToList();
}
}
internal static Person GetPersonById(int id)
{
using (var db = new PersonContext())
{
return (from p in db.People
where p.Id.Equals(id)
select p).FirstOrDefault();
}
}
For INSERT and UPDATE operations, it suffices to bring the entities in the context and call the SaveChanges() method:
internal static void SavePerson(Person model)
{
using (var db = new PersonContext())
{
if (model.Id > 0)
{
db.Attach(model);
db.Update(model);
}
else
{
db.Add(model);
}
db.SaveChanges();
}
}
There’s more info on EF Core entity change tracking and persistence right here.
When you want to DELETE an entity, just call Remove():
internal static void DeletePerson(Person model)
{
using (var db = new PersonContext())
{
db.Remove(model);
db.SaveChanges();
}
}
Code and Credits
The sample app lives here on GitHub. It contains some useful code that was not discussed in this article, like how to transform the profile picture to and from a byte array, and how to two-way bind the birthday to a DatePicker control.
The awesome Game of Thrones caricatures are made by Paul Moyse.
Enjoy!
XAML Brewer, by Diederik Krols 