Filtering using AsQueryable in Mongodb C# - ExpandoObject

user_1700775 0
Viewed 1671

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!

mongodb-.net-driver mongodb linq c#
2 Answers

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

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.

user_18599590
Related