Querying: Filtering


One of the most basic functionalities of querying is the ability to filter out data and return records that match a given condition. There are a couple of ways to do this and they all depend on the querying approach you want to use:

  • Query from basic session operations
  • DocumentQuery from advanced session operations
  • RQL - Raven Query Language

The following examples demonstrate how to add various filtering conditions to a query.

Where

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

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

// 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'

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.

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:

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.

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.

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

// 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

// 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.

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'

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

Information

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