Queries

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

• Query static helper class
• Using LINQ expressions

Query implementations

Query is an abstract class that has many implementations in LiteDB. Each implementation gives 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 map to your object. You need use 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 property name. See Object Mapping.
• LiteDB does not support values as expressions, like CreationDate == DueDate.

Find(), FindById(), FindOne() and FindAll()

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() support Skip and Limit parameters. This operation are used in index level, so it's more efficient than in LINQ to object.

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 Object

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

Count() and Exists()

These two methods are useful because you can count document (or check if exists) without deserializing document. This 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 index to search and count indexes occurences of "Name = John" without deserialize + mapper document.
• If Name field does not have an index, LiteDB will deserialize document but will not run mapper to object. Still fast then Find().Count()
• Same idea if you use Exists(), witch are better than use Count() >= 1. Count need visit all matched results and Exists() stops on first match.

Min() and Max()

LiteDB use skip list implementation on indexes (See Indexes). Collections offers 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 need read only 2 index nodes (tail + previous).

LINQ expressions

LiteDB supports simple LINQ expression to be easy to write your queries. This LINQ works only with strong typed documents. But if you are working with BsonDocument, you need 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.