Querying: Filteringg

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 couple of ways to do this and they all depend on the querying approach you want to use (Queryfrom basic session operations, DocumentQuery from advanced session operations, or directly using RQL).

The following examples demonstrate how to add simple conditions to a query using all of those methods.

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

Remarks

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