Querying multiple fields easily using RavenDB

This one comes from Ayende’s book about RavenDB.

If you want to learn RavenDB basics, I would recommend you to subscribe RavenDB bootcamp (it’s free). But, if you want a deep dive, Ayende’s book is an excellent resource.

Consider this document:

    "Company": "companies/62",
    "Employee": "employees/7",
    "OrderedAt": "1997-01-07T00:00:00.0000000",
    "RequireAt": "1997-02-18T00:00:00.0000000",
    "ShippedAt": "1997-01-13T00:00:00.0000000",
    "ShipTo": {
        "Line1": "Alameda dos Canàrios, 891",
        "Line2": null,
        "City": "Sao Paulo",
        "Region": "SP",
        "PostalCode": "05487-020",
        "Country": "Brazil"
    "ShipVia": "shippers/1",
    "Freight": 108.04,
    "Lines": [
            "Product": "products/1",
            "ProductName": "Chai",
            "PricePerUnit": 14.4,
            "Quantity": 10,
            "Discount": 0
            "Product": "products/21",
            "ProductName": "Sir Rodney's Scones",
            "PricePerUnit": 8,
            "Quantity": 30,
            "Discount": 0.1
            "Product": "products/28",
            "ProductName": "Rössle Sauerkraut",
            "PricePerUnit": 36.4,
            "Quantity": 42,
            "Discount": 0.1
            "Product": "products/36",
            "ProductName": "Inlagd Sill",
            "PricePerUnit": 15.2,
            "Quantity": 5,
            "Discount": 0.1
            "Product": "products/40",
            "ProductName": "Boston Crab Meat",
            "PricePerUnit": 14.7,
            "Quantity": 2,
            "Discount": 0.1

What if we want to get all orders that have a particular product? How would be the query?

var q =
    from order in session.Query<Order>()
    where order.Lines.Any(x => x.Product == "products/1")
    select order;

Pretty simple, huh?! But, as you know, all RavenDB queries will use an index. If there is no index to support a query, RavenDB will generate one automatically for you.

Here is the map of the index created by RavenDB to support this query.

from doc in docs.Orders
select new {
	Lines_Product = (
		from docLinesItem in ((IEnumerable<dynamic>)doc.Lines).DefaultIfEmpty()
		select docLinesItem.Product).ToArray()

The interesting thing here is the use of an array of strings. When RavenDB encounters a collection in the index entry fields, it is actually indexing that field multiple times. Another important idea is that RavenDB will flatten out collections.

So, let’s define an index ourselves.

public class Orders_SearchByProduct :
    AbstractIndexCreationTask<Order, Orders_SearchByProduct.QueryModel>
    public class QueryModel
        public string Query;

    public Orders_SearchByProduct()
        Map = orders =>
            from order in orders
            select new
                Query = new object[]
                    order.Lines.Select(p => p.Product).ToArray(),
                    order.Lines.Select(p => p.ProductName).ToArray()

Now we can search orders that have a specific product considering his Id or name.

This is how we do the query.

using (var session = DocumentStoreHolder.Store.OpenSession())
    var q =
            .Where(o => o.Query == "Manjimup Dried Apples")
    foreach (var order in q.ToList())

Here we are using the OfType operator to change the result to the appopriate returned type.

That’s all.

Compartilhe este insight:

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Elemar Júnior

Sou fundador e CEO da EximiaCo e atuo como tech trusted advisor ajudando diversas empresas a gerar mais resultados através da tecnologia.

Elemar Júnior

Sou fundador e CEO da EximiaCo e atuo como tech trusted advisor ajudando diversas empresas a gerar mais resultados através da tecnologia.

Mais insights para o seu negócio

Veja mais alguns estudos e reflexões que podem gerar alguns insights para o seu negócio:

When designing systems that need to scale you always need to remember that using better resources could help you to...
A música é conhecida e todos sabem que ela encerra com uma nota alta. Mesmo assim, a execução de “Phantom...
When you think about Roslyn source code, you should think about performance-oriented design. I would like to share some performance techniques...
As a consultant, I often need to work with the code that I do not know. I need to understand...
There are a lot of scenarios where our applications need to support long-running processes. In this post, I will share...
Uma das premissas fundamentais do conceito de contrato social é que nós, como indivíduos livres, abrimos mão do direito natural...
× Precisa de ajuda?