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 =
        session.Query<Orders_SearchByProduct.QueryModel,Orders_SearchByProduct>()
            .Where(o => o.Query == "Manjimup Dried Apples")
            .OfType<Order>();
                    
    foreach (var order in q.ToList())
    {
        Console.WriteLine(order.Id);
    }
}

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

That’s all.