Filter Query Results

• One of the most basic functionalities of querying is the ability to filter out data and return records that match a given condition.

• RavenDB provides several ways to run queries, including:

  • Query from the basic Session API
  • DocumentQuery from the Session.Advanced API
  • RQL - Raven Query Language

• The examples in this page demonstrate how filtering is applied by each of the above querying methods.

• In this page:

  • Where
  • Where - Numeric Property
  • Where - Nested Property
  • Where + Any
  • Where + In
  • Where + ContainsAny
  • Where + ContainsAll
  • Where - StartsWith
  • Where - EndsWith
  • Where - Identifier Property
  • Where - Exists
  • Remarks

Where

• Query
• DocumentQuery
• Index
• RQL

IList<Employee> results = session
  .Query<Employee, Employees_ByFirstAndLastName>()                // query 'Employees/ByFirstAndLastName' index
  .Where(x => x.FirstName == "Robert" && x.LastName == "King")    // filtering predicates
  .ToList();                                                      // materialize query by sending it to server for processing

IList<Employee> results = session
  .Advanced
  .DocumentQuery<Employee, Employees_ByFirstAndLastName>()    // query 'Employees/ByFirstAndLastName' index
  .WhereEquals(x => x.FirstName, "Robert")                    // filtering predicates
  .AndAlso()                                                  // by default OR is between each condition
  .WhereEquals(x => x.LastName, "King")                       // filtering predicates
  .ToList();                                                  // materialize query by sending it to server for processing

public class Employees_ByFirstAndLastName : AbstractIndexCreationTask<Employee>
{
  public Employees_ByFirstAndLastName()
  {
      Map = employees => from employee in employees
                         select new
                         {
                             FirstName = employee.FirstName,
                             LastName = employee.LastName
                         };
  }
}

from index 'Employees/ByFirstAndLastName'
where FirstName = 'Robert' and LastName = 'King'

Where - Numeric Property

• Query
• DocumentQuery
• Index
• RQL

IList<Product> results = session
  .Query<Product, Products_ByUnitsInStock>()  // query 'Products/ByUnitsInStock' index
  .Where(x => x.UnitsInStock > 50)            // filtering predicates
  .ToList();                                  // materialize query by sending it to server for processing

IList<Product> results = session
  .Advanced
  .DocumentQuery<Product, Products_ByUnitsInStock>()  // query 'Products/ByUnitsInStock' index
  .WhereGreaterThan(x => x.UnitsInStock, 50)          // filtering predicates
  .ToList();                                          // materialize query by sending it to server for processing

private class Products_ByUnitsInStock : AbstractIndexCreationTask<Product>
{
  public Products_ByUnitsInStock()
  {
      Map = products => from product in products
                        select new
                        {
                            product.UnitsInStock
                        };
  }
}

from index 'Products/ByUnitsInStock'
where UnitsInStock > 50

Where - Nested Property

• Query
• DocumentQuery
• RQL

// return all orders that were shipped to 'Albuquerque'
IList<Order> results = session
  .Query<Order>()
  .Where(x => x.ShipTo.City == "Albuquerque")
  .ToList();

// return all orders that were shipped to 'Albuquerque'
IList<Order> results = session
  .Advanced
  .DocumentQuery<Order>()
  .WhereEquals(x => x.ShipTo.City, "Albuquerque")
  .ToList();

from Orders
where ShipTo.City = 'Albuquerque'

• Query
• DocumentQuery
• Index
• RQL

IList<Order> results = session
  .Query<Order, Order_ByOrderLinesCount>()    // query 'Order/ByOrderLinesCount' index
  .Where(x => x.Lines.Count > 50)             // filtering predicates
  .ToList();                                  // materialize query by sending it to server for processing

IList<Order> results = session
  .Advanced
  .DocumentQuery<Order, Order_ByOrderLinesCount>()    // query 'Order/ByOrderLinesCount' index
  .WhereGreaterThan(x => x.Lines.Count, 50)           // filtering predicates
  .ToList();                                          // materialize query by sending it to server for processing

private class Order_ByOrderLinesCount : AbstractIndexCreationTask<Order>
{
  public Order_ByOrderLinesCount()
  {
      Map = orders => from order in orders
                      select new
                      {
                          Lines_Count = order.Lines.Count
                      };
  }
}

from index 'Order/ByOrderLinesCount'
where Lines.Count > 50

Where + Any

Any is useful when you have a collection of items (e.g. Order contains OrderLines) and you want to filter out based on values from this collection. For example, let's retrieve all orders that contain an OrderLine with a given product.

• Query
• DocumentQuery
• Index
• RQL

IList<Order> results = session
  .Query<Order, Order_ByOrderLines_ProductName>()                                 // query 'Order/ByOrderLines/ProductName' index
  .Where(x => x.Lines.Any(l => l.ProductName == "Teatime Chocolate Biscuits"))    // filtering predicates
  .ToList();                                                                      // materialize query by sending it to server for processing

IList<Order> results = session
  .Advanced
  .DocumentQuery<Order, Order_ByOrderLines_ProductName>()         // query 'Order/ByOrderLines/ProductName' index
  .WhereEquals("Lines_ProductName", "Teatime Chocolate Biscuits") // filtering predicates
  .ToList();                                                      // materialize query by sending it to server for processing

public class Order_ByOrderLines_ProductName : AbstractIndexCreationTask<Order>
{
  public Order_ByOrderLines_ProductName()
  {
      Map = orders => from order in orders
                      select new
                      {
                          Lines_ProductName = order.Lines.Select(x => x.ProductName)
                      };
  }
}

from index 'Order/ByOrderLinesCount'
where Lines_ProductName = 'Teatime Chocolate Biscuits'

Where + In

When you want to check a single value against multiple values, the In operator can become handy. To retrieve all employees where FirstName is either Robert or Nancy, we can issue the following query:

• Query
• DocumentQuery
• Index
• RQL

IList<Employee> results = session
  .Query<Employee, Employees_ByFirstAndLastName>()    // query 'Employees/ByFirstAndLastName' index
  .Where(x => x.FirstName.In("Robert", "Nancy"))      // filtering predicates (remember to add `Raven.Client.Linq` namespace to usings)
  .ToList();                                          // materialize query by sending it to server for processing

IList<Employee> results = session
  .Advanced
  .DocumentQuery<Employee, Employees_ByFirstAndLastName>()    // query 'Employees/ByFirstAndLastName' index
  .WhereIn(x => x.FirstName, new[] { "Robert", "Nancy" })     // filtering predicates
  .ToList();                                                  // materialize query by sending it to server for processing

public class Employees_ByFirstAndLastName : AbstractIndexCreationTask<Employee>
{
  public Employees_ByFirstAndLastName()
  {
      Map = employees => from employee in employees
                         select new
                         {
                             FirstName = employee.FirstName,
                             LastName = employee.LastName
                         };
  }
}

from index 'Employees/ByFirstAndLastName'
where FirstName IN ('Robert', 'Nancy')

Important

Remember to add the Raven.Client.Documents.Linq namespace to usings if you want to use In extension method.

Where + ContainsAny

To check if enumeration contains any of the values from a specified collection, you can use the ContainsAny method.

Let's assume that we want to return all BlogPosts that contain any of the specified Tags.

• Query
• DocumentQuery
• Index
• RQL

IList<BlogPost> results = session
  .Query<BlogPost, BlogPosts_ByTags>()                                    // query 'BlogPosts/ByTags' index
  .Where(x => x.Tags.ContainsAny(new[] { "Development", "Research" }))    // filtering predicates (remember to add `Raven.Client.Linq` namespace to usings)
  .ToList();                                                              // materialize query by sending it to server for processing

IList<BlogPost> results = session
  .Advanced
  .DocumentQuery<BlogPost, BlogPosts_ByTags>()                // query 'BlogPosts/ByTags' index
  .ContainsAny("Tags", new[] { "Development", "Research" })   // filtering predicates
  .ToList();                                                  // materialize query by sending it to server for processing

public class BlogPosts_ByTags : AbstractIndexCreationTask<BlogPost>
{
  public BlogPosts_ByTags()
  {
      Map = posts => from post in posts
                     select new
                     {
                         post.Tags
                     };
  }
}

from index 'BlogPosts/ByTags'
where Tags IN ('Development', 'Research')

Important

Remember to add the Raven.Client.Documents.Linq namespace to usings if you want to use the ContainsAny extension method.

Where + ContainsAll

To check if an enumeration contains all of the values from a specified collection, you can use the ContainsAll method.

Let's assume that we want to return all the BlogPosts that contain all of the specified Tags.

• Query
• DocumentQuery
• Index
• RQL

IList<BlogPost> results = session
  .Query<BlogPost, BlogPosts_ByTags>()                                    // query 'BlogPosts/ByTags' index
  .Where(x => x.Tags.ContainsAll(new[] { "Development", "Research" }))    // filtering predicates (remember to add `Raven.Client.Linq` namespace to usings)
  .ToList();                                                              // materialize query by sending it to server for processing

IList<BlogPost> results = session
  .Advanced
  .DocumentQuery<BlogPost, BlogPosts_ByTags>()                // query 'BlogPosts/ByTags' index
  .ContainsAll("Tags", new[] { "Development", "Research" })   // filtering predicates
  .ToList();                                                  // materialize query by sending it to server for processing

public class BlogPosts_ByTags : AbstractIndexCreationTask<BlogPost>
{
  public BlogPosts_ByTags()
  {
      Map = posts => from post in posts
                     select new
                     {
                         post.Tags
                     };
  }
}

from index 'BlogPosts/ByTags'
where Tags ALL IN ('Development', 'Research')

Important

Remember to add the Raven.Client.Documents.Linq namespace to usings if you want to use the ContainsAll extension method.

Where - StartsWith

• Query
• DocumentQuery
• RQL

// return all products which name starts with 'ch'
IList<Product> results = session
  .Query<Product>()
  .Where(x => x.Name.StartsWith("ch"))
  .ToList();

// return all products which name starts with 'ch'
IList<Product> results = session
  .Advanced
  .DocumentQuery<Product>()
  .WhereStartsWith(x => x.Name, "ch")
  .ToList();

from Products 
where startsWith(Name, 'ch')

Where - EndsWith

• Query
• DocumentQuery
• RQL

// return all products which name ends with 'ra'
IList<Product> results = session
  .Query<Product>()
  .Where(x => x.Name.EndsWith("ra"))
  .ToList();

// return all products which name ends with 'ra'
IList<Product> results = session
  .Advanced
  .DocumentQuery<Product>()
  .WhereEndsWith(x => x.Name, "ra")
  .ToList();

from Products 
where endsWith(Name, 'ra')

Where - Identifier Property

Once a property used in the Where clause is recognized as an identity property of a given entity type (according to FindIdentityProperty convention) and there aren't any other fields involved in the query, then it is called a "collection query". Simple collection queries that ask about documents with given IDs or where identifiers start with a given prefix and don't require any additional handling like ordering, full-text searching, etc, are handled directly by the storage engine. It means that querying by ID doesn't create an auto-index and has no extra cost. In terms of efficiency, it is the same as loading documents with session.Load usage.

• Query
• DocumentQuery
• RQL

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

Order order = session
  .Advanced
  .DocumentQuery<Order>()
  .WhereEquals(x => x.Id, "orders/1-A")
  .FirstOrDefault();

from Orders
where id() = 'orders/1-A'

• Query
• DocumentQuery
• RQL

IList<Order> orders = session
  .Query<Order>()
  .Where(x => x.Id.StartsWith("orders/1"))
  .ToList();

IList<Order> orders = session
  .Advanced
  .DocumentQuery<Order>()
  .WhereStartsWith(x => x.Id, "orders/1")
  .ToList();

from Orders
where startsWith(id(), 'orders/1')

Where - Exists

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.

Remarks

Query and DocumentQuery are converting predicates to the IndexQuery class so they can issue a query from a low-level operation method.