I have 20 fields that users can put any information in them. They can specify if any of those fields are dates. This allows in the user interface to use a date picker and filter and apply date filters.
Is there anyway with the C# driver to a LINQ date filter query on these fields?
Ideally, I would ant to simply do:
result.Where(x => x.A1 != null && searchMin <= DateTime.Parse(x.A1) && DateTime.Parse(x.A1) <= searchMax);
I saw that there are the $toDate and $dateFromString functions, but I have not found any implementation of this with the driver.
Any help on this and what is possible would be greatly appreciated.
I was dealing with something like this yesterday. Tried a whole range of things, like converting the DateTime to Ticks, new Dates and other things. I ended up just using a combination of
[BsonDateTimeOptions(Kind = DateTimeKind.Local)]
above the property in the class and it worked like normal in the Linq.
I can’t change my fields to DateTime fields, because they are used to store any type of information. It can be a date, a number, a list of options. These are custom fields that can be used anyway needed, so I need to store them as strings.
I really need to be able to Parse/Convert the field to a date to run my query.
The driver does not support everything Linq does, but it seems like this should be implemented. Particularly, since there is the $dateFromString function.
The ticks is the only viable option I have found so far, but that requires a lot of overhead to manage this.
What I am doing now is doing the date range filters in memory. This is not clean at all, but I do not see any other option.