[CSHARP-2108] Using $subtract for dates in Linq expression (aggregation projection) Created: 22/Nov/17  Updated: 28/Oct/23  Resolved: 04/Jul/22

Status: Closed
Project: C# Driver
Component/s: Linq
Affects Version/s: 2.4.4
Fix Version/s: 2.17.0

Type: Task Priority: Major - P3
Reporter: Florian Poulin Assignee: Robert Stam
Resolution: Fixed Votes: 0
Labels: question, rfw
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Backwards Compatibility: Fully Compatible

 Description   

The $subtract operator for projections in an aggregation pipeline (cf. unit tests) does not support DateTime types properly.

For instance, I want to calculate the number of days elapsed between now and some date coming from a previous stage of my pipeline.

To do that I would:

  • Subtract that date from the current date (which returns the delta in milliseconds, cf docs)
  • Divide the result by the number of milliseconds in a day
  • Floor the whole thing to get a nice int-ish number

Something like this:

{
  "$project": {
    "nbDays": {
      "$floor": {
        "$divide": [
          {
            "$subtract": [
              "new ISODate()",
              "$dateFieldFromAbove"
            ]
          },
          "1000 * 60 * 60 * 24"
        ]
      }
    }
  }
}

I would expect to be able to write this like this in Linq (which compiles nicely and makes sense):

Project(x => new
{
    nbDays = Math.Floor(
      (DateTime.UtcNow - x.dateFieldFromAbove).TotalMilliseconds / 1000 * 60 * 60 * 24
    )
})

But in C# the difference between two DateTime objects produces a TimeSpan, from which we need to take the TotalMilliseconds for the division to work ....

This property is not supported by the translator.

Member TotalMilliseconds of type System.TimeSpan in the expression tree (22/11/2017 10:50:32 - {document}{dateFieldFromAbove}).TotalMilliseconds cannot be translated.

Is there anything I am missing here? Is there a workaround to deal with DateTime subtractions other than switching back to the painful BsonDocument pipeline? Any plan to support this out of the box in future version of the driver?



 Comments   
Comment by Githook User [ 04/Jul/22 ]

Author:

{'name': 'Robert Stam', 'email': 'robert@robertstam.org', 'username': 'rstam'}

Message: CSHARP-2108: Subtracting one date from another in LINQ. (#837)
Branch: master
https://github.com/mongodb/mongo-csharp-driver/commit/ab539208309c6ee9354879df7f60e502a1598dba

Comment by Robert Stam [ 01/Jul/22 ]

Your approach of subtracting one date from another resulting in a TimeSpan which you then convert to days is valid in C#, but is not currently supported by our LINQ translator.

However, MongoDB has the $dateDiff operator to subtract two dates with the results in the units you select. We have custom extension methods on DateTime to enable using this in LINQ.

For example:

var endDate = DateTime.Parse("2020-01-03Z", null, DateTimeStyles.AdjustToUniversal);
 
var queryable = collection.AsQueryable()
    .Select(x => new { Days = endDate.Subtract(x.StartDate, DateTimeUnit.Day) }); // uses our custom Subtract extension method

is translated as:

{ $project : { Days : { $dateDiff : { startDate : '$StartDate', endDate : ISODate('2020-01-03T00:00:00Z'), unit : 'day' } }, _id : 0 } } 

Note: due to a bug in our implementation of this custom extension method this won't work reliably until the next release of the driver which is 2.17.0.

Comment by Florian Poulin [ 14/Feb/19 ]

Hi Wan,

Thanks for your answer, better late than never .

Actually I figured that by encapsulating the dirty untyped BsonDocument projection in a class in conjunction with the "As" operator we can make this workaround pretty sweet...

Here is the containing class (which essentially keeps the un-typed part of the pipeline encapsulated):

 

class ElapsedTimeProjection
{
    [BsonElement("someId")]
    [BsonRepresentation(BsonType.ObjectId)]
    public string SomeId { get; set; }
 
    [BsonElement("elapsedTime")]
    public double ElapsedTime { get; set; }
 
    public static BsonDocument BuildProjection()
    {
        return new BsonDocument
        {
            { "someId", "$_id" },
            { "elapsedTime", new BsonDocument {
                { "$floor", new BsonDocument {
                    { "$divide", new BsonArray {
                        new BsonDocument { { "$subtract", new BsonArray { DateTime.UtcNow, "$dateFieldFromAbove" } } },
                        (1000 * 60 * 60 * 24)
                    } }
                } }
            } }
        };
    }
}

And now I can use it like that (notice that after the "As" stage I am back to typed mode ... pretty neat):

var result = SomeCollection.Aggregate()
    .Match(...)
    .Project(x => ElapsedTimeProjection.BuildProjection()
    .As<ElapsedTimeProjection>()
    .Group(
        x => x.ElapsedTime, // <---- back to typed
        g => new { timeUnits = g.Key, count = g.Count() })
    .ToList();

Cheers,

Florian

 

 

Comment by Wan Bachtiar [ 12/Feb/19 ]

Is there a workaround to deal with DateTime subtractions other than switching back to the painful BsonDocument pipeline?

Hi Florian,

Thanks for reporting this issue and providing the relevant information. I can see that as of currently (v2.7.x) the MongoDB .NET/C# driver DateTime subtraction returns TimeSpan, which the conversion to other formats are not currently supported in LINQ.

Unfortunately, in the meantime the workaround is to go through BsonDocument pipeline as below:

var agg = collection.Aggregate()
            .Project(new BsonDocument{
                    {"nbDays", new BsonDocument{
                        {"$floor", new BsonDocument{
                            {"$divide", new BsonArray{
                                new BsonDocument{
                                        {"$subtract", 
                                            new BsonArray{DateTime.UtcNow, "$dateFieldFromAbove"}
                                        }
                                    },
                                (1000 * 60 * 60 * 24)
                            }}
                        }}
                    }}}
            );

Regards,
Wan.

Generated at Wed Feb 07 21:41:38 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.