Properly using / handling DateTimeOffset in MongoDB

Question

public class ScheduledEvent : Event
{
    public DateTimeOffset StartDateTime { get; set; }
}

StartDateTime = 5/27/2013 2:09:00 AM +00:00 representing 05/26/2013 07:09 PM PST

What's recorded in MongoDB:

db.ScheduledEvent.find().toArray()[
    {
        "_id": BinData(3, "ZE2p31dh00qb6kglsgHgAA=="),
        "Title": "Today 26th at 7:09pm",
        "Length": "00:00:00",
        "MoreInformation": "http://1.com",
        "Speakers": [{
            "_id": BinData(3, "ndzESsQGukmYGmMgKK0EqQ=="),
            "Name": "Mathias Brandewinder"
            }
        ],
        "Location": {
            "_id": BinData(3, "AAAAAAAAAAAAAAAAAAAAAA=="),
            "Name": "Somwhere "
        },
        "Organizers": [{
            "_id": BinData(3, "AAAAAAAAAAAAAAAAAAAAAA=="),
            "Name": null
        }],
        "CreatedOn": [
            NumberLong("635052144104050898"),
            0
        ],
        "StartDateTime": [
            NumberLong("635052173400000000"),
            0
        ]
    }
]

---

I realize that StartDateTime is stored as Ticks in MongoDB.

var dateMarker = DateTimeOffset.UtcNow;
var nextDay = dateMarker.AddDays(1);

This query does not work:

var today = EventRepoistory
    .All()
    .Where(z => z.StartDateTime >= dateMarker 
        && z.StartDateTime < nextDay)
    .OrderByDescending(z => z.StartDateTime)
    .ToList();

I have added a query display to the Mongo C# driver which shows the following query:

{ "$query" : { "StartDateTime" : { "$gte" : [NumberLong("635052168609734070"), 0], "$lt" : [NumberLong("635053032609734070"), 0] } }, "$orderby" : { "StartDateTime" : -1 } }

Lower bound = 6350521 68609734070

Server = 6350521 73400000000

Upper bound = 6350530 32609734070

Question: Why this MongoDB query doesn't return anything?

db.ScheduledEvent.find({
  "$query": {
    "StartDateTime": {
      "$gte": [NumberLong("635052168609734070"), 0],
      "$lt": [NumberLong("635053032609734070"), 0]
    }
  },
  "$orderby": {
    "StartDateTime": -1
  }
})

I have researched about this topic and I found this solution : MongoDB and DateTimeOffset type

But it seams that LINQ provider is doing what it's supposed to?

---

I have also tried code:

db.ScheduledEvent.find({
  "StartDateTime": {
    "$gte": [NumberLong("1"), 0]
  }
})

But it doesn't return any result.

Answer 1

As other answers state, the root cause of the problem is that DateTimeOffset fields are serialized as arrays (with ticks and offset) by default.

The answer proposed by dsandor works fine, if you are able to localize your changes in DAL layer and use queries specific to MongoDB.

However, such approach does not work well if your repository exposes IQueryable<ScheduledEvent> and some LINQ queries are applied in service layer.

The more generic approach is to change the way how DateTimeOffset fields are serialized to MongoDB, so that LINQ queries function correctly, without necessity for customization of MongoDB queries.

You can easily change the serialization of DateTimeOffset fields with BsonRepresentation(BsonType.String) attribute:

public class ScheduledEvent : Event
{
    [BsonRepresentation(BsonType.String)]
    public DateTimeOffset StartDateTime { get; set; }
}

In this case DateTimeOffset fields are saved to MongoDB in string form 2020-04-03T08:12:23+03:00 and LINQ queries work correctly.

Answer 2

A similar answer is found here: MongoDB and DateTimeOffset type (as you note in your question)

I got this working with the C# Driver by doing the following:

var query = Query.GT("StartDateTime.0", startDate.Ticks);
var json = query.ToJson();

Produces this JSON:

{ "StartDateTime.0" : { "$gt" : NumberLong("635251617859913739") } }

The above JSON works. Per the linked answer the reason is that DateTimeOffset is an array.

When I use LINQ I get (as you noted) a different result for the JSON.

var query = from r in col.AsQueryable<MyObjectType>()
    where r.StartDateTime>= startDate && r.StartDateTime< endDate
    select r;

The LINQ query above produces the following JSON:

{ "StartDateTime" : { "$gte" : [NumberLong("635251617859913739"), 0], "$lt" : [NumberLong("635251635859913739"), 0] } }

I am not sure if the LINQ provider in the C# driver needs to be fixed to handle DateTimeOffset but using the Query builder to specify the first element of the DateTimeOffset array (StartDateTime.0) was the only way I got this working.