MongoDB C# Aggregation with LINQ

Question

I have a mongo object with these fields:

DateTime TimeStamp;
float    Value;

How can I get the aggregation pipeline, in C#, with LINQ, to get the minimum, maximum and average of Value over a specific timestamp range?

I have seen a few aggregation examples, but I don't quite get it. Having an example on a simple case like this would certainly (hopefully) make me understand it.

mickl · Accepted Answer · 2018-08-29 05:22:34Z

8

You can use LINQ syntax which gets translated into Aggregation Framework's syntax. Assuming you have following Model class:

public class Model
{
    public DateTime Timestamp { get; set; }
    public float Value { get; set; }
}

you can use where to specify timestamp range and then use group with null as grouping key. MongoDB driver will translate Min, Max and Average from anonymous type into $max, $min and $avg from Aggregation Framework syntax

var q = from doc in Col.AsQueryable()
        where doc.Timestamp > DateTime.Now.AddDays(-3)
        where doc.Timestamp < DateTime.Now.AddDays(3)
        group doc by (Model)null into gr
        select new
        {
            Avg = (double)gr.Average(x => x.Value),
            Min = gr.Min(x => x.Value),
            Max = gr.Max(x => x.Value)
        };

var result = q.First();

List of accumulators supported by MongoDB driver can be found here.

EDIT: the (Model)null is required because the query has to be transformed to $group with _id set to null (docs) since you want to get one result with aggregates. Casting is required just for C# compiler purpose as doc is of type Model.

edited Aug 29, 2018 at 5:22

answered Aug 28, 2018 at 21:02

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thomas Over a year ago

can you explain the '(Model) null' part? I don't understand the null keyword there

Thomas Over a year ago

an additional question: is is possible to do something, below the where, like: let Value = doc.Value and then group Value in order to not carry around the rest of the object?

mickl Over a year ago

@Thomas explained the null part in my edited answer. You can read more here (mongodb.github.io/mongo-csharp-driver/2.7/reference/driver/crud/…) about which LINQ operators are supported

Neil · Accepted Answer · 2018-08-28 20:28:36Z

The aggregation for this is done in two steps:

$match - Retrieve documents with TimeStamp value between some defined minDate and maxDate.
$group - Group on null. This will put all documents in a single group so we can apply an accumulator function across everything from the step 1 $match. The accumulator functions you're looking for are $min, $max, and $avg.

IMongoCollection<Entity> collection = GetMyCollection();

DateTime minDate = default(DateTime); // define this yourself
DateTime maxDate = default(DateTime); // define this yourself

var match = new BsonDocument
{ {
    "$match", new BsonDocument
    { {
        "TimeStamp", new BsonDocument
        { {
            "$and", new BsonDocument
            {
                { "$gt", minDate },
                { "$lt", maxDate }
            }
        } }
    } }
} };

var group = new BsonDocument
{ {
    "$group", new BsonDocument
    {
        { "_id", BsonNull.Value },
        { "min", new BsonDocument { { "$min", "Value" } } },
        { "max", new BsonDocument { { "$max", "Value" } } },
        { "avg", new BsonDocument { { "$avg", "Value" } } },
    }
} };

var result = collection.Aggregate(PipelineDefinition<Entity, BsonDocument>.Create(match, group)).Single();

double min = result["min"].AsDouble;
double max = result["max"].AsDouble;
double avg = result["avg"].AsDouble;

Collectives™ on Stack Overflow

MongoDB C# Aggregation with LINQ

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related