Skip to main content

Use Logical Operators

  • A query's where clause can combine multiple filtering conditions with logical operators.
    This lets you express richer predicates than a single condition, for example:
    "products that are in stock and cost more than 50", or "employees whose first name is Robert or Nancy".

  • This article covers the logical operators available when filtering query results:
    AND, OR, and NOT, as well as how to group conditions to control operator precedence.
    The examples are shown with:

    • Query
      the LINQ-based query API, using C# operators such as &&, ||, !, and !=.
    • DocumentQuery
      the low-level query API, using methods and modifiers such as AndAlso, OrElse, Not, and WhereNotEquals.
    • RawQuery
      the client API for executing an RQL string directly, with query parameters supplied by AddParameter.
    • RQL
      the raw query language, using and, or, not (...), and comparison operators such as !=.
      RQL keywords are case-insensitive; the examples below use lowercase.

  • The same logical-operator rules apply when querying a collection dynamically and when querying a static index.

  • For filtering by field value, numeric value, nested field, collection, document ID, and other basic conditions,
    see Filter query results.

Combine conditions with AND​

Use the AND operator to return only documents that satisfy all conditions.

For example, the following query returns Employee documents where FirstName is "Robert" and LastName is "King".

List<Employee> employees = session
    .Query<Employee>()
    .Where(e => e.FirstName == "Robert" && e.LastName == "King")
    .ToList();

Auto-index used: Auto/Employees/ByFirstNameAndLastName
The auto-index is derived from the FirstName and LastName fields referenced in the where clause.

Chaining conditions uses AND​

  • In a LINQ query, chaining multiple Where calls combines the conditions with AND.
    For example:
    .Where(e => e.FirstName == "Robert").Where(e => e.LastName == "King")
    is equivalent to:
    .Where(e => e.FirstName == "Robert" && e.LastName == "King").

  • In a DocumentQuery, consecutive Where* predicates are also combined with AND by default.
    For example:
    .WhereEquals(e => e.FirstName, "Robert").WhereEquals(e => e.LastName, "King")
    is equivalent to:
    .WhereEquals(e => e.FirstName, "Robert").AndAlso().WhereEquals(e => e.LastName, "King").

Combine conditions with OR​

Use the OR operator to return documents that satisfy at least one of the specified conditions.

For example, the following query returns Employee documents where FirstName is either "Robert" or "Nancy".

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

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

Use IN for multiple values on the same field​

When the same field is compared against multiple possible values, as in the example above,
you can use In / WhereIn instead of repeating OR conditions. See Filter by multiple possible values.

Negate a condition with NOT​

Use the NOT operator to return only documents that do not satisfy the specified condition.

For example, the following query returns Employee documents where FirstName is not "Robert".

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

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

Using WhereNotEquals with DocumentQuery​

  • In a DocumentQuery, the Not operator negates only the next predicate or subclause.
    It cannot be used on its own; it must be followed by a predicate or by OpenSubclause().

  • For a single equality condition, you can use WhereNotEquals instead of Not.WhereEquals.
    For example:

    List<Employee> employees = session.Advanced
        .DocumentQuery<Employee>()
        .WhereNotEquals(e => e.FirstName, "Robert")
        .ToList();

    is equivalent to:

    List<Employee> employees = session.Advanced
        .DocumentQuery<Employee>()
        .Not
        .WhereEquals(e => e.FirstName, "Robert")
        .ToList();

Negate a group of conditions​

Use NOT with grouping to negate multiple conditions as a single unit.

For example, the following query returns Employee documents that are not
(FirstName is "Robert" and LastName is "King").

In a DocumentQuery, place Not before OpenSubclause() to negate the whole group.

List<Employee> employees = session
    .Query<Employee>()
    .Where(e => !(e.FirstName == "Robert" && e.LastName == "King"))
    .ToList();

Auto-index used: Auto/Employees/ByFirstNameAndLastName
The auto-index is derived from the FirstName and LastName fields referenced in the where clause.

Alternative syntax​

  • not (A and B) is equivalent to not A or not B.
    For example, the query above:
    not (FirstName = "Robert" and LastName = "King") can also be expressed as:
    FirstName != "Robert" or LastName != "King".

  • In a DocumentQuery, this is:
    WhereNotEquals(e => e.FirstName, "Robert").OrElse().WhereNotEquals(e => e.LastName, "King").

  • In RQL, this is:
    from Employees where FirstName != "Robert" or LastName != "King".

Group conditions to control precedence​

When you mix AND and OR in the same query, use grouping to control which conditions are evaluated together.
In a DocumentQuery, group conditions with OpenSubclause() and CloseSubclause();
in a LINQ query and in RQL, use parentheses.

For example, the following query returns Employee documents whose Title is "Sales Representative"
and whose FirstName is either "Robert" or "Nancy".

List<Employee> employees = session
    .Query<Employee>()
    .Where(e => e.Title == "Sales Representative" &&
               (e.FirstName == "Robert" || e.FirstName == "Nancy"))
    .ToList();

Auto-index used: Auto/Employees/ByFirstNameAndTitle
The auto-index is derived from the Title and FirstName fields referenced in the where clause.

Grouping matters​

Without the grouping, the query:
Title = "Sales Representative" and FirstName = "Robert" or FirstName = "Nancy"
is evaluated as:
(Title = "Sales Representative" and FirstName = "Robert") or FirstName = "Nancy",
because AND has higher precedence than OR (see Operator precedence and defaults).
This returns a different set of documents than the grouped query.

Logical operators when querying a static index​

The logical operators work the same way when querying a static index.
The conditions in the where clause are evaluated against the index-fields.

For example, if a static index defines FirstName, LastName, and Title fields, you can combine conditions on those fields just as you would in a dynamic query:

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

Operator precedence and defaults​

  • Precedence:
    When AND and OR appear together without grouping, AND is evaluated before OR.
    In other words, a and b or c is interpreted as (a and b) or c.
    Use grouping (OpenSubclause / CloseSubclause or parentheses) whenever you need a different evaluation order.

  • Default operator in DocumentQuery:
    When consecutive Where* predicates in a DocumentQuery are written without an explicit
    AndAlso or OrElse between them, they are combined with AND by default.
    Use AndAlso to make the implicit AND explicit, or OrElse when you need OR.

  • NOT requires a following predicate or subclause:
    The Not operator negates the predicate or subclause that comes immediately after it.
    It cannot be used on its own.

In this article