Introduction to MongoDb with .NET part 16: querying in the .NET driver

Introduction

In the previous post we continued our exploration of data serialisation in the MongoDb .NET driver. We looked at various attributes such as BsonIgnore, BsonRepresentation or BsonIgnoreIfNull. Data serialisation is not too complex as long as the documents in MongoDb adhere to some schema, like in our demo restaurants and zipcodes collection. As soon as we have to deserialise an unordered set of documents then we’ll probably need to implement our own serialisation and things get more complicated. This is an additional reason to follow a schema even if MongoDb documents are schemaless.

In this post we’ll look at querying techniques in the .NET driver. We’ll build upon the demo .NET code we started building previously.

The Find methods

The IMongoCollection of T interface offers the following Find methods at the time of writing this post:

• Find: an extension method that enables us to build a query in a fluent manner, i.e. we can chain various other methods to it related to searching
• FindSync: a synchronous find method that returns a cursor and is not as flexible as the Find extension method
• FindAsync: an asynchronous, i.e. awaitable version of FindSync

All of these functions accept a filter that basically follow two different formats and we’ve seen an example for both of them: a FilterDefinition or a LINQ statement that returns true. I find the Find extension method the most flexible of the above simply because it opens the possibility of attaching other search-related queries to it in a fluent manner such as sorting and limiting. I’ll therefore stick to it in this post but it’s good to be aware of the alternatives.

findOne and find all

Recall that findOne() offers a quick way to get acquainted with the document structure in the Mongo shell. How can we write the same in C#? Easy:

ZipCodeDb firstZip = modelContext.ZipCodes.Find(z => true).FirstOrDefault();
RestaurantDb firstRestaurant = modelContext.Restaurants.Find(r => true).FirstOrDefault();

We return true without any filtering in the LINQ statement. It is equivalent to a SELECT * FROM statement in SQL without a where clause. Then we just attach the familiar FirstOrDefault method to get the first document.

Conversely if we want to retrieve all documents without any filter then we just apply the ToList terminal operator:

List<ZipCodeDb> allZipCodes = modelContext.ZipCodes.Find(z => true).ToList();
List<RestaurantDb> allRestaurants = modelContext.Restaurants.Find(r => true).ToList();

You’ll notice as you flick through the functions offered by IntelliSense that both the FirstOrDefault and ToList methods have asynchronous versions: FirstOrDefaultAsync and ToListAsync. That is quite common throughout the MongoDb library that a function comes in both a synchronous and an asynchronous version.

Filtering basics

As noted before filtering can be built using either the FilterDefinition object specific to the MongoDb library or the more general LINQ expressions. .NET programmers will be aware of LINQ I believe so let’s start with the FilterDefinitionBuilder instead. In my experience there are complex filter definitions that can only be expressed using this specific syntax so it’s good to be aware of it even though you’re a LINQ fanatic.

Filter definitions are built using the generic Builders static class and its Filter property. The Builders class is a gateway to build a number of different definition objects and FilterDefinitions are only one of them. The Filter property then has a large number of methods that help us build queries. We’ve already seen an example before:

ModelContext modelContext = ModelContext.Create(new ConfigFileConfigurationRepository(), new AppConfigConnectionStringRepository());
var filter = Builders<RestaurantDb>.Filter.Eq(r => r.Borough, "Brooklyn");

If you type “Builders.Filter.” in Visual Studio then IntelliSense will bring up a long range of functions that more or less correspond to the query operators we saw before in this series. E.g. the Gt function maps to the $gt operator, Not to $not etc. In many cases it’s not too difficult to guess the purpose of the function by its name, like Gt = GreaterThan. The Filter property has a special property called Empty that corresponds to the empty JSON filter {} we saw in earlier examples in the Mongo shell. It is also the FilterDefinition equivalent of the “return true” LINQ statement in the Find examples above.

The Filter methods, such as Eq and Gte then require one or more parameters. The Eq function, like other similar functions like Gte and Lt, require a field selector in form of a LINQ expression and then then the filter value. The above example selects the field called “borough” and we want to filter this field on the value “Brooklyn”.

Let’s see an example for the logical AND operator with the FilterDefinition solution:

ModelContext modelContext = ModelContext.Create(new ConfigFileConfigurationRepository(), new AppConfigConnectionStringRepository());
var boroughFilter = Builders<RestaurantDb>.Filter.Eq(r => r.Borough, "Brooklyn");
var cuisineFilter = Builders<RestaurantDb>.Filter.Eq(r => r.Cuisine, "Delicatessen");
var cuisineAndBoroughFilter = boroughFilter & cuisineFilter;
var firstRes = modelContext.Restaurants.Find(cuisineAndBoroughFilter).First();
Console.WriteLine(firstRes);

We want to find the first restaurant in Brooklyn with style “Delicatessen”. The “&” operator is a short-hand notation to chain AND conditions. Here’s an alternative solution:

var cuisineAndBoroughFilterAlternative = Builders<RestaurantDb>.Filter.And(boroughFilter, cuisineFilter);

The And function accepts a collection or parameter array with any number of filter definitions and chains them together with an AND. As side note you can probably guess that there is an Or function that accepts the same filter definition parameters and chains them together with a logical OR. The short-hand notation for that is a single pipe character ‘|’.

Here’s an example:

{
  "MongoDbId": "56edc2ff03a1cd840734dbb1",
  "Address": {
    "BuildingNr": "7114",
    "Coordinates": [
      -73.9068506,
      40.6199034
    ],
    "Street": "Avenue U",
    "ZipCode": "11234"
  },
  "Borough": "Brooklyn",
  "Cuisine": "Delicatessen",
  "Grades": [
    {
      "InsertedUtc": "2014-05-29T00:00:00Z",
      "Grade": "A",
      "Score": 10
    },
    {
      "InsertedUtc": "2014-01-14T00:00:00Z",
      "Grade": "A",
      "Score": 10
    },
    {
      "InsertedUtc": "2013-08-03T00:00:00Z",
      "Grade": "A",
      "Score": 8
    },
    {
      "InsertedUtc": "2012-07-18T00:00:00Z",
      "Grade": "A",
      "Score": 10
    },
    {
      "InsertedUtc": "2012-03-09T00:00:00Z",
      "Grade": "A",
      "Score": 13
    },
    {
      "InsertedUtc": "2011-10-14T00:00:00Z",
      "Grade": "A",
      "Score": 9
    }
  ],
  "Name": "Wilken'S Fine Food",
  "Id": 40356483
}

Here’s the LINQ-style solution:

var firstResWithLinq = modelContext.Restaurants.Find(r => r.Borough == "Brooklyn" && r.Cuisine == "Delicatessen").FirstOrDefault();

Let’s see something more complex: find the first restaurant whose grades array includes an A, a B and a C grade, i.e. at least one of each. We need to dig down into the object graph in this case. The Filter property has an All function which builds an $all filter for an array field but we cannot use it in this case. The All method would help if the grades array were only a simple string array, like “grades: [“A”, “B”]”. We need to go one level deeper here.

The solution comes in the form of three ElemMatch functions that are linked with an AND. The ElemMatch function accepts a collection field and then a filter on a sub-property of the collection as follows:

var arrayFilterGradeA = Builders<RestaurantDb>.Filter.ElemMatch(r => r.Grades, g => g.Grade == "A");
var arrayFilterGradeB = Builders<RestaurantDb>.Filter.ElemMatch(r => r.Grades, g => g.Grade == "B");
var arrayFilterGradeC = Builders<RestaurantDb>.Filter.ElemMatch(r => r.Grades, g => g.Grade == "C");
var arrayFilterWithAllGrades = arrayFilterGradeA & arrayFilterGradeB & arrayFilterGradeC;
var firstResWithAllGrades = modelContext.Restaurants.Find(arrayFilterWithAllGrades).FirstOrDefault();
Console.WriteLine(firstResWithAllGrades);

Here’s a restaurant that matches the filter:

{
  "MongoDbId": "56edc2ff03a1cd840734dbb5",
  "Address": {
    "BuildingNr": "1269",
    "Coordinates": [
      -73.871194,
      40.6730975
    ],
    "Street": "Sutter Avenue",
    "ZipCode": "11208"
  },
  "Borough": "Brooklyn",
  "Cuisine": "Chinese",
  "Grades": [
    {
      "InsertedUtc": "2014-09-16T00:00:00Z",
      "Grade": "B",
      "Score": 21
    },
    {
      "InsertedUtc": "2013-08-28T00:00:00Z",
      "Grade": "A",
      "Score": 7
    },
    {
      "InsertedUtc": "2013-04-02T00:00:00Z",
      "Grade": "C",
      "Score": 56
    },
    {
      "InsertedUtc": "2012-08-15T00:00:00Z",
      "Grade": "B",
      "Score": 27
    },
    {
      "InsertedUtc": "2012-03-28T00:00:00Z",
      "Grade": "B",
      "Score": 27
    }
  ],
  "Name": "May May Kitchen",
  "Id": 40358429
}

The example demonstrates that sometimes we need to be creative with our queries in the driver to apply what we want but eventually we can find the equivalent of the filters we wrote in the Mongo shell.

That’s enough of the basics of filtering. We’ll continue with some other methods that can refine the search results in the next post.

You can view all posts related to data storage on this blog here.