MongoDB C# 2.0 Driver Multiple Unwinds

Question

I'm trying out Mongo for the first time and am having an issue where I have:

public class A
{
    public int ID {get;set;}
    .
    .
    .
    public List<B> Bs { get; set; }
}

public class B
{
    public int ID { get; set; }
    .
    .
    .
    public List<C> Cs { get; set; }
}

public class C
{
    public string Name { get; set; }
    public double Amount { get; set; }
}

and I want get back the top 10 C's with the highest Summed Amounts when I group C by name. So for example, John Smith could be in several B's within a single A and could also be in B's among several different A's

I can accomplish this within in the Mongo Shell by running:

db.As.aggregate(  
  {$unwind: "$Bs"},
  {$unwind: "$Bs.Cs"},
  {$group: { _id: "$Bs.Cs.Name", total: {$sum: "$Bs.Cs.Amount"}}},
  {$sort: {total: -1}},
  {$limit: 10}
);

But I cant figure out how to do this within my C# app using the MongoDB 2.0 driver. Could somebody point me in the right direction?

Also, I'm a SQL Server guy and am very used to using sprocs, should I put this particular aggregate in a stored javascript on the server and just call it from my C# app? If so, how do you call stored javascript with the 2.0 driver?

Thanks!

Answer 1

Unfortunately not all MongoDB queries can be written with LINQ. Anyway you can implement it through aggregation:

var collection = database.GetCollection<A>("As");
var result = await collection
      .Aggregate()
      .Unwind(x => x.Bs)
      .Unwind(x => x["Bs.Cs"])
      .Group(new BsonDocument {{"_id", "$Bs.Cs.Name"}, {"total", new BsonDocument("$sum", "$Bs.Cs.Amount")}})
      .Sort(new BsonDocument("total", -1))
      .Limit(10)
      .ToListAsync();