In this article we’ll show how to use a LiteDB database instance to hold local data in an UWP app. LiteDB is a NoSQL database with an API that is inspired by MongoDB. It focuses on storing ‘documents’: loosely typed nested key-value or key-array pairs similar to JSON objects.

Whenever you need to manipulate, query, and persist a collection of dynamic complex objects in your app, a NoSQL database is probably the better choice. A relational database such a SQLite would impose a less convenient, fixed, strongly-typed multi-table schema on your data.

We built a sample UWP app that locally stores some NetFlix series data. The app’s data layer covers

creating the local database,
running basic CRUD-queries,
querying metadata,
running advanced queries,
handling foreign-key relationships, and
dealing with images.

Here’s how that sample app looks like:

Configuring the app

In order to use LiteDB, your app needs a dll that has its source code in a very active GitHub repo and is distributed via NuGet:

Our sample UWP app also references Microsoft.UI.XAML a.k.a. WinUI. This is to ensure that we’re using the latest set of XAML controls and styles – it’s where the rounded corners come from.

When you open the app’s bin folder after adding the LiteDB NuGet package, you’ll notice that its dll takes less than half a megabyte – not bad for a whole database engine:

Creating the database instance

When creating an instance of a LiteDatabase you need to provide a connection string with at least the path to the file where it needs to be stored. For an UWP app, a file in ApplicationData.LocalFolder is an obvious choice:

private static LiteDatabase MyDatabase
{
    get
    {
        var databaseName = "MySeries";
        var filePath = Path.Combine(
            Windows.Storage.ApplicationData.Current.LocalFolder.Path, 
            databaseName);

        return new LiteDatabase(filePath);
    }
}

Here’s how the local data folder looks like after this code has run:

Creating Document Collections

The ‘complex dynamic objects’ that the sample app will store, represent NetFlix series with actors, and with seasons that have episodes. We defined these in POCO classes. LiteDB translates these to BsonDocuments (binary JSON) using its object mapping strategy. This strategy contains data type mapping and primary key generation/determination:

Here’s how the sample app’s entities class diagram looks like:

We’ll store these entities in two collections: one for the Series and one for the Actors. Observe that in a relational database the same schema would

require at least five tables – one for each entity and one to hold the n-to-n relationship between Series and Actor, and
leave no room for new or unexpected attributes.

Let’s focus on storing documents that represent Series. Here’s the code that

(re)creates a LiteCollection to hold the Series instances using DropCollection() and GetCollection<T>(),
places an index on it with EnsureIndex(), and
populates it with sample data with some Insert() statements,
in an Awaitable method:

public static Task Reset()
{
    return Task.Run(() =>
    {
        using (var db = MyDatabase)
        {
            // Remove collection.
            db.DropCollection("series");

            // Get a collection (create it if it doesn't exist)
            var seriesCollection = db.GetCollection("series");

            // Index on Name.
            seriesCollection.EnsureIndex(x => x.Name);

            // Populate.
            foreach (var series in Series.SampleData)
            {
                seriesCollection.Insert(series);
            }

            // ...
        }
    });
}

The database now knows its schema and is ready for some action.

Basic Queries

A call to FindAll() against a document collection returns all instances in it, sorted by their primary key – in our sample app this is the numeric Id field:

public static List SelectAll()
{
    using (var db = MyDatabase)
    {
        var col = db.GetCollection("series");
        return col.FindAll().ToList();
    }
}

A call to Query() returns an ILiteQueryable<T> which exposes LINQ capabilities. Here’s a query that returns all the Series in the database that have a season in a specific year, sorted by Name:

public static List SelectFromYear(int year)
{
    using (var db = MyDatabase)
    {
        var col = db.GetCollection("series");
        return col
            .Query()
            .Where(x => x.Seasons.Select(s => s.Year).Any(y => y == year))
            .OrderBy(x => x.Name)
            .ToList();
    }
}

The call to fetch a single document from a collection is conveniently called FindOne(). It takes a Lambda expression for predicate:

var ac = seriesCollection.FindOne(s => s.Name == "Altered Carbon");

The rest of the API for CRUD queries is also straightforward. Here are some samples of Insert(), Update() and Delete() statements. After each call, we pass back the last Actor in the collection, serialized into a JSON-string. Here’s the code:

public static IEnumerable Crud()
{
    using (var db = MyDatabase)
    {
        var actors = db.GetCollection("actors");
        var ws = new Actor { Name = "Will Smiff" };
        actors.Insert(ws);
        yield return BsonMapper.Global.Serialize(actors.FindAll().Last()).ToString();
        ws.Name = "Will Smith";
        actors.Update(ws);
        yield return BsonMapper.Global.Serialize(actors.FindAll().Last()).ToString();
        actors.Delete(ws.ActorId);
        yield return BsonMapper.Global.Serialize(actors.FindAll().Last()).ToString();
    }
}

Here’s how it looks like in the sample app:

Querying metadata

Just like in a relational database, you can use the API that queries user content to also fetch system metadata. The database itself is exposed as a collection named ‘$database’. Here’s how to enumerate all its properties – things like name, size, and configuration:

public static IEnumerable SelectDatabaseProperties()
{
    using (var db = MyDatabase)
    {
        var col = db.GetCollection("$database");
        var doc = col.FindAll().ToList().First();
        foreach (var item in doc.Keys)
        {
            yield return $"{item}: {doc[item]}";
        }
    }
}

Here’s how to get the list of all user collections in the database, using the expression-based syntax that we’ll cover shortly:

public static IEnumerable SelectUserCollections()
{
    using (var db = MyDatabase)
    {
        var col = db.GetCollection("$cols");
        //var cols = col.Find(BsonExpression.Create("$.type = 'user'")).ToList();
        var cols = col.Find("$.type = 'user'").ToList();
        var name = "name";
        foreach (var item in cols)
        {
            yield return $"{item[name]}";
        }
    }
}

Here’s the sample app displaying the results:

Advanced Queries

Next to its standard LINQ API LiteDB also supports a SQL syntax. Inside the statements you can use expressions that are based on JsonPath – a lightweight (but still powerful) JSON version of XPath.

Here are some sample queries and their corresponding expressions:

The titles of all episodes in a 2020 season	$.Seasons[@.Year = 2020].Episodes[*].Title
All season finales (last episode of each season)	$.Seasons[*].Episodes[-1]
All episodes with the text ‘fight’ in its description	$.Seasons[*].Episodes[@.Description LIKE ‘%fight%’]

Here’s how to write and execute these queries from C#. You Execute() a command and then Read() through its result set, pretty much like in the good old ADO.NET days:

public static IEnumerable Select2020Seasons()
{
    using (var db = MyDatabase)
    {
        // This year's episodes.
        var reader = db.Execute(
            "SELECT $.Name, $.Seasons[@.Year = 2020].Episodes[*].Title AS Episodes 
             FROM series");

        while (reader.Read())
        {
            yield return reader.Current.ToString();
        }
    }
}

public static IEnumerable SelectSeasonFinales()
{
    using (var db = MyDatabase)
    {
        // Season finales per season.
        var reader = db.Execute("
            SELECT $.Name, $.Seasons[*].Episodes[-1] AS SeasonFinales 
            FROM series");

        while (reader.Read())
        {
            yield return reader.Current.ToString();
        }
    }
}

public static IEnumerable SelectFightEpisodes()
{
    using (var db = MyDatabase)
    {
        // Episodes about fighting.
        var reader = db.Execute("
            SELECT $.Name, $.Seasons[*].Episodes[@.Description LIKE '%fight%'] AS Fights 
            FROM series");

        while (reader.Read())
        {
            yield return reader.Current.ToString();
        }
    }
}

Here’s how the results look like in the sample app:

Handling Foreign Key Relationships

Similar to foreign keys in a relational database, DBRef allows you to create references between collections – mainly to avoid duplication. In our sample entity model, each series has a cast of actors. We didn’t want to embed the actor’s details in the series document. While an actor may appear in multiple series, it makes sense to store properties like name and birthday and Academy Award nominations only once.

In the series entity we decorated the property holding the list of actors with a BsonRef attribute:

public class Series
{
    // ...

    [BsonRef("actors")]
    public Actor[] Cast { get; set; }

    // ...
}

The Cast now refers to Actor instances instead of embedding these. The Series instance will store only the identity of each Actor. The identity is the primary key property that you define by using naming conventions or applying the BsonId attribute (more details here):

public class Actor
{
    [BsonId]
    public int ActorId { get; set; }

    // ...
}

When adding actors to the cast of a series, make sure that the Actor documents are first added to the database – they need to have their identity. Then you can insert/update/upsert the Series:

var f1 = seriesCollection.FindOne(s => s.Name == "Formula1: Drive to survive");

var drivers = new List
{
    new Actor { Name = "Alex Albon" },
    new Actor { Name = "Carlos Sainz" },
    new Actor { Name = "Charles Leclerc" }
};
actorsCollection.Upsert(drivers);
f1.Cast = drivers.ToArray();
seriesCollection.Upsert(f1);

That same order applies to data modifications inside a transaction (yes, LiteDB supports transactions):

var rm = seriesCollection.FindOne(s => s.Name == "Rick and Morty");

var cartoons = new List
{
    new Actor { Name = "Rick Sanchez" },
    new Actor { Name = "Morty Smith" },
    new Actor { Name = "Mr. Meeseeks" }
};
rm.Cast = cartoons.ToArray();
db.BeginTrans();
actorsCollection.Upsert(cartoons); // Actors added.
seriesCollection.Upsert(rm);
// actorsCollection.Upsert(cartoons); // Actors not added - even inside a transaction the order is important.
db.Commit();

To join the Series with their Actors, just do the same as in Entity Framework and Include() the related entity. Here’s the LINQ query:

var col = db.GetCollection("series");
return col
    .Query()
    .Include(s => s.Cast)
    .OrderBy(x => x.Name)
    .ToList();

Here’s how the result looks like in the sample app:

Using FileStorage

LiteDB limits the maximum size for an individual document to 1 MB. For regular data types that should suffice. When you’re dealing with images and streams, you should store these in so-called FileStorage. When you start using the FileStorage feature, LiteDB creates two collections to store the metadata (‘_files’) and the content (‘_chunks’) of the files.

Here’s how to upload a local file (a poster image for a Series) together with its metadata –also a BsonDocument- into FileStorage:

public static string SaveFile(string fileId, string filePath, string series)
{
    using (var db = MyDatabase)
    {
        var fs = db.FileStorage;
        var fileInfo = fs.Upload(fileId, filePath);
        fs.SetMetadata(fileInfo.Id, new BsonDocument { ["series"] = series });

        return $"Imported {fileInfo.Filename} ({fileInfo.Length} bytes) as {fileInfo.Id}.";
    }
}

Here’s the call from the app to store the file:

fileInfo = DataLayer.SaveFile(
        @"$/series/rickandmorty.jpg", 
        path, 
        "Rick and Morty");

Observe that we apply a folder structure to the uploaded file(s), as if they’re saved in a ‘series’ folder.

Here’s how to use the resulting file Id to fetch the contents back:

public static Stream SelectFile(string fileId)
{
    using (var db = MyDatabase)
    {
        var stream = new MemoryStream();
        var fs = db.FileStorage;
        var fileInfo = fs.Download(fileId, stream);
        return stream;
    }
}

Here’s how to fetch all files from a virtual folder:

public static IEnumerable QueryFolder(string folder)
{
    using (var db = MyDatabase)
    {
        var fs = db.FileStorage;
        var infos = fs.Find("_id LIKE @0", folder + "%");
        if (infos != null)
        {
            foreach (var info in infos)
            {
                yield return info.Id;
            }
        }
    }
}

And the call from the client:

var files = DataLayer.QueryFolder(@"$/series/");

Here’s how to fetch files by their meta data:

public static Stream FindFileByMetadata(string series)
{
    using (var db = MyDatabase)
    {
        var stream = new MemoryStream();
        var fs = db.FileStorage;
        var fileInfo = fs.Find(x => x.Metadata["series"] == "Altered Carbon").FirstOrDefault();
        fs.Download(fileInfo.Id, stream);
        return stream;
    }
}

For the sake of completeness, here’s how to remove a file from FileStorage:

public static bool DeleteFile(string fileId)
{
    using (var db = MyDatabase)
    {
        var fs = db.FileStorage;
        return fs.Delete(fileId);
    }
}

Here’s the result from these queries in the sample app:

The Verdict

Whenever you need to manipulate, query, and locally persist collections of dynamic complex objects in a .NET based app, it’s worth considering LiteDB. It’s lightweight, it‘s easy to use, and it comes with rich capabilities. It’s also free of charge.

The Source

The sample app lives here on GitHub.

Enjoy and stay healthy!

	Manaconda on A Beer Color Meter for Windows…
	sashaplatformuno on Getting started with SkiaSharp…
	xamlbrewer on Displaying OxyPlot charts in Q…
	Alax on Displaying OxyPlot charts in Q…
	xamlbrewer on Using the Windows Community To…

XAML Brewer, by Diederik Krols

If you're not having fun, you're doing it wrong.

Monthly Archives: April 2020

Using LiteDB as a local NoSQL database in UWP