This project has moved. For the latest updates, please go here.

Mongo querying compare two fields on each record

Jan 20, 2015 at 8:15 PM

Is it possible to query a collection by comparing two fields on each document? For example:
var repo = new MongoRepository<Foo>();
        repo.Add(new[] {
                new Foo { Int1 = 10, Int2 = 20 },
                new Foo { Int1 = 20, Int2 = 10 },
                new Foo { Int1 = 15, Int2 = 15 },

        var results = repo.Where(f => f.Int1 > f.Int2);
I would expect the second record back (int1=20, int2=10).

I tried building a query like this but it fails and throws an exception explaining that the where clause is invalid.
Jan 20, 2015 at 8:35 PM
Edited Jan 20, 2015 at 8:41 PM
This is a bit harder to answer than I'd like but here goes:

In essence, you can't write a query that compares two fields. You can, but then you'd need to pass a Javascript function to the $where clause so that MongoDB can execute that function on each document. However, this executes pretty slow (but: server-side). MongoRepository itself does not support this, but you coud derive your own class from MongoRepository<T> and add methods that do support this scenario using the underlying MongoDB C-sharp driver. Simply add using MongoDB.Driver (and possibly using MongoDB.Bson) and use the driver directly.

You could, as an alternative, 'materialize' the documents (using .ToArray() for example) after doing an (optional) initial filter (e.g. Where(f => f.SomeField == "bar").ToArray()) and then use Linq-to-Objects to filter out the entities client-side (...ToArray().Where(f => f.Int1 > f.Int2)). Depending on the number of documents you're expecting to retrieve before filtering that is a possibility but mostly for small-ish resultsets.

Another option is to go over the collection once and update each entity with a new property that stores the result of Int1 > Int2 in the entity itself.

Other options are MapReduce, Aggregate framework and maybe even more. But these, also, would require you to use the underlying driver more directly than MongoRepository itself allows.

I hope this answers your question?
Jan 20, 2015 at 9:36 PM
Thank you for the detailed response. I knew of the javascript nested function method but was unaware how it would be implemented via MongoRepository.

Currently I am using your materialize method, which is not very performant since it must load nearly every record in the collection. I was hoping to offset this by storing two ticks (long) values during document update then later query on the difference between these values as a basis for the next operation.

Thanks for an amazing library, I've used MR in several projects now and really enjoy working with it.