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.