Filtering

One of the most basic functionalities when it comes to querying is the ability to filter out data and only return records that match given condition. There are couple of ways to do this, and they all depend on querying approach you want to use (Query from basic session operations, DocumentQuery from advanced session operations or low-level Command). Following example demonstrates how to add a simple conditions to query using all those methods.

Where

• Query
• DocumentQuery
• Commands
• Index

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

QueryResult result = store
.DatabaseCommands
.Query(
	"Employees/ByFirstAndLastName",
	new IndexQuery
		{
			Query = "FirstName:Robert AND LastName:King"
		});

public class Employees_ByFirstAndLastName : AbstractIndexCreationTask<Employee>
{
public Employees_ByFirstAndLastName()
{
	Map = employees => from employee in employees
					   select new
						{
							FirstName = employee.FirstName,
							LastName = employee.LastName
						};
}
}

Where - numeric property

• Query
• DocumentQuery
• Commands
• Index

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

QueryResult result = store
.DatabaseCommands
.Query(
	"Products/ByUnitsInStock",
	new IndexQuery
	{
		Query = "UnitsInStock_Range:{Ix50 TO NULL}"
	});

private class Products_ByUnitsInStock : AbstractIndexCreationTask<Product>
{
public Products_ByUnitsInStock()
{
	Map = products => from product in products
					  select new
						{
							product.UnitsInStock
						};
}
}

The importance of types in queries

Let's consider the following index and queries:

• Index
• Query
• DocumentQuery
• Commands

public class Orders_ByTotalPrice : AbstractIndexCreationTask<Order>
{
public class Result
{
	public decimal TotalPrice;
}

public Orders_ByTotalPrice()
{
	Map = orders => from order in orders
					select new
					{
						TotalPrice = order.Lines.Sum(x => (x.Quantity * x.PricePerUnit) * (1 - x.Discount))
					};
}
}

IList<Order> results = session
.Query<Orders_ByTotalPrice.Result, Orders_ByTotalPrice>()
.Where(x => x.TotalPrice > 50)
.OfType<Order>()
.ToList();

IList<Order> results = session
.Advanced
.DocumentQuery<Orders_ByTotalPrice.Result, Orders_ByTotalPrice>()
.WhereGreaterThan(x => x.TotalPrice, 50)
.OfType<Order>()
.ToList();

QueryResult result = store
.DatabaseCommands
.Query(
	"Orders/ByTotalPrice",
	new IndexQuery
	{
		Query = "TotalPrice_Range:{Dx50 TO NULL}"
	});

Note that PricePerUnit is of type decimal in Order entity, so indexed TotalPrice value will be decimal too. In oder to properly query such index, we need to preserve types in queries. That is why Orders_ByTotalPrice.Result.TotalPrice is decimal and IndexQuery.Query has Dx prefix specified before the actual value. Types of properties in predicates have to match types of indexed fields.

• More about the need to use OfType here, you can find in projections article.

Where - nested property

• Query
• DocumentQuery
• Commands
• Index

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

QueryResult result = store
.DatabaseCommands
.Query(
	"Order/ByOrderLinesCount",
	new IndexQuery
	{
		Query = "Lines.Count_Range:{Ix50 TO NULL}"
	});

private class Order_ByOrderLinesCount : AbstractIndexCreationTask<Order>
{
public Order_ByOrderLinesCount()
{
	Map = orders => from order in orders
					select new
					{
						Lines_Count = order.Lines.Count
					};
}
}

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 a values from this collection. For example, let's retrieve all orders that contain a OrderLine with a given product.

• Query
• DocumentQuery
• Commands
• Index

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

QueryResult result = store
.DatabaseCommands
.Query(
	"Order/ByOrderLinesCount",
	new IndexQuery
	{
		Query = "Lines_ProductName:\"Teatime Chocolate Biscuits\""
	});

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)
					};
}
}

Where + In

When you want to check single value against multiple values In operator can become handy. E.g. to retrieve all employees that FirstName is either Robert or Nancy we can issue following query:

• Query
• DocumentQuery
• Commands
• Index

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

QueryResult result = store
.DatabaseCommands
.Query(
	"Employees/ByFirstAndLastName",
	new IndexQuery
	{
		Query = "@in<FirstName>:(Robert, Nancy)"
	});

public class Employees_ByFirstAndLastName : AbstractIndexCreationTask<Employee>
{
public Employees_ByFirstAndLastName()
{
	Map = employees => from employee in employees
					   select new
						{
							FirstName = employee.FirstName,
							LastName = employee.LastName
						};
}
}

Important

Remember to add Raven.Client.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 ContainsAny method.

Let's assume that we want to return all BlogPosts that contain any of the specified Tags.

• Query
• DocumentQuery
• Commands
• Index

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

QueryResult result = store
.DatabaseCommands
.Query(
	"BlogPosts/ByTags",
	new IndexQuery
	{
		Query = "(Tags:Development OR Tags:Research)"
	});

public class BlogPosts_ByTags : AbstractIndexCreationTask<BlogPost>
{
public BlogPosts_ByTags()
{
	Map = posts => from post in posts
				   select new
					{
						post.Tags
					};
}
}

Important

Remember to add Raven.Client.Linq namespace to usings if you want to use ContainsAny extension method.

Where + ContainsAll

To check if enumeration contains all of the values from a specified collection you can use ContainsAll method.

Let's assume that we want to return all BlogPosts that contain all of the specified Tags.

• Query
• DocumentQuery
• Commands
• Index

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

QueryResult result = store
.DatabaseCommands
.Query(
	"BlogPosts/ByTags",
	new IndexQuery
	{
		Query = "(Tags:Development AND Tags:Research)"
	});

public class BlogPosts_ByTags : AbstractIndexCreationTask<BlogPost>
{
public BlogPosts_ByTags()
{
	Map = posts => from post in posts
				   select new
					{
						post.Tags
					};
}
}

Important

Remember to add Raven.Client.Linq namespace to usings if you want to use ContainsAll extension method.

Remarks

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

By default page size is set to 128 if not specified, so above queries will not return more than 128 results. You can read more about paging here.