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

.NET MongoDB Driver - 2.0
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!!

In your C# driver, you are sorting descendant, while before converting into string, you are sorting ascending. Is It correct? Other thing: why you don't Sort dates being Date where mongodb can use indexes and then with $project you convert them? — Valijon
– Valijon, Commented Jan 15, 2020 at 8:32
1. Sort only descending(both key) 2. Then convert date to a string, how? — Harshal Yelpale
– Harshal Yelpale, Commented Jan 15, 2020 at 8:45
You need to use aggregate. Take a look stackoverflow.com/a/36407426/3710490 — Valijon
– Valijon, Commented Jan 15, 2020 at 10:56

Harshal Yelpale · Accepted Answer · 2020-01-21 12:13:09Z

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;
    }

Collectives™ on Stack Overflow

MongoDB with C# Driver - date format with sort

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related