Group by $week in c# Library

[Fire]

How can we group by week number ($week) in c# library linq or Fluent Builder? Upon checking your documentation here: Expressions there are no any example for using $week in c#.

Please help we really want to use the $week for our group by.

[James_Kovacs]

Hi, @Fire,

Welcome to the MongoDB Community Forums. I understand that you are trying to group by the week of a DateTime. Unfortunately .NET does not include a Week property on DateTime. You can however specify it as JSON:

ProjectionDefinition<Document> projection = "{Week: {$week: '$CreatedAtUtc'}}";
var aggregation = coll.Aggregate().Project(projection);
Console.WriteLine(aggregation);

Output:

aggregate([{ "$project" : { "Week" : { "$week" : "$CreatedAtUtc" } } }])

While this allows you to use the $week operator, it lacks strong typing support. So use this technique with caution.

Note that you cannot use $week in a grouping, only in a projection.

CORRECTION: You can use $week in a grouping as [{ $group : { _id : { $week : "$d" }, x : { $sum : "$x" } } }]. When I first tried it, I had a syntax error in the MQL, which caused the server to report that it wasn’t supported.

You can however use $dateTrunc which was introduced in MongoDB 5.0. (Note that support for Truncate was added in LINQ3, which you must opt into. See LINQ3 for more information.)

var linqQuery = coll.AsQueryable()
    .GroupBy(x => x.CreatedAtUtc.Truncate(DateTimeUnit.Week))
    .Select(x => new { Bucket = x.Key, FirstDocumentInBucket = x.First() });
Console.WriteLine(linqQuery);

Output:

test.groupings.Aggregate([{ "$group" : { "_id" : { "$dateTrunc" : { "date" : "$CreatedAtUtc", "unit" : "week" } }, "__agg0" : { "$first" : "$$ROOT" } } }, { "$project" : { "Bucket" : "$_id", "FirstDocumentInBucket" : "$__agg0", "_id" : 0 } }])

Hopefully this provides you some options and ideas.

Sincerely,
James

[Fire]

@James_Kovacs

var linqQuery = coll.AsQueryable()
    .GroupBy(x => x.CreatedAtUtc.Truncate(DateTimeUnit.Week))
    .Select(x => new { Bucket = x.Key, FirstDocumentInBucket = x.First() });
Console.WriteLine(linqQuery);

Wow thank you four response.

We are more interested in the LINQ3 solutions, we are using MongoDB 6.0 and latest library c# (2.17). How can we achieve this?

var linqQuery = coll.AsQueryable()
.GroupBy(x => x.CreatedAtUtc.Truncate(DateTimeUnit.Week))
.Select(x => new { Bucket = x.Key, FirstDocumentInBucket = x.First() });
Console.WriteLine(linqQuery);

When we tried this one, it is causing some error that this is not supported.

Lastly, we would like to request to mongoDB .net driver Team to create a natively $week operator in group by c#, perharps using your own syntax library without depending on the c# Week property. I dont think c# will add the Week property in the DateTime class.

[Sudhesh_Gnanasekaran]

@Fire I hope you missed the settings part to support the Linq3 version of the C# MongoDB driver.

clientSettings.LinqProvider = LinqProvider.V3;

Refer to the below link for more info

https://mongodb.github.io/mongo-csharp-driver/2.14/reference/driver/crud/linq3/

[James_Kovacs]

@Fire

Lastly, we would like to request to mongoDB .net driver Team to create a natively $week operator in group by c#, perharps using your own syntax library without depending on the c# Week property. I dont think c# will add the Week property in the DateTime class.

Please file a feature request in our issue tracker and we will be happy to consider it.

Sincerely,
James

[Fire]

Thanks! Saw it!

We will post a feature request regarding the $week group by in .net driver. Here is our feature request:
https://jira.mongodb.org/browse/CSHARP-4405

We really hope that this could be implemented