Introduction
In the last post we looked at the basic features of Lex.DB and where it may be useful for Windows 8 Store application development. This time out I will dig a little deeper into the API and look at how we can use the Indexing features which allow us to query the database in a more useful manner as well as how we delete records from the database.
Indexes are fields which we determine will be used for searching data. Within a table we are rarely likely to search on all of the fields when looking for data, but it's just a likely we won't just want to search by the primary key. Lex.DB is lightweight and fast as it only holds it's indexes in memory when running. This allows us to request specific records based on these indexes, without the overhead of all of the other fields being held in-memory for no reason. This does require a small amount of configuration to tell Lex.DB which fields should be indexed, but after that the usage is pretty simple.
NOTE: Lex.DB is being actively developed at the time of writing and features are changing often. This post was written at the release of version 1.1.2.0. Newer versions may implement features beyond the scope of this post which I may write out in future blog posts.
Indexing
We're going to extend our earlier simple demo to demonstrate how we can create indexes and then query against them. The first task is to modify our Initialisation method to declare which properties we wish to use as indexes. For our Person sample we're going to index the Surname field to allow us to query records for those matching specific surnames.
private async Task InitLexDB()
{
_db.Map<Person>().Automap(i => i.PersonID, true)
.WithIndex("Surname", i => i.Surname);
await _db.InitializeAsync();
}
Here we use the WithIndex method which takes in two arguments. The first is the name for the index as a string. The second is an expression to control which property is being added to this index.Our next task will be to update the AddPeople method we previously created. We'll first purge the database and then populate it with a set of records we can work against.
private async Task AddPeople()
{
await _db.PurgeAsync();
Person newPerson1 = new Person { Forename = "Joe", Surname = "Bloggs" };
Person newPerson2 = new Person { Forename = "James", Surname = "Smith" };
Person newPerson3 = new Person { Forename = "David", Surname = "Peterson" };
Person newPerson4 = new Person { Forename = "Steve", Surname = "Gordon" };
Person newPerson5 = new Person { Forename = "David", Surname = "Gordon" };
Person newPerson6 = new Person { Forename = "Colin", Surname = "Gordon" };
Person newPerson7 = new Person { Forename = "Michael", Surname = "Gordon" };
List<Person> newPeople = new List<Person>{
newPerson1,
newPerson2,
newPerson3,
newPerson4,
newPerson5,
newPerson6,
newPerson7 };
await _db.Table<Person>().SaveAsync(newPeople);
}
The PurgeAsync method on the database will delete all records in all tables so we can start with a clean slate. We'll look at that again later in this blog entry. With these changes we now have an index as well as some data we can query against.
We'll firstly create a simple method which performs a likely real-world requirement. It will allow us to supply a surname as an argument and will then return to us a list of people who have that surname.
Querying By An Index
We'll firstly create a simple method which performs a likely real-world requirement. It will allow us to supply a surname as an argument and will then return to us a list of people who have that surname.
private async Task<List<Person>> LoadBySurname(string surname)
{
List<Person> people = await _db.Table<Person>()
.IndexQueryByKey("Surname", surname).ToListAsync();
return people;
}
The work is done using the IndexQueryByKey method which takes in two arguments. The first is the name of the index we want to use, in this case "Surname". The second is the value you wish to search for. Here we pass along the surname string which has been passed into the LoadBySurname method. We use ToListAsync to return a task of type List<Person> asynchronously.Using Take and Skip
Lex.DB has a few other extension methods we can take advantage of to limit the results being returned. A common requirement is to return a limited amount of records. Let's pretend our table holds 1000 people. It's unlikely we want to deal with all of those together. Instead we may want to look at the first 20. We can do that using the Take(x) method.
await _db.Table<Person>().Query<int>().Take(20).ToListAsync()The take method expects an interger to instruct it on how many records to return. This example would return the first 20 records from the table.
This alone may have it's uses but a more realistic requirement will be to gracefully manage paging data. The scenario here would be to allow the user to step through data in pages. Here we could return whole list of people and then juggle it in memory to manage the paging, but that's pretty inefficient on large tables. It's better to call to the database for a limited number of records required to show the current page of data. This will mean more database calls which has it's own overheads, but done correctly is likely to be suitable to quite a few paging scenarios.
This is pretty easy to achieve by using the Skip(x) extension.
await _db.Table<Person>().Query<int>().Take(10).Skip(10).ToListAsync()This will interrogate the table and return to us a list of 10 records, skipping the first 10. We could quite easily build a simple paging system around these two commands.
Querying Between Ranges
One final index querying feature I want to cover in this post is how to supply a greater than / less than range for the returned records. There are a couple of methods on the IndexQuery that we can use limit the range of the returned records. Let's say that we want to return only the people whose Surname begins with the letter G. We can write the following query.
private async Task<List<Person>> LoadBySurnameRange()
{
var table = _db.Table<Person>();
var index = table.IndexQuery<string>("Surname");
var query = await index.GreaterThan("G", true).LessThan("H").ToListAsync();
return query;
}
First we create a variable to hold the table object and then an index variable to hold the base IndexQuery. The important line is the next one where we specify the GreatThan and LessThan methods. These take two arguments. The first is the data we which to compare with, the second is an optional boolean to represent if the compare should be equal to the comparison data.In this case we want all records matching the letter G or higher, so we use G for the first argument and specify true for the equal to argument. We then use the LessThan method to state we want to restrict to any Surname which is less than "H". We don't use the equal to argument here as we wouldn't want to capture anyone with a Surname of "H". Finally we use the ToListAsync method to return a list of the results.
The methods can be used together as we have here, or independently.
NOTE: Until version 1.1.2.0 of Lex.DB there were bugs with these methods and the results were often not as expected. Thanks to the project owner, Lex Lavnikov for taking in the feedback and fixing this very quickly. Please ensure you update to the new version using NuGet.
Deleting Records
Deleting records in Lex.DB is pretty straightforward and there are a few methods we can use to do this. If we know the primary key for the record we can use the DeleteByKeyAsync method on the DBTable class which requires just the primary key to be passed in.
The next line uses our DeletePerson method which we know takes a list of people as it's argument. Here we pass in the result of the preceeding query which is our person with a surname of Bloggs. Finally I run the count again and can see that my table now only has 6 records, confirming the expected single record has been deleted.
Earlier we looked at a command use on the AddPeople method which purged the whole database. To end this section I'll show a corresponding command on the DBTable which we can use if we need to purge only a single table in our database.
await _db.Table<Person>().DeleteByKeyAsync(2);We can also use the DeleteAsync method which takes in the object (or a list of objects) to be deleted. In our test application we can add a simple method to demonstrate this.
private async Task DeletePerson(List<Person> people)
{
await _db.Table<Person>().DeleteAsync<Person>(people);
}
To use this method we will add the following code to our OnNavigatedTo void for the page.
int recordcountatstart = _db.Table<Person>().CountAsync().Result;
List<Person> peopletodelete = await LoadBySurname("Bloggs");
await DeletePerson(peopletodelete);
int recordcountatend = _db.Table<Person>().CountAsync().Result;
The first line uses a method called CountAsync to count the records on the Person table. This is so that we can validate the number of records which have been deleted is correct. The second line uses a LoadBySurname method we added earlier which allows us to query our Surname index for a particular surname. In this case, because I know the test data we have populated into the database I know we should have one matching record.The next line uses our DeletePerson method which we know takes a list of people as it's argument. Here we pass in the result of the preceeding query which is our person with a surname of Bloggs. Finally I run the count again and can see that my table now only has 6 records, confirming the expected single record has been deleted.
Earlier we looked at a command use on the AddPeople method which purged the whole database. To end this section I'll show a corresponding command on the DBTable which we can use if we need to purge only a single table in our database.
await _db.Table<Person>().PurgeAsync();
Summary
In this post we have reviewed two more important concepts for working with Lex.DB, indexing and deletion. Both are fairly simple using the available methods. My early impressions so far are that Lex.DB might offer a good solution for working with data in my Windows 8 applications. The API is still new and a little rough in places.
My next step is to pull down the source and dig into it a little further as it looks like a fun project to try contribute to. It takes me out of my comfort zone and will hopefully expand my c# knowledge. I'll continue this series with some of my findings. I also plan to start a new project to build a more real-world example application for Lex.DB which will use better design practices such as the repository pattern.
I'm curious to see how Les.DB works in a system where I need to store related objects in one-to-many and many-to-many situations. Currently Lex.DB does not support this but I hope to find a viable pattern which I can wrap around it to achieve the end goal.
My next step is to pull down the source and dig into it a little further as it looks like a fun project to try contribute to. It takes me out of my comfort zone and will hopefully expand my c# knowledge. I'll continue this series with some of my findings. I also plan to start a new project to build a more real-world example application for Lex.DB which will use better design practices such as the repository pattern.
I'm curious to see how Les.DB works in a system where I need to store related objects in one-to-many and many-to-many situations. Currently Lex.DB does not support this but I hope to find a viable pattern which I can wrap around it to achieve the end goal.
