Skip to main content

Filter Query Results

Filtering and index usage​

  • When querying a collection with a filtering condition without specifying an index,
    RavenDB treats the query as a dynamic query and will use an AUTO-index to process the filter.

    If no suitable auto-index exists yet, RavenDB will create one automatically based on the fields used in the query. Later queries that need the same indexed fields will reuse the same auto-index.
    To learn more about dynamic queries and auto-indexes, see Query overview.

  • When querying a STATIC index, RavenDB will use the specified index to process the filter.
    The filtering condition is evaluated against the fields indexed by that index, and no auto-index is created.

Filter by field value​

Use Where to return only documents where a field has the specified value.
For example, the following query will return all Employee documents where FirstName is "Robert".

List<Employee> employees = session
    .Query<Employee>()                   // Query the Employees collection
    .Where(e => e.FirstName == "Robert") // Apply the filter predicate
    .ToList();                           // Execute the query and return the results

Auto-index used: Auto/Employees/ByFirstName
The auto-index is derived from the FirstName field referenced in the where clause.

Filter by numeric value​

You can filter numeric fields using comparison operators.
For example, the following query returns all products that have more than 50 units in stock.

List<Product> products = session
    .Query<Product>()
    .Where(p => p.UnitsInStock > 50)
    .ToList();

Auto-index used: Auto/Products/ByUnitsInStock
The auto-index is derived from the UnitsInStock field referenced in the where clause.

The auto-index name does not depend on the comparison operator (=, >, <, ...),
so the same auto-index serves any comparison on UnitsInStock.

You can use the following comparison operators:

OperatorDescription
=Equal to
!=Not equal to
>Greater than
>=Greater than or equal to
<Less than
<=Less than or equal to

Filter by nested field​

To filter by a nested field, specify the full path to the field.
For example, the following query returns all orders that were shipped to Albuquerque.

List<Order> orders = session
    .Query<Order>()
    .Where(o => o.ShipTo.City == "Albuquerque") // Filter by the nested field's full path
    .ToList();

Auto-index used: Auto/Orders/ByShipTo.City
The auto-index is derived from the nested field path ShipTo.City referenced in the where clause.

The full dotted path is preserved in the auto-index name.

Filter by values in a collection​

Use Any when the field you want to filter by is inside a collection.

For example, each Order document contains a Lines collection.
The following query returns every Order document that contains at least one order line for
Teatime Chocolate Biscuits.

List<Order> orders = session
    .Query<Order>()
    .Where(o => o.Lines.Any(line =>
        line.ProductName == "Teatime Chocolate Biscuits"))
    .ToList();

Auto-index used: Auto/Orders/ByLines[].ProductName
The auto-index is derived from the array-element path Lines[].ProductName referenced in the where clause.

The [] notation in the auto-index name indicates that Lines is a collection and that the index is built over each element's ProductName.

Filter by multiple possible values​

Use In when a field can match any value from a specified list.
For example, the following query returns Employee documents whose first name is either Robert or Nancy.

List<Employee> employees = session
    .Query<Employee>()
     // Requires: using Raven.Client.Documents.Linq;
    .Where(e => e.FirstName.In("Robert", "Nancy"))
    .ToList();

Auto-index used: Auto/Employees/ByFirstName The auto-index is derived from the FirstName field referenced in the where clause.

The same auto-index serves both equality (FirstName = "Robert") and in queries on this field -
the operator does not affect the auto-index name.
This means the auto-index created in Filter by field value is reused here, and no new auto-index is created.

Filter by matching any value in a collection​

Use ContainsAny to return documents where a collection field contains at least one value from the specified list.

Assume the following BlogPost class:

public class BlogPost
{
    public string Id { get; set; }
    public string Title { get; set; }
    public string[] Tags { get; set; }
}

The following query returns all blog posts that contain either the Development tag or the Research tag.
The query will return a document if the Tags collection contains at least one of the specified values.

List<BlogPost> posts = session
    .Query<BlogPost>()
     // Requires: using Raven.Client.Documents.Linq;        
    .Where(p => p.Tags.ContainsAny(new[] { "Development", "Research" }))
    .ToList();

Auto-index used: Auto/BlogPosts/ByTags
The auto-index is derived from the Tags collection field referenced in the where clause.

Filter by matching all values in a collection​

Use ContainsAll to return documents where a collection field contains all values from the specified list. For example, the following query returns only blog posts where the Tags collection contains both Development and Research.

List<BlogPost> posts = session
    .Query<BlogPost>()
     // Requires: using Raven.Client.Documents.Linq;        
    .Where(p => p.Tags.ContainsAll(new[] { "Development", "Research" }))
    .ToList();

Auto-index used: Auto/BlogPosts/ByTags
The auto-index is derived from the Tags collection field referenced in the where clause.

ContainsAny (in) and ContainsAll (all in) produce the same auto-index name;
the operator only affects which documents match, not which auto-index is created or used.
This means the auto-index created for Filter by matching any value in a collection is reused here.

Filter by string prefix or suffix​

Use StartsWith to return documents where a string field starts with the specified value.
Learn more in Starts-with query.

Use EndsWith to return documents where a string field ends with the specified value.
Learn more in Ends-with query.

Filter by field presence​

To find all documents in a collection that have a specified field, see How to Filter by Field Presence.

To find all documents in a collection that don't have a specified field, see How to Filter by Non-Existing Field.

Filter by document ID​

You can filter by document ID or by an ID prefix. When the query only filters by the document ID, RavenDB does not need to create an auto-index. Instead, the query is served directly by the storage engine.

This applies when the property used in the Where clause is recognized as the identity property of the entity type, according to the FindIdentityProperty convention, and no other fields are involved in the query.

Such queries are considered Collection queries. Simple collection queries that ask for documents by ID, or by an ID prefix, and do not require additional handling such as ordering or full-text search, have no additional indexing cost.

In terms of efficiency, querying by ID is the same as loading documents using session.Load.

For example, the following query returns the order whose document ID is orders/1-A.

Order order = session
    .Query<Order>()
    .Where(o => o.Id == "orders/1-A")
    .FirstOrDefault();

No auto-index is created.
Filtering only by document ID is served directly by the storage engine.

You can also filter by an ID prefix to return all documents whose IDs start with a specified value:

List<Order> orders = session
    .Query<Order>()
    .Where(o => o.Id.StartsWith("orders/"))
    .ToList();

No auto-index is created.
Filtering by an ID prefix is also handled directly by the storage engine.

Filtering when querying static indexes​

All filtering methods shown above can also be used when querying a static index.
However, when querying a static index, the filter must reference fields that are defined by the index.

For example, consider the following index:

public class Employees_ByFirstAndLastName :  AbstractIndexCreationTask<Employee>
{
    public class IndexEntry
    {
        // The index fields
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }
    
    public Employees_ByFirstAndLastName()
    {
        Map = employees => from employee in employees
                           select new IndexEntry
                           {
                               FirstName = employee.FirstName,
                               LastName  = employee.LastName
                           };
    }
}

You can query the index and filter the resulting documents by the index fields.
The following example queries the index for Employee documents and filters them by the index field FirstName.

List<Employee> employees = session
    .Query<Employee, Employees_ByFirstAndLastName>()
    .Where(e => e.FirstName == "Robert")
    .ToList();

Syntax​

LINQ extension methods (Raven.Client.Documents.Linq)

// Match if the field equals any of the provided values.
public static bool In<T>(this T field, params T[] values);
public static bool In<T>(this T field, IEnumerable<T> values);

// Match if the collection field shares at least one value with the provided values.
public static bool ContainsAny<T>(this IEnumerable<T> list, IEnumerable<T> items);

// Match if the collection field contains every provided value.
public static bool ContainsAll<T>(this IEnumerable<T> list, IEnumerable<T> items);

These methods exist as placeholders for the LINQ provider and are only meaningful inside a query expression.
Calling them outside a Where will throw.

DocumentQuery / AsyncDocumentQuery filtering methods

Each filter method below has two overloads:
one taking a string field name (useful for nested or array paths such as "ShipTo.City" or "Lines.ProductName"),
and one taking an Expression<Func<T, TValue>> property selector.

The optional exact parameter (default false) controls case sensitivity for string comparisons.

// Equality and comparison
TSelf WhereEquals(string fieldName, object value, bool exact = false);
TSelf WhereEquals<TValue>(Expression<Func<T, TValue>> propertySelector, TValue value, bool exact = false);

TSelf WhereNotEquals(string fieldName, object value, bool exact = false);
TSelf WhereNotEquals<TValue>(Expression<Func<T, TValue>> propertySelector, TValue value, bool exact = false);

TSelf WhereGreaterThan(string fieldName, object value, bool exact = false);
TSelf WhereGreaterThan<TValue>(Expression<Func<T, TValue>> propertySelector, TValue value, bool exact = false);

TSelf WhereGreaterThanOrEqual(string fieldName, object value, bool exact = false);
TSelf WhereGreaterThanOrEqual<TValue>(Expression<Func<T, TValue>> propertySelector, TValue value, bool exact = false);

TSelf WhereLessThan(string fieldName, object value, bool exact = false);
TSelf WhereLessThan<TValue>(Expression<Func<T, TValue>> propertySelector, TValue value, bool exact = false);

TSelf WhereLessThanOrEqual(string fieldName, object value, bool exact = false);
TSelf WhereLessThanOrEqual<TValue>(Expression<Func<T, TValue>> propertySelector, TValue value, bool exact = false);

// Collection / set matching
TSelf WhereIn(string fieldName, IEnumerable<object> values, bool exact = false);
TSelf WhereIn<TValue>(Expression<Func<T, TValue>> propertySelector, IEnumerable<TValue> values, bool exact = false);

TSelf ContainsAny(string fieldName, IEnumerable<object> values);
TSelf ContainsAny<TValue>(Expression<Func<T, IEnumerable<TValue>>> propertySelector, IEnumerable<TValue> values);

TSelf ContainsAll(string fieldName, IEnumerable<object> values);
TSelf ContainsAll<TValue>(Expression<Func<T, IEnumerable<TValue>>> propertySelector, IEnumerable<TValue> values);

// Prefix
TSelf WhereStartsWith(string fieldName, object value, bool exact);
TSelf WhereStartsWith<TValue>(Expression<Func<T, TValue>> propertySelector, TValue value, bool exact);

RawQuery / AsyncRawQuery

// Submit an RQL string directly.
IRawDocumentQuery<T>      RawQuery<T>(string query);
IAsyncRawDocumentQuery<T> AsyncRawQuery<T>(string query);

// Bind a value to an RQL parameter referenced as $paramName.
TSelf AddParameter(string name, object value);
ParameterTypeDescription
fieldNamestringThe name of the field to filter on. Use a dotted path for nested fields (e.g. "ShipTo.City", "Lines.ProductName").
propertySelectorExpression<Func<T, TValue>>A lambda expression selecting the field to filter on.
valueobject / TValueThe value to compare against.
valuesIEnumerable<object> / IEnumerable<TValue>The set of values to compare against.
exactboolWhen true, string comparison is case-sensitive.
Default: false.
querystringAn RQL string. Use $paramName placeholders for values supplied via AddParameter.
namestringThe name of an RQL parameter (no $ prefix).