LiteDB multiple queries in C#

Question

I am new to LiteDB and would like to execute two or more queries in one go.

I want to be able to search for a certain customer in my DataBase and since there will be multiple customers with a similar name I want return all of them and sort them in Descending (Id or Date) Order. I have both functions improvised but separated, so I'm looking for a way to combine Search and Sort into one Query.

Here is the main search function:

private void search_btn_Click(object sender, RoutedEventArgs e)
    {
        using (var dataBase = new LiteDatabase(dbConnectionString))
        {
            // Stranka = Customer
            var collection = dataBase.GetCollection<Stranka>("stranka");
            // Search for a customer name whom I enter in the searchName_tb
            var query = collection.Find(Query.Where("Ime_Priimek", 
            x => x.AsString.Contains(searchName_tb.Text)));

            // Currently I'm doing everything with ListView, so I clear it
            // to make room for resulting data to be shown
            lvStranke.Items.Clear();

            foreach (var customer in query)
            {                 
                lvStranke.Items.Add(customer);
            }
        }
    }

Here is my version of sorting in descending order when they get loaded/refreshed:

    public void LiteDB_ShowAll()
    {
        using (var dataBase = new LiteDatabase(dbConnectionString))
        {
            var collection = dataBase.GetCollection<Stranka>("stranka");
            lvStranke.Items.Clear();

            var count = collection.Count(Query.All());
            // From last ID to first (Descending)
            for (int i = count; i >= 1; i--)
            {
                lvStranke.Items.Add(collection.FindById(i));
            }               
        }         
    }

score 4 · Answer 1 · answered Oct 27 '17 at 09:23

The best way to search is using an index in your query field and order the results, like this:

public void LiteDB_ShowAll()
{
    using (var dataBase = new LiteDatabase(dbConnectionString))
    {
        var collection = dataBase.GetCollection<Stranka>("stranka");
        collection.EnsureIndex("Ime_Priimek");

        lvStranke.Items.Clear();

        var result = collection
            .Find(Query.StartsWith("Ime_Priimek", searchName_tb.Text))
            .OrderBy(x => x["_id"].AsInt32);

        foreach (var item in result)
        {
            lvStranke.Items.Add(item);
        }               
    }         
}

In this case, you will need order only filtered documents.

This solution sounds better for me in regards of performances consumption — Fabrice T, Sep 23 '20 at 02:54

score 0 · Accepted Answer · answered Oct 26 '17 at 22:32

Have you looked at the example at the Queries wiki page? It looks like you can use LINQ to sort objects you've got by Find although this would be done on the client-side rather than by the engine itself.

    public void LiteDB_ShowAll()
    {
        using (var dataBase = new LiteDatabase(dbConnectionString))
        {
            var collection = dataBase.GetCollection<Stranka>("stranka");
            lvStranke.Items.Clear();

            foreach(var cust in collection.FindAll().OrderByDescending(x => x.Date)) // or by x.Id
            {
                lvStranke.Items.Add(cust);
            }               
        }         
    }

The important drawback in your LiteDB_ShowAll implementation is not its inefficiency. It is the fact that it expects that Customer objects can never be deleted from the DB and this is often just not true. So you eventually will have "gaps" in your IDs and that will break your code.

I do have a Delete function and so far I didn't have any trouble but the LiteDB_ShowAll is basically just to update the changes in the database and show them in the ListView. This was the first approach that came to mind though... I can add that code aswell. Trying to find ways to improve it. Looking forward to the reply and Thanks for the answer! — McBooley, Oct 26 '17 at 22:44
@McBooley, I'm not sure what reply you expect from me. As for Delete, the trouble should be that 1) You don't get the Customer with maximum ID (as count is less than the maximum ID) 2) You get `null` for some ID from `FindById` (for the deleted ID) — SergGr, Oct 26 '17 at 22:49

LiteDB multiple queries in C#

2 Answers2