I'm trying to use dynamic Linq (System.Linq.Dynamic.Core
) to query
data from MongoDB, based on a SQL-like string query I get from Excel.
This
works, but I'm trying to change the semantics of date - long
comparisons.
I'm trying to enable queries where I compare an ISODate field with a
double representing the Excel datetime (OLE automation date, number of
days since 1899-12-30), but any parsed long gets cast into a DateTime
based on the number of 100ns intervals since 0001-01-01T00:00.000
Gregorian via this overload of the datetime
constructor.
I want to somehow change this, so that an integer/double in a
comparison gets
cast into a DateTime
for comparison via the
DateTime.FromOADate(Double)
function.
The official MongoDB driver supports the IQueryable
interface, and
I'm using
it directly.
Here's the base for what I'm trying to accomplish:
MongoClientSettings settings =
MongoClientSettings.FromConnectionString(
"mongodb://my-mongo.local"
);
settings.LinqProvider = LinqProvider.V2;
MongoClient client = new MongoClient(settings);
var database = client.GetDatabase("my_db");
var collection = database.GetCollection<BsonDocument>("colls.my_coll");
/* colls.my_colls consists of members like this:
{
"_id" : ObjectID,
"name": string,
"numeric": double,
"date": ISODate
}
*/
// in the query, 44920 is the OLE automation date representation of
2022-12-25
var results = collection
.AsQueryable<BsonDocument>()
.AsQueryable()
.Where("date < 44920 and name != \"badname\" and numeric >= 520");
This doesn't work because the integer gets interpreted in the 'wrong' way.
One potential solution that came to my mind was to
map
the MongoDB result to a custom class, and use a custom (DateTime-
wrapping)
class for the date
field in it, which has the proper conversion
constructor
for int/double. I would then have to use a custom TypeConvertor
to
implement
an automatic conversion (I found
this
in the tests), but I don't fully understand how TypeConvertor
s work
(I'm a
recent C++ to C# convert).
Am I on the right track? Am I missing something? Is there a better way?