Sort on function applied to each item in array element in MongoDB

Question

I have an object that contains a field with a list of measures for one of the fields:

public class TestObject {

    [BsonElement("graph")]
    public List<Measurement> Graph { get; set; }
}

public class Measurement {

    [BsonRequired]
    [BsonElement("magnitude")]
    public uint Magnitude { get; set; }

    [BsonRequired]
    [BsonElement("measured_on")]
    public DateTime MeasuredOn { get; set; }
}

I'm trying to write a find operation for MongoDB that would retrieve the first N TestObject entries ordered by the sum of the M most recent Magnitude attributes on the Graph attribute. My current attempt looks like this:

var findOptions = new FindOptions<TestObject> {
    Limit = N,
    Sort = new SortDefinitionBuilder<TestObject>()
               .Descending(p => p.Graph.OrderByDescending(r => r.MeasuredOn)
               .Take(M)
               .Sum(r => r.Magnitude)),
    Projection = new ProjectionDefinitionBuilder<TestObject>()
                     .Slice(p => p.Graph, 0, N),
};

IMongoCollection<TestObject> collection = GetCollection();
var cursor = await collection.FindAsync(FilterDefinition<TestObject>.Empty,
                                       findOptions, 
                                       token)
                             .ConfigureAwait(false);
return await cursor.ToListAsync(token).ConfigureAwait(false);

However, this is currently throwing a NotSupportedException:

System.NotSupportedException: 
The method OrderByDescending is not supported in the expression tree: 
{document}{graph}.OrderByDescending(r => r.MeasuredOn).

From this I can understand that my attempt to mix LINQ expressions with MongoDB will not work, but I can't figure out how to make this query work. Any help would be appreciated.

Answer 1

don't think it's possible to achieve with the Find interface. however, the Aggregation interface can do it like so:

let docLimit = 1;
let graphLimit = 2;

db.collection.aggregate(
    [
        {
            $project: {
                _id: 1,
                graph: 1
            }
        },
        {
            $unwind: "$graph"
        },
        {
            $sort: { "graph.measured_on": -1 }
        },
        {
            $group: {
                _id: "$_id",
                graph: { $push: "$graph" },
            }
        },
        {
            $set: { graph: { $slice: ["$graph", graphLimit] } }
        },
        {
            $set: { graphSum: { $sum: "$graph.magnitude" } }
        },
        {
            $sort: { graphSum: -1 }
        },
        {
            $limit: docLimit
        },
        {
            $unset: "graphSum"
        }
    ])

also don't think you can translate the above aggregation query to a strongly-typed c# query either. you'll just have to pass a json string to the driver.