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
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
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:
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
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.
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:
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
.
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
.
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
Information
Underneath, Query
and DocumentQuery
are converting predicates to IndexQuery
class so they can issue a query from low-level command method.
Safe By Default
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.