Queries - negue/LiteDB GitHub Wiki

In LiteDB, queries must have an index on the search field to find documents. If there is no index on the field, LiteDB automatically creates a new index with default options. To make queries, you can use:

• Query static helper class
• Using LINQ expressions

Query is an static class that creates query criteria. Each method a different criteria operation that can be used to query documents.

• Query.All - Returns all documents. Can be specified an index field to read in ascending or descending index order.
• Query.EQ - Find document are equals (==) to value.
• Query.LT/LTE - Find documents less then (<) or less then equals (<=) to value.
• Query.GT/GTE - Find documents greater then (>) or greater then equals (>=) to value.
• Query.Between - Find documents between start/end value.
• Query.In - Find documents that are equals of listed values.
• Query.Not - Find documents that are NOT equals (!=) to value.
• Query.StartsWith - Find documents that strings starts with value. Valid only for string data type.
• Query.Contains - Find documents that strings contains value. Valid only for string data type. This query do index search, only index scan (slow for many documents).
• Query.And - Apply intersection between two queries results.
• Query.Or - Apply union between two queries results.

var results = collection.Find(Query.EQ("Name", "John Doe"));

var results = collection.Find(Query.GTE("Age", 25));

var results = collection.Find(Query.And(
    Query.EQ("FirstName", "John"), Query.EQ("LastName", "Doe")
));

var results = collection.Find(Query.StartsWith("Name", "Jo"));

In all queries:

• Field must have an index (by using EnsureIndex or [BsonIndex] attribute. See Indexes). If there is no index, LiteDB will create a new index on the fly.
• Field name on left side, Value (or values) on right side
• Queries are executed in BsonDocument class before mapping to your object. You need to use the BsonDocument field name and BSON types values. If you are using a custom ResolvePropertyName or [BsonField] attribute, you must use your document field name and not the property name on your type. See Object Mapping.
• LiteDB does not support values as expressions, like CreationDate == DueDate.

Collections are 4 ways to return documents:

• FindAll: Returns all documents on collection
• FindOne: Returns FirstOrDefault result of Find()
• FindById: Returns SingleOrDefault result of Find() by using primary key _id index.
• Find: Return documents using Query builder or LINQ expression on collection.

Find() supports Skip and Limit parameters. These operations are used at the index level, so it's more efficient than in LINQ to Objects.

Find() method returns an IEnumerable of documents. If you want do more complex filters, value as expressions, sorting or transforms results you can use LINQ to Objects.

collection.EnsureIndex(x => x.Name);

var result = collection
    .Find(Query.EQ("Name", "John Doe")) // This filter is executed in LiteDB using index
    .Where(x => x.CreationDate >= x.DueDate.AddDays(-5)) // This filter is executed by LINQ to Object
    .OrderBy(x => x.Age)
    .Select(x => new 
    { 
        FullName = x.FirstName + " " + x.LastName, 
        DueDays = x.DueDate - x.CreationDate 
    }); // Transform

These two methods are useful because you can count documents (or check if a document exists) without deserializing the document. These methods use In

// This way is more efficient
var count = collection.Count(Query.EQ("Name", "John Doe"));

// Than use Find + Count
var count = collection.Find(Query.EQ("Name", "John Doe")).Count();

• In the first count, LiteDB uses the index to search and count the number of index occurrences of "Name = John" without deserializing and mapping the document.
• If the Name field does not have an index, LiteDB will deserialize the document but will not run the mapper. Still faster than Find().Count()
• The same idea applies when using Exists(), which is again better than using Count() >= 1. Count needs to visit all matched results and Exists() stops on first match (similar to LINQ's Any extension method).

LiteDB uses a skip list implementation for indices (See Indexes). Collections offer Min and Max index values. The implementation is:

• Min - Read head index node (MinValue BSON data type) and move to next node. This node is the lowest value in index. If index are empty, returns MinValue. Lowest value is not the first value!
• Max - Read tail index node (MaxValue BSON data type) and move to previous node. This node is the highest value on index. If index are empty, returns MaxValue. Highest value is not the last value!

This Max operation are used on AutoId for Int32 types. It's fast to get this value because it needs to read only two index nodes (tail + previous).

Some LiteDB methods support predicates to allow you to easily query strongly typed documents. If you are working with BsonDocument, you need to use classic Query class methods.

var collection = db.GetCollection<Customer>("customer");

var results = collection.Find(x => x.Name == "John Doe");

var results = collection.Find(x => x.Age > 30);

var results = collection.Find(x => x.Name.StartsWith("John") && x.Age > 30);

• LINQ implementations are: ==, !=, >, >=, <, <=, StartsWith, Contains, Equals, &&, ||
• Property name support inner document field: x => x.Name.Last == "Doe"
• Behind the scene, LINQ expressions are converted to Query implementations using QueryVisitor class.