How do I filter documents using the timestamp in ObjectId in C#?

Question

I am building an application that requires a transfer of data from MongoDB documents to an SQL Server table. I am creating a JSON file for exporting the MongoDB documents into it (the code for which has been attached herewith). How do I now add a filter such that only the documents created in the MongoDB collection after a specific data re exported to the JSON?

I believe this can be achieved by somehow using the timestamp in the ObjectId field of a MongoDB document, but could not find out how.

using (FileStream fs = File.Create(path))
{
    using (var fw = new StreamWriter(fs))
    {
        fw.Write("[");
            using (var cursor = await collection.Find(new BsonDocument()).Project(Builders<BsonDocument>.Projection.Exclude("_id")).ToCursorAsync())
            {
            while (await cursor.MoveNextAsync())

                foreach (var doc in cursor.Current)
                {
                    fw.Write(doc.ToString());
                    fw.Write(",");
                }
                fw.Flush();
        }
        fs.SetLength(fs.Length - 1);
        fw.Write("]");
    }
}

Have a look at [this](https://stackoverflow.com/questions/35122191/filter-by-date-using-an-idobject). If this is not what you're looking for is very alike. — dcg, Mar 27 '19 at 05:46

score 4 · Answer 1 · answered Mar 27 '19 at 15:51

I can't use your exact example but I have managed to create something similar that filters using the ObjectId datetime:

// Declare a date range - presumably these would be dynamic not fixed strings
var startDateTime = DateTime.Parse("2018-09-13 14:19:26.000Z");
var endDateTime = DateTime.Parse("2018-09-24 14:03:38.000Z");

// Use the dates to create ObjectId type for comparison
var startId = new ObjectId(startDateTime, 0, 0, 0);
var endId = new ObjectId(endDateTime, 0, 0, 0);

// Use the ObjectId types in the filter
using (var cursor = await collection.Find(x => x._id > startId && x._id < endId).ToCursorAsync())
{
    while (await cursor.MoveNextAsync())
    {
        foreach (var doc in cursor.Current)
        {
            // Use doc object
        }
     }
 }

Note: I used the latest MongoDB.Driver package

score 1 · Accepted Answer · answered Mar 29 '19 at 10:29

In the above code snippet, a JSON file is being built using StreamWriter, whereas, the purpose can be solved by using the mongoexport.exe process via a C# application code, which makes the filtering easier too:

public static string dateConverter(DateTime dt)
    {
        long decimalNumber = (long)(dt.Subtract(new DateTime(1970, 1, 1))).TotalSeconds;
        return (Convert.ToString(decimalNumber, 16));
    }
public static void Main(string[] args)
    {
        try
        {
            CultureInfo provider = CultureInfo.InvariantCulture;
            string instr;
            Console.WriteLine("Enter the start date");
            instr = Console.ReadLine();
            DateTime.TryParseExact(instr, "yyyy/MM/dd", provider, DateTimeStyles.None, out startDate);
            Console.WriteLine("Enter the end date");
            instr = Console.ReadLine();
            DateTime.TryParseExact(instr, "yyyy/MM/dd", provider, DateTimeStyles.None, out endDate);
            queryFilter = "{_id:{$gte: ObjectId('" + dateConverter(startDate) + "0000000000000000'), $lte: ObjectId('" + dateConverter(endDate) + "ffffffffffffffff')}}";
            string expstring = " --db yourDatabaseName --collection yourCollectionName --type json --query " + queryFilter + " --out yourFilePath --jsonArray";
            Process export = new Process();
            export.StartInfo.FileName = ExportEXEPath;
            export.StartInfo.Arguments = expstring;
            export.Start();
        }
        catch (Exception ex)
        {
            Console.WriteLine("[ERROR]: " + ex.Message);
        }
    }

Yet, there is an issue with the passing of argument into command-line using a string that contains double-quotes (") within itself (as has been done after the --query keyword), a discussion about which can be referred to by clicking here.

How do I filter documents using the timestamp in ObjectId in C#?

2 Answers2