MongoDB.Driver.Builders how to group and get average

Question

We are using the C# MongoDB driver, and we would like to group on the Date part of a timestamp and get the average for that date. The problem is that we can't find the correct synthax for the group using the builders.

This code shows how the group is made with BSON documents, but we find that synthax not clear to read and very confusing ! So were are looking for the correct builder synthax.

We would like to use the Builders because it is more typed in C# then using the method with BsonDocuments in a pipeline. Here is a code snippet, where the first 3 operations work, but we can't find out GroupBy.

        DateTime from = new DateTime(2014, 12, 2);
        DateTime to = new DateTime(2014, 12, 4);
        var id = "37d163c0-44cc-4907-94cf-1e26b5eec911";

        var grp = new BsonDocument
        {
            {
                //Sort the documents into groups
                "$group",
                new BsonDocument
                {
                    //Make the unique identifier for the group a BSON element consisting
                    // of a field named Car.
                    // Set its value to that of the Cars field
                    // The Cars field is nolonger an array because it has now been unwound
                    //{ "_id", new BsonDocument { { "Date", "$Date" } }  },
                    {
                        "_id",new BsonDocument{ new BsonDocument("year",new BsonDocument ("$year","$Date")),
                                new BsonDocument("month",new BsonDocument ("$month","$Date")),
                                new BsonDocument("day",new BsonDocument ("$dayOfMonth","$Date"))
                                }
                    },
                    {
                        //Add a field named Owners
                        "avgAmount",
                        new BsonDocument
                        {
                            { "$avg" ,"$Value"}
                       }
                   }
                }
            }
        };

        AggregateArgs aggregateArgs = new AggregateArgs()
        {
            Pipeline = new[]
        {
            new BsonDocument("$match", Query<Reading>.EQ(c => c.SensorId, id).ToBsonDocument())
            , new BsonDocument("$match", Query<Reading>.LTE(c => c.Date, to).ToBsonDocument())
            , new BsonDocument("$match", Query<Reading>.GTE(c => c.Date, from).ToBsonDocument())
            , grp
            //, new BsonDocument("$group",GroupBy<Reading>.Keys(c=> c.Date).ToBsonDocument())


        }
        };
        IEnumerable<BsonDocument> documents = collection.Aggregate(aggregateArgs);

All help is appreciated, we already looked in simular questions on the forum, but can't find a correct working solution, question 1 or question 2.

Answer 1

with the new MongoDB .NET driver (2.0 - http://docs.mongodb.org/ecosystem/drivers/csharp/) , Linq support is fully suported, here is the synthax of the question usign the new driver. Much more readable .NET code then before using the BsonDocument synthax.

    public async Task<List<DailyStat>> GetLast31DaysReport(string id)
    {
        var mc = new MongoClient(_url);
        var db = mc.GetDatabase(DbName);
        var collection = db.GetCollection<Reading>(CollectionName);

        DateTime from = DateTime.Now.AddDays(-31);
        DateTime to = DateTime.Now;

        var output = await collection.Aggregate()
            .Match(r => r.SensorId == id)
            .Match(r => r.Date <= to)
            .Match(r => r.Date >= to.AddDays(-31))
            .Group(r => new { groupedYear = r.Date.Year, groupedMonth = r.Date.Month, groupedDay = r.Date.Day }, g =>
                new {
                    Key = g.Key,
                    avgValue = g.Average(x => x.Value),
                    minValue = g.Min(x => x.Value),
                    maxValue = g.Max(x => x.Value)
                })
            .Project(r => new DailyStat()
                {
                    Day = r.Key.groupedDay,
                    Month = r.Key.groupedMonth,
                    Year = r.Key.groupedYear,
                    Value = r.avgValue,
                    MinValue = r.minValue,
                    MaxValue = r.maxValue
                })
            .ToListAsync().ConfigureAwait(false);

        var returnList = new List<DailyStat>();
        while (returnList.Count < 31)
        {
            var value = output.FirstOrDefault(rec => rec.Day == from.Day && rec.Month == from.Month && rec.Year == from.Year);
            returnList.Add(value ?? new DailyStat() { Month = from.Month, Year = from.Year, Day = from.Day, Value = 0, MaxValue = 0, MinValue = 0 });
            from = from.AddDays(1);
        }
        return returnList;
    }