MongoDB with C# Driver - date format with sort

Question

I need to convert ISODate to string format like "2019-06-27" this, and also need to apply a sort on it by date. However, I've converted date in required format but messing up with date sort due to date string format which is earlier converted.

App Environment

1. .NET MongoDB Driver - 2.0
2. MongoDB version - 3.2

This is how the document stored in the MongoDB collection:

{ 
  "_id": "9d78d0e8-7b13-4487-88a3-d91d64f29b38",
  "Number": "001",
  .......,
  .......,
  "createdon": {
        "$date": "2019-09-19T00:00:00.000Z"
    },
  "modifiedon": {
        "$date": "2019-12-17T19:52:00.000Z"
    }
}

And this is the working C# function but without date sort:

public string GetData(Data.DataModel.TestModel model)
    {
        string collectionName = "testCollection";
        BsonDocument sort = new BsonDocument();
        BsonDocument match = new BsonDocument();
        BsonDocument project = new BsonDocument();

        int skip = model.skip;
        int limit = model.limit;

        try
        {                
            match.Add();

            project.AddRange(new BsonDocument { 

                                { "TDLNumber", 1 },
                                { "createdon", new BsonDocument("$dateToString", 
                                    new BsonDocument("format", "%Y-%m-%d").Add("date", "$createdon")) // format like 2019-06-27
                                },
                                { "modifiedon", new BsonDocument("$dateToString", 
                                new BsonDocument("format", "%Y-%m-%d").Add("date", "$modifiedon"))// format like 2019-06-27
                                }
                    });

            sort.AddRange(new BsonDocument { { "createdon", -1 }, { "modifiedon", -1 } });

            List<BsonDocument> lstReslut = dbo.FindAggDynQuery(collectionName, match, project, skip, limit, sort);
            QueryResult = lstReslut.ToJson();
        }
        catch (Exception)
        {
            QueryResult = "[]";
        }
        return QueryResult;
    }

But if I do without date convert it works perfectly like this

 { "createdon", 1},
 { "modifiedon",1},
// { "createdon", new BsonDocument("$dateToString", 
//      new BsonDocument("format", "%Y-%m-%d").Add("date", "$createdon")) // format like 2019-06-27
// },
//{ "modifiedon", new BsonDocument("$dateToString", 
//      new BsonDocument("format", "%Y-%m-%d").Add("date", "$modifiedon"))// format like 2019-06-27
//}

Here's databae layer query function:

public List<BsonDocument> FindAggDynQuery(string collectionName, BsonDocument find, BsonDocument project, int skip, int limit, BsonDocument sort)
    {
        using (var connectionManager = new Test.Data.ConnectionManager())
        {
            var _collection = connectionManager.GetDBCollection(collectionName);
            var result = _collection.Find(find).Sort(sort).Project(project).Skip(skip).Limit(limit).ToListAsync().Result;
            return result;
        }
    }

What's going wrong here. Any help would be appreciate!!

Answer 1

After working on couple of hours, here's my optimized answer.

Changes in the GetData method using the Aggregate method:

match.Add();

project.AddRange(new BsonDocument { 

                            { "TDLNumber", 1 },
                            { "createdon", 1 },
                            { "modifiedon", new BsonDocument("$dateToString", 
                            new BsonDocument("format", "%Y-%m-%d").Add("date", "$modifiedon"))// format like 2019-06-27
                            }
                });

BsonDocument expAddfield = new BsonDocument(new BsonDocument("$addFields", new 
BsonDocument("createdon", new BsonDocument("$dateToString",
                                                                                                                        new BsonDocument
                                                                                                                        {
                                                                                                                            { "date", "$createdon" }, 
                                                                                                                            { "format", "%Y-%m-%d" }
                                                                                                                        }))));

sort.Add("createdon", -1);

List<BsonDocument> lstReslut= dbo.FindAggDynNoGroupWithSortSkipLimit(watertrackingCollection, expProject, match1, expAddfield, sort, skip, limit);

QueryResult = lstReslut.ToJson();

Aggregate Method

public List<BsonDocument> FindAggDynQuery(string collectionName, BsonDocument expProject, BsonDocument expMatch, BsonDocument expAddfield, BsonDocument expSort, int skip, int limit)
    {
        var connectionManager = new ez2Track.Data.ConnectionManager();
        var _collection = connectionManager.GetDBCollection(collectionName);
        var agg = _collection.Aggregate().Project(expProject).Match(expMatch).AppendStage<BsonDocument>(expAddfield).Sort(expSort).Skip(skip).Limit(limit);
        var result = agg.ToListAsync().Result;
        return result;
    }