RavenDb map index for additional calculations

Viewed 70

At the beginning I would like to mention that I am very new to any kind of NoSql databases. I guess I might have misunderstand the concept of indexes in RavenDb.

In my application I have a colletion of documents representing history of car refueling (with child collection of routes taken on this refuel). Simplified version looks like this:

public class Fuel
{
    public decimal? VolumeUsed { get; set; }
    public decimal CostPerLitre { get; set; }
    public decimal? TotalDistance { get; set; }
    public IList<Route> Routes { get; set; }
}

public class Route
{
    public string StartingAddress { get; set; }
    public IList<Stop> Stops { get; set; }
}

Documents in collection will not be modified often, so I decided to put all calculations into a databse, rather than calculates everything on the fly on each request. But I don't think that additional calculation fields should be in this document, as they will only depend on existing values and not supplied by user. And I thought about Map index - very nice way of doing calculations (and maybe aggregation in the future) each time document is inserted or modified.

I created another class for index

public class FuelCalculated
{
    public decimal? VolumeUsed { get; set; }
    public decimal CostPerLitre { get; set; }
    public decimal? TotalDistance { get; set; }
    public decimal? AverageFuelConsumption { get; set; }
    public decimal? TotalCost { get; set; }
    public IList<RouteCalculated> Routes { get; set; }
}

public class RouteCalculated
{
    public string StartingAddress { get; set; }
    public IList<Stop> Stops { get; set; }
    public decimal TotalDistance { get; set; }
    public decimal AverageFuelConsumption { get; set; }
}

and index defition:

public class FuelCalculatedIndex : AbstractIndexCreationTask<Fuel, FuelCalculated>
{
    public FuelCalculatedIndex()
    {
        Map = fuels =>
            fuels.Select(f => new FuelCalculated()
            {
                AverageFuelConsumption = (f.VolumeUsed * 100) / f.TotalDistance,
                Routes = f.Routes.Select(r => new RouteCalculated()
                {
                    StartingAddress = r.StartingAddress,
                    Stops = r.Stops,
                    TotalDistance = r.Stops.Sum(s => s.Distance),
                    AverageFuelConsumption = r.Stops.Sum(s => s.AverageFuelConsumption * s.Distance) / r.Stops.Sum(s => s.Distance),
                }).ToList(),
                TotalCost = f.VolumeUsed * f.CostPerLitre,
                TotalDistance = f.TotalDistance,
                VolumeUsed = f.VolumeUsed,
            });

        StoreAllFields(FieldStorage.Yes);
    }
}

For now I don't care about volume of the index - I want to have everything needed for the display, so queries don't have to jump to original document.

Now, when I query documents in code I get null results (or 0 for not nullable fields) in every calculated field:

using (var session = documentStore.OpenAsyncSession())
{
    return await session
        .Query<FuelCalculated, FuelCalculatedIndex>()
        .ToListAsync();
}

Returned JSON:

{
   "volumeUsed":28.04,
   "costPerLitre":4.93,
   "totalDistance":467.3,
   "totalCost":null,                       <----
   "averageFuelConsumption":null,          <----
   "routes":[
      {
         "startingAddress":"Address 1",
         "stops":[
            {
               "address":"Address 2",
               "distance":351.0,
               "averageFuelConsumption":6.0
            }
         ],
         "totalDistance":0,                <----
         "totalAverageFuelConsumption":0   <----
      },
      {
         "startingAddress":"Address 3",
         "stops":[
            {
               "address":"Address 4",
               "distance":116.3,
               "averageFuelConsumption":7.0
            }
         ],
         "totalDistance":0,                <----
         "totalAverageFuelConsumption":0   <----
      }
   ]
}

I got really confused when I tried simple RavenDb query in Raven.Studio and it returned proper values

from index 'FuelCalculatedIndex'
select TotalCost

So it seems that the problem lays in C# code. What do I do wrong?

UPDATE

I just tried runnig raw query in C#:

await session
    .Advanced.AsyncRawQuery<FuelCalculated>("from index 'FuelCalculatedIndex' select TotalCost")
    .ToListAsync();

Surprisingly, it works.

1 Answers

The result of a query is always a document (map index). So the original query should actually be:

using (var session = documentStore.OpenAsyncSession())
{
    return await session
        .Query<FuelCalculated, FuelCalculatedIndex>()
        .As<Fuel>
        .ToListAsync();
}

You need to use ProjectInto in order to get the stored fields:

using (var session = documentStore.OpenAsyncSession())
{
    return await session
        .Query<FuelCalculated, FuelCalculatedIndex>()
        .ProjectInto<FuelCalculated>
        .ToListAsync();
}

https://ravendb.net/docs/article-page/4.2/Csharp/client-api/session/querying/how-to-project-query-results#projectinto

Related