c# - How to use 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

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.