Advanced MongoDB Queries in C# using the NoRM Driver: Part 1

In my last two posts, I said that I planned on showing how to perform more advanced queries using MongoDB. Today I will be doing just that by demonstrating advanced queries using the NoRM driver. The queries you learn here are not specific to the C# NoRM driver. I will show you three forms of each query: (1) the Json version and (2) the NoRM driver version in C# using Anonymous objects, and (3) How to use Linq with the NoRM driver.

Note, the source code for all these examples can be found here on GitHub.

Without further ado, lets jump in.

Conditional Operators: <, <=, >, >=

Native MongoDB Json

To apply conditional operators, we use the following operators:

>	$gt	Greater than
>=	$gte	Greater than or equal to
<	$lt	Less than
<=	$lte	Less than or equal to

The operator is combined with its comparison value is a single Json object in place of the value for the field. For instance:

db.orders.find( { OrderAmount : { $gt : 50 } } );

The above finds all orders with an amount greater than 50. Notice the pattern { $gt : 50 } which is { $operator : value } is used as the value for OrderAmount. You will see this pattern repeated for many of the advanced queries.

Here are all the operators:

db.orders.find( { OrderAmount : { $gt : 50 } } );
db.orders.find( { OrderAmount : { $gte : 50 } } );
db.orders.find( { OrderAmount : { $lt : 100 } } );
db.orders.find( { OrderAmount : { $lte : 100 } } );

C# NoRM Driver

Using Anonymous Objects

To do this using the NoRM driver, you can either use LINQ, or query using anonymous objects. For those using anonymous objects, NoRM provides a static class named Q to build queries from. So you can use Q.GreaterThan(value) to build the query. Below are the same queries using NoRM in C#.

var result1 = collection.Find( new {OrderAmount = Q.GreaterThan( 50 )} );
var result2 = collection.Find( new {OrderAmount = Q.GreaterOrEqual( 50 )} );
var result3 = collection.Find( new {OrderAmount = Q.LessThan( 100 )} );
var result4 = collection.Find( new {OrderAmount = Q.LessOrEqual( 100 )} );

Using Linq

Using Linq is pretty straightforward so I won’t show but one of the operations here:

var provider = new MongoQueryProvider(mongo);
var orders = new MongoQuery< Order >( provider );
var result1 = from order in orders
              where order.OrderAmount > 50
              select order;

Between Operation

Json

To perform a between operation, simply combine two of the above operators.

db.orders.find( { OrderAmount : { $gt : 50, $lt : 200 } } );

This finds orders where the order amount is between 50 and 200.

NoRM

Anonymous

For the NoRM driver, we use the And() method.

var result = collection.Find( new {OrderAmount = Q.GreaterThan( 50 ).And(Q.LessThan( 200 ))} );

Linq

var result = from order in orders
             where order.OrderAmount > 50 && order.OrderAmount < 200
             select order;

Not Equal To

Json

db.orders.find( { OrderAmount : { $ne : 100.23 } } );

NoRM

Anonymous

var result = collection.Find( new {OrderAmount = Q.NotEqual( 100 )} );

Linq

var result = from order in orders
             where order.OrderAmount != 100
             select order;

In Operator

Json

db.orders.find( { CustomerName : { $in : ["Elmer Fudd", "Daffy Duck"] } } );

NoRM

Anonymous

var result = collection.Find( new {CustomerName = Q.In( "Elmer Fudd", "Daffy Duck" )} );

Linq

To use the IN operator with Linq, you first need to create a List<T> of the “in” items to compare against. Then us the Contains() method to see if the item exists.

var result = from order in orders
             where new List {"Elmer Fudd", "Daffy Duck"}.Contains( order.CustomerName )
             select order;

Note that I initially tried to do this by creating an array and use contains, but this failed. You must create a generic list to get it to work.

Conclusion

Well this concludes Part 1 of the advanced queries. We’ll dig even deeper next time.

Note, the source code for all these examples can be found here on GitHub.

-Chris