see on GitHub

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

    see on GitHub

    related articlesRelated Articles

    Client API

    Indexes

    Querying

    Home / Docs / Indexes / Querying / Filtering