MongoDB .NET Driver - Convert string to DateTime and for Filter Builder

Question

var builder = Builders<ModelClass>.Filter;
var filter = builder.Where(x => x.Active);

if (fromDate.HasValue)
{
    var date = fromDate.Value;
    var subfilter = builder.Where(x => DateTime.Parse(x.EnrollmentDate) >= date);
    filter &= subfilter;
}

Enrollment Date is saved as a string:

public string EnrollmentDate { get; set; }

, I need to filter docs within a set of date range, but how do I compare this? I need to filter like this.

I get

System.InvalidOperationException: Parse({document}{EnrollmentDate}) is not supported.

Error in subfilter line.

Is there any way you can migrate the documents to use a datetime field in Mongo instead? Otherwise, I think you might need an aggregation that uses [`$toDate`](https://docs.mongodb.com/manual/reference/operator/aggregation/toDate/) or [`$dateFromString`](https://docs.mongodb.com/manual/reference/operator/aggregation/dateFromString/#mongodb-expression-exp.-dateFromString), but that won't make for an efficient query. Or perhaps [using Javascript](https://stackoverflow.com/questions/29535822/mongodb-c-sharp-driver-where-with-javascript-function), though I don't think that will be much better. — ProgrammingLlama, Mar 10 '22 at 09:10
Hi my mongo db is hosted in a differnt location, When ever i store the date time it is saved with +5:30hrs Difference, To Avoid this i a using string format — Adarsh s, Mar 10 '22 at 09:20
You could look at storing your datetime values as UTC and then converting them appropriately to display on the UI / to do a query. — ProgrammingLlama, Mar 10 '22 at 09:45

Yong Shun · Answer 1 · 2022-03-10T10:25:24.207

I think you need to achieve with MongoDB query as below:

{
  "$expr": {
    "$gte": [
      { "$toDate": "$EnrollmentDate" },
      date
    ]
  }
}

While I think it is not achievable with MongoDB .Net Driver LINQ syntax, you convert the query as BsonDocument:

var subfilter = new BsonDocument("$expr",
    new BsonDocument("$gte", 
        new BsonArray {
            new BsonDocument("$toDate", "$EnrollmentDate"),
            date
        }
    )
);

filter &= subfilter;

Radovancev · Accepted Answer · 2022-03-10T09:24:13.190

1

You have problem here when you want to do DateTime.Parse()

Can you post format of your string EnrollmentDate? And your variable date , is it only Date or DateTime?

This one maybe can help you here

Also, try to use

var subfilter = builder.Gte(x=>x.Y, Z)

edited Mar 10 '22 at 09:24

answered Mar 10 '22 at 09:17

Radovancev

286
3
11

2021-12-20 is the format of EnrolmentDate, Usually DateTime.parse converts the above date to=> 20-12-2021 12.00.00 AM – Adarsh s Mar 10 '22 at 09:25
@Adarshs So, the format of EnrolmentDate are ok. No, after DateTime.Parse result will be 20/12/2021 00:00:00. What about your variable date? Is it DateTime? Also, you need to know that Mongo always saves the date in UTC time. You can use `[BsonDateTimeOptions(Kind = DateTimeKind.Local)]` in your model for fromDate DateTime – Radovancev Mar 10 '22 at 09:39
I Have come up with a work Around var date = fromDate.Value; date = date.Add(DateTime.MinValue.TimeOfDay); string dateasString = date.Year + "-" + (date.Month > 9 ? date.Month : "0" + date.Month)+"-"+(date.Day > 9 ? date.Day : "0" + date.Day); var regfilter = builder.Regex(x => x.EnrollmentDate, "/[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-9][0-9]/g"); filter &= regfilter; var subfilter = builder.Gte(x => x.EnrollmentDate, dateasString);//Where(x =>DateTime.Parse(x.EnrollmentDate) >= date);filter &= subfilter; – Adarsh s Mar 10 '22 at 10:00
1

Basically Since Date is saved as yyyy-mm-dd, i converted from date to yyyy-mm-dd format and used Gte filter on it also used a regex to skip any date that dont follow this format, Thank yu for your help and i helped a lot – Adarsh s Mar 10 '22 at 10:04

score 0 · Answer 3 · answered Apr 20 '23 at 10:04

0

I had the same error with DateTime parsing in MongoDB so I'd suggest you to refactor EnrollmentDate in your model to DateTime. MongoDB knows how to persist DateTime and how to Filter and Sort by it without having to change your subfilters.

answered Apr 20 '23 at 10:04

Guy_g23

316
2
7

MongoDB .NET Driver - Convert string to DateTime and for Filter Builder

3 Answers3