You are currently browsing legacy 4.1 version of documentation. Click here to switch to the newest 6.2 version.
    We can help you with migration to the latest RavenDB
    Contact Us Now
    See on GitHub

    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.

    related articlesRelated Articles

    Indexes

    Querying

    Home / Docs / Indexes / Querying / Filtering