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.
-
RavenDB provides several ways to run queries, including:
- Query from the basic
Session
API - DocumentQuery from the
Session.Advanced
API - RQL - Raven Query Language
- Query from the basic
-
The examples in this page demonstrate how filtering is applied by each of the above querying methods.
-
In this page:
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
Query
and DocumentQuery
are converting predicates to the IndexQuery
class
so they can issue a query from a low-level operation method.