How to query nested information in RavenDB?

Question

I have the following document called Reservation:

{
    "CustomerId": 1,
    "Items": [
        {
            "EmployeeId": "employees/1",
            "StartTime": "2011-08-15T07:20:00.0000000+03:00",
            "EndTime": "2011-08-15T07:40:00.0000000+03:00"
        },
        {
            "EmployeeId": "employees/1",
            "StartTime": "2011-08-15T07:40:00.0000000+03:00",
            "EndTime": "2011-08-15T09:10:00.0000000+03:00"
        },
        {
            "EmployeeId": "employees/3",
            "StartTime": "2011-08-16T07:20:00.0000000+03:00",
            "EndTime": "2011-08-16T11:35:00.0000000+03:00"
        }
    ]
    "ReservedAt": "2011-10-20T15:28:21.9941878+03:00"
}

In addition I have the following projection class:

public class ReservationItemProjection
{
    public string ReservationId { get; set; }
    public string CustomerId { get; set; }
    public string EmployeeId { get; set; }
    public DateTime StartTime { get; set; }
    public DateTime EndTime { get; set; }
}

What kind of index and query do I write if I want to find matching ReservationItemProjections? E.g.:

// invalid example query:
var matches = docs.Query<ReservationItemProjection,
    ReservationItemProjectionsIndex>()
    .Where(x =>
        x.EmployeeId == "employees/1" &&
        x.StartTime >= minTime &&
        x.EndTime <= maxTime)
    .ToList();

Please note, that I do not wish to get a list of Reservation documents but a list of ReservationItemProjection objects. The documentation says:

But while just getting the documents matching a particular query is useful, we can do better than that. Instead of getting the documents themselves, I want to get the values directly from the index, without getting the full document.

I have already tried using an index like this:

public class ReservationItemProjectionsIndex : 
    AbstractIndexCreationTask<Reservation, ReservationItemProjection>
{
    public ReservationItemProjectionsIndex()
    {
        Map = reservations => 
            from reservation in reservations
            from item in reservation.Items
            select new
            {
                ReservationId = reservation.Id,
                CustomerId = reservation.CustomerId,
                item.EmployeeId,
                item.StartTime,
                item.EndTime
            };
        Store(x => x.ReservationId, FieldStorage.Yes);
        Store(x => x.CustomerId, FieldStorage.Yes);
        Store(x => x.EmployeeId, FieldStorage.Yes);
        Store(x => x.StartTime, FieldStorage.Yes);
        Store(x => x.EndTime, FieldStorage.Yes);
    }
}

Somehow I can't get the query and index working: it either throws an exception about not being able to cast from ReservationItemProjection to Reservation or, when I have been able to get the ReservationItemProjection objects, they will have included all Items in all Reservations that have even one matching Item, even though my query has the Where-clause x.EmployeeId == "employees/1".

Summary: what is the required index? Does the index need only a Map clause or also Reduce or TransformResults? How do I write the query in C#?

Answer 1

Kasper, In RavenDB, you are querying for documents. While it is technically possible to do what you want, it is usually meaningless to do so, because the projected information doesn't have the required context to do something with it.

What is it that you are trying to do?

For reference, the index would be something like:

 from doc in docs.Items
 from reservation in doc.Reservations
 select new { reservation.EmployeeId, reservation.Start, reservation.End }

Then, mark EmployeeId, Start and End as Store.

Now, in your query, issue:

  session.Query<...,...>().AsProjection<ReservationProjection>().ToList();

The AsProjection call will let the DB know that you want the values from the index, not the document