[Solved]-Filtering using AsQueryable in Mongodb C# - ExpandoObject-LINQ,C#

whatever you're trying to pass into .where() method is an expression tree that needs to be translated into mongodb query language. c# compiler will accept expandoobject's methods like singleordefault (extension method) but this will fail in the runtime when such expression tree needs to be translated into mongo query.

there's nothing special about expandoobject when you work with mongodb. it has to be translated somehow into bson document (mongodb format) and for example below code:

dynamic o = new expandoobject();
o.dateofbith = new datetime(2000, 1, 1);
r.contents = o;
col.insertone(r);

inserts expandoobject same way as bsondocument or dictionary<string, t> so it simply becomes:

{ "_id" : objectid(",,,"), "contents" : { "dateofbith" : isodate("2000-01-01t07:00:00z") } }

in your database.

knowing that you can build your query using the dot notation. there's also a stringfielddefinition class you can utilize since you cannot build an expression tree from expandoobject in a strongly typed way:

var fielddef = new stringfielddefinition<record, datetime>("contents.dateofbith");
var filter = builders<record>.filter.gt(fielddef, new datetime(2000, 1, 1));

var res = col.find(filter).tolist();

these kind of expression transformations doesn't play well with linq how i see. tho if you go down 1 level to define the field as string, it will play better. than you can inject your where clause to keep iqueryable if you like>

update (you can have multiple conditions, and different kinds of null checking)

string connectionstring = "mongodb://localhost:27017";
var client = new mongoclient(connectionstring);
var db = client.getdatabase("test");
var records = db.getcollection<record>("recorddata");
//dynamic content = new expandoobject();
//content.dateofbirth = datetime.today;
//records.insertone(new record
//{
//    appname = "my app", created = datetime.today, createdby = "some user", formname = "form name",
//    organisationid = "organization id", schemaversion = 1, version = "1.1", contents = content
//});
// first option for multiple conditions:
var filter =
    builders<record>.filter.lt("contents.dateofbirth", datetime.now) &
    builders<record>.filter.gt("contents.dateofbirth", datetime.now.adddays(-10)) &
    // checking if the field is there. whether the value is null or not.
    builders<record>.filter.exists("contents.dateofbirth") &
    // checking if the field is there, and it's not null
    builders<record>.filter.ne("contents.dateofbirth", bsonnull.value);
// second option for multiple conditions
var dateofbirths = records.asqueryable().where(_ => filter.inject()).where(x => x.appname == "my app").tolist();

from there, you can continue your iqueryable syntax.

update 2

in case of .where(x => x.appname.contains("app")) you'll get

"pipeline" : [
        {
            "$match" : {
                "contents.dateofbirth" : {
                    "$exists" : true,
                    "$gt" : isodate("2020-05-15t12:48:14.483+02:00"),
                    "$lt" : isodate("2020-05-25t12:48:14.480+02:00"),
                    "$ne" : null
                }
            }
        },
        {
            "$match" : {
                "appname" : /app/g
            }
        }
    ]

from profiling the database. so the <string>.contains(<string>) is implemented in iqueryable as a regular expression filter not in memory.