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

    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

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

    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