Filtering using AsQueryable in Mongodb C# - ExpandoObject

I am trying to query the mongodb database using AsQueryable and LINQ functions.

My current database structure is that I have some collections, mainly a record which are defined in C# like this:

public class Record  
    {
        [BsonElement("formName")]
        public string FormName { get; set; }

        [BsonElement("created")]
        public DateTime Created { get; set; }

        [BsonElement("createdBy")]
        public string CreatedBy { get; set; }

        [BsonId]
        [BsonIgnoreIfDefault]
        [BsonRepresentation(BsonType.ObjectId)]
        private string InternalId { get; set; }

        [BsonElement("recordId")]
        [Newtonsoft.Json.JsonProperty("recordId")]
        public string Id { get; set; }

        [BsonElement("organisationId")]
        public string OrganisationId { get; set; }

        // TODO: Consider making configurable
        private string appName = "MediLog";

        [BsonElement("appName")]
        public string AppName
        {
            get { return this.appName; }
            set { this.appName = value; }
        }

        [BsonElement("schemaVersion")]
        public int SchemaVersion { get; set; }

        [BsonElement("contents")]
        public ExpandoObject Contents { get; set; }

        [BsonElement("modified")]
        public DateTime Modified { get; set; }

        [BsonElement("modifiedBy")]
        public string ModifiedBy { get; set; }

        [BsonElement("majorVersion")]
        public int MajorVersion { get; private set; }

        [BsonElement("minorVersion")]
        public int MinorVersion { get; private set; }

        [BsonElement("version")]
        public string Version
        {
            get
            {
                return (MajorVersion + "." + MinorVersion);
            }

            set
            {
                MajorVersion = Convert.ToInt32(value?.Substring(0, value.IndexOf(".", StringComparison.OrdinalIgnoreCase)), CultureInfo.InvariantCulture);
                MinorVersion = Convert.ToInt32(value?.Substring(value.IndexOf(".", StringComparison.OrdinalIgnoreCase) + 1), CultureInfo.InvariantCulture);
            }
        }

    }

As you, data are mainly stored in Contents which is an ExpandoObject and that's where my issue is.

I am trying to do something like this:

 var collection =
                database.GetCollection<Record>("recordData").AsQueryable()
                    .Where(x =>  x.Contents.SingleOrDefault(z => z.Key == "dateOfBirth").Value.ToString().Length > 0)
                        .ToList();  

but I get this exception:

System.InvalidOperationException HResult=0x80131509 Message={document}{contents}.SingleOrDefault(z => (z.Key == "dateOfBirth")).Value.ToString().Length is not supported.

Also, when I tried:

  var collection1 = database.GetCollection<Record>("recordData").AsQueryable()
                .Where(x => (DateTime)(x.Contents.SingleOrDefault(z => z.Key == "dateOfBirth").Value)  > DateTime.Now)
                .ToList();

I get this exception:

System.InvalidOperationException
  HResult=0x80131509
  Message=Convert({document}{contents}.SingleOrDefault(z => (z.Key == "dateOfBirth")).Value) is not supported.

Also, when I do this:

var collection =
database.GetCollection(“recordData”).AsQueryable()
.Where(x => (DateTime)x.Contents.First(z => z.Key == “dateOfBirth”).Value > DateTime.Now ).ToList();

I am getting this exception:

System.NotSupportedException: ‘The expression tree is not supported: {document}{contents}’

So my question really is that how can I run queries on an object which is of type ExpandoObject using AsQueryable and LINQ.

Thanks!

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();