MongoDB + C# driver + query array of elements where each array element contains sub-document to query on

Question

I'm using version 1.5.0.4566 of the official MongoDB C# driver. I'm using Mongo version 2.06.

Here is what my document structure looks like (omitted fields not necessary for this discussion):

{ "Parents" : 
   [ 
     {
     "CreatedOn": ISODate("2012-07-28T15:30:06.623Z"),
     "Title": "Simple Title",
     "Children": [   
                  { "StatusId": 1, "Active" : true, SubChild : { "ExpiresOn": ISODate("2012-07-28T15:30:06.623Z")}},
                  { "StatusId": 1, "Active" : true, SubChild : { "ExpiresOn": ISODate("2012-08-28T15:30:06.623Z")}}
                 ]
     },
     {
     "CreatedOn": ISODate("2012-07-28T15:30:06.623Z"),
     "Title": "Another Simple Title",
     "Children": [   
                  { "StatusId": 1, "Active" : true, SubChild : { "ExpiresOn": ISODate("2012-07-28T15:30:06.623Z")}},
                  { "StatusId": 1, "Active" : true, SubChild : { "ExpiresOn": ISODate("2012-08-28T15:30:06.623Z")}}
                 ]
     }
   ]
}

If I wanted to query the Children that have a StatusId equal to one and Active is true I can use ElemMatch.

Query.ElemMatch("Children", Query.And(Query.EQ("StatusId", 1),Query.EQ("Active",true)));

What I cannot get to work is when I need to include the SubChild element in my query.

Query.ElemMatch("Children", Query.And(Query.EQ("StatusId",1), Query.EQ("Active",true),Query.LT("SubChild.ExpiresOn",DateTime.UtcNow)));

The query doesn't return any values when I try to include the SubChild.ExpiresOn field in the query. I have tried different ways to build this query, but keep getting zero documents when I include the SubChild.ExpiredOn field.

What am I missing?

Thanks,

Answer 1

Try this instead

Query.ElemMatch("Children", Query.And(Query.EQ("StatusId",1), Query.EQ("Active",true),Query.LT("SubChild.ExpiresOn",DateTime.UtcNow)));

Wondering why this query magically works? It's the case (StatusId vs StatusID). JavaScript is case sensitive.

You could eliminate this problem by using strongly typed Linq queries, like:

from x in collection.AsQueryable()
where x.Children.Any(child => 
    child.StatusId == 1 
    && child.Active 
    && child.SubChild.ExpiresOn < DateTime.UtcNow)
select x