Performing aggregations and using async methods with MongoDB 3.2 and C# driver 2.0

• Download sample - 17.7 MB

Introduction

Hi folks, I’m back here with more tips and recipes for non-relational databases. This time we will be talking about the latest C# MongoDB driver. It’s far from being a brand new release, however when I was looking for some guidelines to kick in some basic crud operations and aggregations using it, well, I have found the official material quite terse.

The most outstanding difference in this version is the full stack async support, which is quite cool. But it is a bit hard to understand this particular feature if you don’t place it into a real world sample.

That is all this article is about, in the sessions below I will be showing a full operational and straightforward app, using all main crud methods and a couple of aggregations.

Background

Currently there are several non-relational databases available in the industry. If you are familiar at least with one of them, you will be fine here. Otherwise here in the Code Project community you can find several introductory articles regarding this subject.

Installation

Let’s start with the database setup! I have based this whole article on the official Mongodb material, so I will refer its website every now and then along the way.

To begin with, you need to download the installer from the official source. The installation wizard will guide you through the process and, by the moment you finish it, double check you have got the Mongodb service up running:

Now we need some data to work with, and the dataset provided by MongoBD will suit us. Here you will find it (json file) and the command line to import it.

Database IDE

Im using the Razor SQL. This tool is pretty cool, the only problem is... there is no free version. However you are able to use it for 30 days under the trial license. After importing the sample data, the table/json collection will be shown as:

Creating a demo App

First step is creating a new Windows Form solution or, even better, you can download it as it is available right in the beginning of this article. Its structures goes as following:

The references highlighted in red are the most important and you can get them via NuGet. Please make sure of getting the 2.0 C# driver version.

All blue area is how I have decided to organize the project. Pretty standard: BLL stands for the Business Rules, DAL for the Data Access Layer, DTO contains the Entities and View holds our Windows Form.

Back to the sample data structure, You probably have noticed that it has fields related to the restaurant is organized in a hierarchy format:

{
  "address": {
     "building": "1007",
     "coord": [ -73.856077, 40.848447 ],
     "street": "Morris Park Ave",
     "zipcode": "10462"
  },
  "borough": "Bronx",
  "cuisine": "Bakery",
  "grades": [
     { "date": { "$date": 1393804800000 }, "grade": "A", "score": 2 },
     { "date": { "$date": 1378857600000 }, "grade": "A", "score": 6 },
     { "date": { "$date": 1358985600000 }, "grade": "A", "score": 10 },
     { "date": { "$date": 1322006400000 }, "grade": "A", "score": 9 },
     { "date": { "$date": 1299715200000 }, "grade": "B", "score": 14 }
  ],
  "name": "Morris Park Bake Shop",
  "restaurant_id": "30075445"
}

And our Windows Form, in turn, will reflect this schema as following:

Database connection

If you have downloaded the demo app, you might have notice 2 constant values appearing everywhere:

namespace Mongodb_20Driver_CRUD.DTO
{
    /// System constant values
    public static class Constants
    {
        /// BD name
        public const string DEFAULT_DB = "test";

        /// Coll name
        public const string DEFAULT_COLLECTION = "restaurants";

    }
}

These two values represents the db name (“test”) and the table/collection (“restaurants”) that we have imported previously. You will see these two constants all around from now on.

We also need a class to play the MongoBD client role, and it looks like this:

namespace Mongodb_20Driver_CRUD.DAL
{
    /// Mongodb client
    public class MongodbClient
    {

        /// Client instance
        public IMongoClient Client { get; set; }

        /// Database instance
        public IMongoDatabase DataBase { get; set; }
        
        /// Constructor
        public MongodbClient()
        {
            Client = new MongoClient();
            DataBase = Client.GetDatabase(DTO.Constants.DEFAULT_DB);            
        }
    }
}

Yeah I know, it looks way too simple. However, it’s all we need in order to connect and run queries against the Mongodb.

For instance, this is the method used to discover how many rows the sample data has:

public async Task<long>  GetOverallCount()
{
	long count = 0;
	try
	{
		var collection = _clientDAL.DataBase.GetCollection<BsonDocument> 
		(DTO.Constants.DEFAULT_COLLECTION);
		var filter = new BsonDocument();
		using (var cursor = await collection.FindAsync(filter))
		{
			while (await cursor.MoveNextAsync())
			{
				var batch = cursor.Current;
				foreach (var document in batch)
				{
					count++;
				}
			}
		}
	}
	catch (Exception ex)
	{
		throw new Exception("Error retrieving the overall count: " + ex.ToString());
	}
	return count;
}

Note that this method uses those 2 classes I’ve shown previously right in this line:

var collection = _clientDAL.DataBase.GetCollection<BsonDocument>(DTO.Constants.DEFAULT_COLLECTION);

The “_clientDAL” is the MongodbClient instance, and we use this command to retrieve the “restaurants” table/collection.

With this collection’s instance in hand, we are able to loop through all restaurants items using the return of the async method “FindAsync”.

It might look like a lot of code just for processing a table count, but when we put together the whole app, the advantage of this async approach will be clear.

Inserting / Updating a row

Now that you already know the basics classes of our demo, we will start coding our BLL (business logic). The first feature here will be inserting and updating a restaurant in the database:

public async void Insert(DTO.Restaurant customer)
{
	try
	{
		if (customer == null)
			return;

		//Create inner grades list:
		BsonArray grades = new BsonArray();
		foreach (var grade in customer.Grades)
		{
			grades.Add(new BsonDocument
					{
						{ "date", grade.Date },
						{ "grade", grade.GradeValue },
						{ "score", grade.Score }
					});                    
		}

		//Create main object:
		var document = new BsonDocument
		{
			{ "address" , new BsonDocument
				{
					{ "street", customer.Address.Street },
					{ "zipcode", customer.Address.Zipcode },
					{ "building", customer.Address.Building },
					{ "coord", new BsonArray { customer.Address.Coord.Item1,
					customer.Address.Coord.Item2 } }
				}
			},
			{ "borough", customer.Borough },
			{ "cuisine", customer.Cuisine },
			{ "name", customer.Name },
			{ "restaurant_id", customer.RestaurantID }
		};

		document.Add("grades", grades);

		//Insert it:
		var collection = _clientDAL.DataBase.GetCollection<BsonDocument> 
		(DTO.Constants.DEFAULT_COLLECTION);
		await collection.InsertOneAsync(document);

	}
	catch (Exception ex)
	{
		throw new Exception("Error inserting new Restaurant: " + ex.ToString());
	}          
}

A couple of things to be discussed here:

First, the c# driver uses BsonDocument objects to represent a json object. There are others overloads, however BsonDocument is widely used.

About the method itself, it receives a “restaurant” entity (please refer to DTO namespace to check it out all entities) and it is parsed into a BsonDocument instance. As the restaurant’s grades can be more than one, these objects are kept in a list (BsonArray) and it is linked in the main BsonDocument object.

At last, with our good ol’ collection instance filled up, we just need to call the “InsertOneAsync” method to finalize the operation.

We are still missing the updating piece here and, fortunately, the c# driver provides a nice way to handle it. To do so, you just need to replace the last two lines of the method by this:

var filter = Builders<BsonDocument>.Filter.Eq("restaurant_id",customer.RestaurantID);
 
var collection = _clientDAL.DataBase.GetCollection<BsonDocument> (DTO.Constants.DEFAULT_COLLECTION);  

var result = await collection.ReplaceOneAsync(filter, document, new UpdateOptions() { IsUpsert = true });

If no document matches the update condition, the default behavior of the “ReplaceOneAsync” method is to do nothing. However, by specifying the upsert option to true, the update operation either updates matching document(s) or inserts a new document if no matching document exists.

Deleting a row

This is the easiest one:

var collection = _clientDAL.DataBase.GetCollection<BsonDocument>(DTO.Constant.DEFAULT_COLLECTION);
var filter = Builders<BsonDocument>.Filter.Eq("restaurant_id", id);
var result = await collection.DeleteOneAsync(filter);

It is just a matter of informing the unique id that you want to delete, wrapped in a Filter object, and call the “DeleteOneAsync” method. There are others options available in the c# driver, for example you are able delete more the one row at once via “DeleteManyAsync”.

Aggregations

That is the most valuable feature of any database for the business point of view. It’s when your solution will be able to make sense out of large amounts of data.

For the simplicity sake we are going to implement 3 types of aggregations, allowing 3 filtering options for each. Our aggregation UI has this design:

Yeah, I know! That’s what happens when a backend developer tries to venture into the UI design field, such an amazing thing! Anyways, what matters here is how to handle the querying objects in order to request aggregations. Let’s start with the Cuisine count:

public async Task<List<DTO.Aggregatedtem> >  GetAggregatedCuisineList(DTO.Filter filter)
{
	List<DTO.Aggregatedtem>  returnList = new List<DTO.Aggregatedtem> ();
	var query = Builders<BsonDocument> .Filter.Empty;
	var criteriaFilter = Builders<BsonDocument> .Filter.Empty;
				
	try
	{
		//Filtering (block 1):
		if (string.IsNullOrEmpty(filter.Borough) == false)
		{
			criteriaFilter = Builders<BsonDocument>.Filter.Eq("borough",filter.Borough);
			query = query & criteriaFilter;
		}

		if (string.IsNullOrEmpty(filter.Cuisine) == false)
		{
			criteriaFilter = Builders<BsonDocument>.Filter.Eq("cuisine",filter.Cuisine);
			query = query & criteriaFilter;
		}

		if (string.IsNullOrEmpty(filter.Grade) == false)
		{
			criteriaFilter = Builders<BsonDocument>.Filter.Eq("grades.grade", filter.Grade);
			query = query & criteriaFilter;
		}

		//Querying (block 2):
		var collection = _clientDAL.DataBase.GetCollection<BsonDocument> (DTO.Constants
		.DEFAULT_COLLECTION);
		var results = await collection.Aggregate()
									   .Match(query)
									   .Group(new BsonDocument
									   { { "_id", "$cuisine" }, 
									   { "count", new BsonDocument("$sum", 1) } })
									   .ToListAsync();

		//Parsing results (block 3):
		foreach (BsonDocument item in results)
		{
			returnList.Add(new DTO.Aggregatedtem()
			{
				Label = item.Elements.ElementAtOrDefault(0).Value.AsString,
				Value = item.Elements.ElementAtOrDefault(1).Value.AsInt32
			});
		}

	}
	catch (Exception ex)
	{
		throw new Exception("Error preforming the aggregation: " + ex.ToString());            
	}

	return returnList.OrderByDescending(x =>  x.Value).ToList();
}

This method is pretty much divided in 3 blocks:

In the first block, it conditionally creates the query object according to the DTO.Filter informed.

Then, it’s time to invoke a couple of lambda methods out of our known 'collection' object:

• First we inform that we want to aggregate the results (Aggregate()).
• We also set the query object (Match(query)) that was created in the first step.
• Then we group the results by the $cuisine field (which is the field name prefixed by a dollar sign $).
• And finally we call the async method to retrieve the aggregated dataset (TopstAsync()).

The final step is to parse the BsonDocument return into our DTO.Aggregatedtem. There are smarter ways to do it, for instance you could use the own BsonDocument parser.

To perform the borough’s aggregation, the logic is exactly the same. You just need to replace the grouping field name by “$borough”.  

And for the last KPI, we are going to aggregate all info by the restaurant’s max score (which might be quite useful when taking your sweetheart out for a dinner):

public async Task<List<DTO.Aggregatedtem>> GetAggregatedMaxScore(DTO.Filter filter)
{
	List<DTO.Aggregatedtem>  returnList = new List<DTO.Aggregatedtem> ();
	var query = Builders<BsonDocument> .Filter.Empty;
	var criteriaFilter = Builders<BsonDocument> .Filter.Empty;            

	try
	{
		//Filtering (block 1):
		if (string.IsNullOrEmpty(filter.Borough) == false)
		{
			criteriaFilter = Builders<BsonDocument>.Filter.Eq("borough",
			filter.Borough);
			query = query & criteriaFilter;
		}

		if (string.IsNullOrEmpty(filter.Cuisine) == false)
		{
			criteriaFilter = Builders<BsonDocument>.Filter.Eq("cuisine",
			filter.Cuisine);
			query = query & criteriaFilter;
		}

		if (string.IsNullOrEmpty(filter.Grade) == false)
		{
			criteriaFilter = Builders<BsonDocument>.Filter.Eq("grades.grade",
			filter.Grade);
			query = query & criteriaFilter;
		}

		//Querying (block 2):
		var collection = _clientDAL.DataBase.GetCollection<BsonDocument> 
		(DTO.Constants.DEFAULT_COLLECTION);
		var results = await collection.Aggregate()
		                              .Match(query)
		                              .Group(new BsonDocument 
		                              {{"_id", "$name" }, { "MaxScore", 
		                              new BsonDocument("$max", "$grades.score")})
		                              ToListAsync();
	   
		//Parsing (block 3):
		foreach (BsonDocument item in results)
		{                    
			string key = item.Elements.ElementAtOrDefault(0).Value.AsString;
			var value = item.Elements.ElementAtOrDefault(1).Value.AsBsonArray.Max();

			if (value != null && value != BsonNull.Value)
			{
				returnList.Add(new DTO.Aggregatedtem() { Label = key, Value = double.Parse
				(value.ToString()) }); 
			}
		}
	}
	catch (Exception ex)
	{
		throw new Exception("Error preforming the aggregation. " + ex.ToString());
	}
	return returnList.OrderByDescending(x =>  x.Value).ToList();
}

Just in order to make it as didactic as possible, I’m keeping the same structure. The big difference here is how we aggregate the results, which is by the restaurant’s name. Another important aspect here is that rather than perform a count, now we are using the “$max” function over the grade’s score.

By the way, that’s is an example of how you're able to notate hierarchy for this sort of fields: grades.score.

Taking advantage of async features

As I have standed in the beginning, one of the outstanding features of the 2.0 diver is the native async methods. By using it properly, you are able to prevent the UI to freeze while it is executing other heavy processes.

In our small demo you are going to notice this by reproducing this scenario:

Fill up all fields, add some grades and click “save”. When you do so, the UI is going to trigger two actions: Save the restaurant and update the "row count" down in the left corner of the screen.

And here is the trick, the “save” action normally will get done before the counting. However, the UI is not going to be locked while waiting for the counting process to finish, thus you are going see the the count being nicely increased by one a couple of seconds after the the saving action.

It’s a very basic example indeed, but it is a glimpse of how powerful this feature is.

Conclusion

As I have written in previous articles, non-relational databases are here to stay. They are flexible, reliable, performatic and perhaps more importantly: the developer’s community is growing bigger.

This rise of developers working with these new databases leads to a fast knowledge sharing, quite exciting!

Now it’s a matter of keeping up with so many features and enhancements coming up almost on daily basis.