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 uma resposta

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:

Em um post anterior, indiquei que um servidor de identidades seria uma bela alternativa para um “primeiro microsserviço”. Neste post,...
Há alguns anos, cheguei, por acaso, a uma palestra do Simon Sinek no TED. Na época, ele ainda era um...
Some years ago, Alistair Cockburn proposed this interesting pattern. Quoting his words, the primary intent is: Allow an application to...
Agora em fevereiro, depois de 18 meses, fechei meu ciclo como CTO na Guiando para integrar o conselho consultivo da...
“Microservices” is a trending topic. Big companies are trying to associate their technologies with this concept – but, it is...
I spent the last week talking about RavenDB in San Francisco. By the way, version 4 is out. Also, I...