Unable to filter on Date fields in MongoDB using C# Driver

Question

I am manually serializing a number of POCOs using Newtonsoft JsonTextWriter and saving the result as a MongoDB BsonDocument.

//
// POCO to store in MongoDB
public class Session
{

    public DateTime? StartUTCTimestamp { get; set; }

    public DateTime? StartTimestamp { get; set; }

    public DateTime? EndTimestamp { get; set; }

    public void ToJSON(ref JsonTextWriter writer)
    {
        Session session = this;            

        writer.WriteStartObject(); // {

        writer.WritePropertyName("StartUTCTimestamp");
        writer.WriteValue(session.StartUTCTimestamp);

        writer.WritePropertyName("StartTimestamp");
        writer.WriteValue(session.StartTimestamp);

        writer.WritePropertyName("EndTimestamp");
        writer.WriteValue(session.EndTimestamp);


        writer.WriteEndObject(); // }
    }
}

A method in a test application which is used to import the data, retrieves all the Session objects from a SQL Server database (using Telerik's Open Access ORM) storing the results in an List. I serialize each Session by calling the ToJSON() method defined on the POCO (see above), passing in a reference to a JsonTextWriter. The resulting JSON string is then deserialized, using the C# MongoDB driver, into a BsonDocument which is then saved to Mongo. (The example below is from an ASP.NET Web Forms page, hence the alert box user controls).

private void LoadData()
{
    DateTime startDate = new DateTime(2015,12,31,23,59,59);
    var collection = _database.GetCollection<BsonDocument>("sessions");

    using (DbContext ctx = new DbContext())
    {
        List<Session> sessions = ctx.Sessions.Where().ToList();

        foreach (Session item in sessions)
        {
            JsonTextWriter writer = null;
            try
            {
                StringWriter sw = new StringWriter();
                writer = new JsonTextWriter(sw);
                writer.CloseOutput = true;
                item.ToJSON(ref writer);
                String json = sw.ToString();

                BsonDocument doc = MongoDB.Bson.Serialization.BsonSerializer.Deserialize<BsonDocument>(json);

                collection.InsertOne(doc);
                this.ucAlertMsg.Show("bg-info", "Completed without exception");
            }
            catch (Exception ex)
            {
                while (ex.InnerException != null) { ex = ex.InnerException; }
                this.ucAlertMsg.Show("bg-danger", ex.Message);
            }
            finally
            {
                writer.Close();
            }
        }
    }
}

This is saving the document just fine, however, I am unable to effectively query the documents, filtering on a date range. I believe, based on several other posts and articles that I have read, that this may be because the value is stored as a string instead of an "ISODate()".

// 
// This is what IS saved
{
    "_id" : ObjectId("5729128cd9017a248cbe6284"),
    "StartUTCTimestamp" : "2015-12-15T23:24:06",
    "StartTimestamp" : "2015-12-15T18:24:06",
    "EndTimestamp" : "2015-12-15T18:26:59",
}

//
// Is this what I need? 
{
    "_id" : ObjectId("5729128cd9017a248cbe6284"),
    "StartUTCTimestamp" : ISODate("2015-12-15T23:24:06"),
    "StartTimestamp" :  ISODate("2015-12-15T18:24:06"),
    "EndTimestamp" :  ISODate("2015-12-15T18:26:59"),
}

In my LoadData() method I have tried a number of configurations on the TextWriter which, according to some of the articles I have read seems like it should have helped ...

StringWriter sw             = new StringWriter();
writer                      = new JsonTextWriter(sw);
writer.DateFormatHandling   = DateFormatHandling.IsoDateFormat;
writer.CloseOutput          = true;

Assigning the "IsoDateFormat" to the "DateFormatHanding" setting on the writer produced no difference. I also tried "MicrosoftDateFormat" and the data was still stored as a string but the format was different.

The actual question

So that's all the set up ... the question is "how do I search MongoDB documents based on date"?

Using the C# driver for MongoDB allows me to search using Linq. Here's the linq query I am using.

IMongoCollection<Session> collection = database.GetCollection<Session>("sessions");
DateTime startDate  = (this.StartDate.HasValue) ? this.StartDate.Value : DateTime.Now.AddDays(-7);
DateTime endDate    = (this.EndDate.HasValue) ? this.EndDate.Value : DateTime.Now;
var data            = collection.Find<Session>(e => e.StartTimestamp.Value >= startDate && e.StartTimestamp.Value <= endDate).ToList();

Since the JSON maps directly back to a Session POCO I should be able to use that type (?). I can successfully filter on other fields in the Session POCO. It's just the dates that are giving me fits.

I am guessing that there is either something amiss or an oversight in my implementation or that since the data is being stored as a string it's not able to be compared as a Date(?).

Any insight would be greatly appreciated.

Thanks, -G

score 4 · Answer 1 · answered Jul 07 '16 at 21:39

The date in your filter expression must be created using BsonValue.Create(yourDate);

Example:

   IMongoDatabase db = GetMongoDbConnection();
   IMongoCollection<BsonDocument> collection = db.GetCollection<BsonDocument> ("yourCollectionName");
   DateTime date = BsonValue.Create(DateTime.Now.Date);
   var filter = Builders<BsonDocument>.Filter.Gt("dateFieldToFilterOn", date);
   List<BsonDocument> bsonDocuments = await collection.Find(filter).ToListAsync();

Gary O. Stenstrom · Accepted Answer · 2016-05-05T13:20:43.257

Not many people have seen this post so perhaps I have tagged it poorly, but for the one person who upvoted it I did come accross what I regard more as a "work around" than an actual solution but perhaps it will help you as well.

Instead of, or in addition to saving dates in the typical String format ("yyy/MM/dd HH:mm:ss"), I saved the dates as Int64 ticks. The parser/writer will recognize these as numbers and store them as such. And numbers are, of course, easily sorted and ordered.

So the Session model from the original post now looks like this ...

//
// POCO to store in MongoDB
public class Session
{

    public DateTime? StartUTCTimestamp { get; set; }

    public DateTime? StartTimestamp { get; set; }

    public DateTime? EndTimestamp { get; set; }


    //
    // If the StartUTCDate is defined then return the number of "ticks" in the date
    [BsonElement("StartUTCTimestampTicks")]
    public Int64? StartUTCTimestampTicks 
    { 
        get 
        { 
            return (this.StartUTCTimestamp.HasValue) ?
                    (Int64?)this.StartUTCTimestamp.Value.Ticks : 
                    null; 
        } 
    }

    //
    // If the StartDate is defined then return the number of "ticks" in the date
    [BsonElement("StartTimestampTicks")]
    public Int64? StartTimestampTicks
    { 
        get 
        { 
            return (this.StartTimestamp.HasValue) ?
                    (Int64?)this.StartTimestamp.Value.Ticks : 
                    null; 
        } 
    }

    //
    // If the EndDate is defined then return the number of "ticks" in the date
    [BsonElement("EndTimestampTicks")]
    public Int64? EndTimestampTicks
    { 
        get 
        { 
            return (this.EndTimestamp.HasValue) ?
                    (Int64?)this.EndTimestamp.Value.Ticks : 
                    null; 
        } 
    }

    public void ToJSON(ref JsonTextWriter writer)
    {
        Session session = this;            

        writer.WriteStartObject(); // {


        if (session.StartUTCTimestamp.HasValue)
        {
            writer.WritePropertyName("StartUTCTimestamp");
            writer.WriteValue(session.StartUTCTimestamp);

            writer.WritePropertyName("StartUTCTimestampTicks");
            writer.WriteValue(session.StartUTCTimestampTicks);
        }

        if (session.StartTimestamp.HasValue)
        {
            writer.WritePropertyName("StartTimestamp");
            writer.WriteValue(session.StartTimestamp);

            writer.WritePropertyName("StartTimestampTicks");
            writer.WriteValue(session.StartTimestampTicks);
        }

        if (session.EndTimestamp.HasValue)
        {
            writer.WritePropertyName("EndTimestamp");
            writer.WriteValue(session.EndTimestamp);

            writer.WritePropertyName("EndTimestampTicks");
            writer.WriteValue(session.EndTimestampTicks);
        }


        writer.WriteEndObject(); // }
    }
}

I've added properties exposing the number of ticks in each date, if defined. If not they simply return null. I did have to decorate them with the BsonElement attribute to make them recognizable to the JsonTextWriter (Not sure why these needed it and the other properties did not), otherwise I received an error.

I also modified the ToJSON() method on the model to check that the dates were provided before serializing the values. If the date is not defined then we just don't add the json element to the document.

The resulting document stored in MongoDB now looks like ...

{
    "_id" : ObjectId("572b4486d9016b151846b8ed"),
    "StartUTCTimestamp" : "2016-04-24T17:02:12",
    "StartUTCTimestampTicks" : NumberLong(635971141320000000),
    "StartTimestamp" : "2016-04-24T13:02:12",
    "StartTimestampTicks" : NumberLong(635970997320000000),
    "EndTimestamp" : "2016-04-24T13:05:16",
    "EndTimestampTicks" : NumberLong(635970999160000000)
}

Why these values are being stored with a "NumberLong()" datatype function and the dates are not stored with the "ISODate()" function is a mystery to me, but it now allows me to query dates and date ranges based on Ticks rather than date strings.

IMongoCollection<Session> collection = database.GetCollection<Session>("sessions");
DateTime startDate  = (this.StartDate.HasValue) ? this.StartDate.Value : DateTime.Now.AddDays(-7);
DateTime endDate    = (this.EndDate.HasValue) ? this.EndDate.Value : DateTime.Now;
var data            = collection.Find<Session>(e => e.StartTimestampTicks.Value >= startDate.Ticks && e.StartTimestampTicks.Value <= endDate.Ticks).ToList();

This actually has me considering NOT saving the date strings at all. I am preserving them more as labels because they are more readable when viewing the actual JsonDocument. But, as long as the primary use of the document is to be viewed through a user interface, it's a trivial matter to convert Ticks to more readable date string representations for purposes of viewing.

Anyway ... I hope this help ... someone ... :)

Unable to filter on Date fields in MongoDB using C# Driver

2 Answers2