Properly using / handling DateTimeOffset in MongoDB

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.

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.