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

This entry was posted in .NET, C#, MongoDB, NoSQL and tagged , , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

5 Comments

  1. James Goddard
    Posted June 23, 2010 at 2:32 pm | Permalink

    So what would be the equivalent of your $in query with a subitem, such as:

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

  2. Posted June 23, 2010 at 5:26 pm | Permalink

    That is exactly correct. Here is a script you can execute to try it out.


    > // Create to orders with a Profile sub-object and add them to the orders collection.
    > var o1 = { "OrderAmount" : 25, "Profile" : { "CustomerName" : "Elmer Fudd" } };
    > var o2 = { "OrderAmount" : 50, "Profile" : { "CustomerName" : "Daffy Duck" } };
    > db.orders.save(o1);
    > db.orders.save(o2);
     
    > // Show what is currently in the table.
    > db.orders.find()
    { "_id" : ObjectId("4c2297124ebf3a006711b6a0"), "OrderAmount" : 25, "Profile" : { "CustomerName" : "Elmer Fudd" } }
    { "_id" : ObjectId("4c2297194ebf3a006711b6a1"), "OrderAmount" : 50, "Profile" : { "CustomerName" : "Daffy Duck" } }
     
    > // Show that it includes everyone in the list.
    > db.orders.find( { "Profile.CustomerName" : { $in : [ "Elmer Fudd", "Daffy Duck" ] } } );
    { "_id" : ObjectId("4c2297124ebf3a006711b6a0"), "OrderAmount" : 25, "Profile" : { "CustomerName" : "Elmer Fudd" } }
    { "_id" : ObjectId("4c2297194ebf3a006711b6a1"), "OrderAmount" : 50, "Profile" : { "CustomerName" : "Daffy Duck" } }
     
    > // Show that it excludes when not in the list.
    > db.orders.find( { "Profile.CustomerName" : { $in : [ "Elmer Fudd", "John Smith" ] } } );
    { "_id" : ObjectId("4c2297124ebf3a006711b6a0"), "OrderAmount" : 25, "Profile" : { "CustomerName" : "Elmer Fudd" } }

  3. James Goddard
    Posted June 24, 2010 at 6:27 am | Permalink

    Sorry, I guess I wasn’t clear. I know thats how to perform such a query in Mongo. What I was asking is what is the NoRM equivalent of such a query?

  4. James Goddard
    Posted June 24, 2010 at 6:42 am | Permalink

    Actually, let me extend that a little to include the actual use case I am struggling with. What I am actually trying to search on is any items where two arrays intersect such as in the following:

    use test;
    var o1 = { “CustomerName” : “Elmer Fudd”, Order : [ { ItemName: "Shotgun", Quantity : 1 }, { ItemName: "Shotgun Shell", Quantity : 500 }, { ItemName: "Rabbit Bait", Quantity : 5 }]};
    db.orders.save(o1);
    var o2 = { “CustomerName” : “Daffy Duck”, Order : [ { ItemName: "Mallet", Quantity : 1 } ]};
    db.orders.save(o2);

    // Show what is currently in the table.
    db.orders.find()
    { “_id” : ObjectId(“4c23518b186f000000000c8a”), “CustomerName” : “Elmer Fudd”, “Order” : [
    {
    "ItemName" : "Shotgun",
    "Quantity" : 1
    },
    {
    "ItemName" : "Shotgun Shell",
    "Quantity" : 500
    },
    {
    "ItemName" : "Rabbit Bait",
    "Quantity" : 5
    }
    ] }
    { “_id” : ObjectId(“4c2351f6186f000000000c8b”), “CustomerName” : “Daffy Duck”, “Order” : [ { "ItemName" : "Mallet", "Quantity" : 1 } ] }

    // Show that it includes everyone item the list.
    db.orders.find( { “Order.ItemName” : { $in : [ "Shotgun", "Mallet" ] } } );
    { “_id” : ObjectId(“4c23518b186f000000000c8a”), “CustomerName” : “Elmer Fudd”, “Order” : [
    {
    "ItemName" : "Shotgun",
    "Quantity" : 1
    },
    {
    "ItemName" : "Shotgun Shell",
    "Quantity" : 500
    },
    {
    "ItemName" : "Rabbit Bait",
    "Quantity" : 5
    }
    ] }
    { “_id” : ObjectId(“4c2351f6186f000000000c8b”), “CustomerName” : “Daffy Duck”, “Order” : [ { "ItemName" : "Mallet", "Quantity" : 1 } ] }

    // Show that it excludes when not in the list.
    db.orders.find( { “Order.ItemName” : { $in : [ "Mallet" ] } } );
    { “_id” : ObjectId(“4c2351f6186f000000000c8b”), “CustomerName” : “Daffy Duck”, “Order” : [ { "ItemName" : "Mallet", "Quantity" : 1 } ] }

    Unfortunately, every time I think I’ve come up with a workable solution using NoRM it throws an exception. For example if I try to use an Linq intersect it fails because it will not allow “subqueries” with intersect and so the Count fails on the result.

    If it is possible to perform such a query using NoRM I love to hear it.

  5. Andre' Hazelwood
    Posted July 23, 2010 at 9:54 am | Permalink

    My understanding is that you would use the Expando object to perform the query (something like the code below, haven’t tested it):

    var query = new Expando();
    query[ "Order.ItemName" ] = Q.In( “Mallet” );
    db.Collection.Find( query );

    Look at the BasicQueryUsingChildProperty() test in: http://github.com/atheken/NoRM/blob/master/NoRM.Tests/CollectionFindTests/QueryTests.cs

One Trackback

  1. By NoSql : MongoDB vs RavenDB « reflexionsWeb.info on December 13, 2010 at 8:47 am

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

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>