MongoDB in .NET part 8: queries

Introduction

So far in this series we’ve gone through the most important database operations in MongoDb .NET: create, update and delete which was the topic of the previous post. We’ve also seen some query examples such as finding an element by ID.

In this post we’ll look more closely at querying in MongoDb .NET. The C# driver provides the following ways of querying documents:

  • The various Find methods of MongoCollection where you supply a Mongo query
  • LINQ: the current version of the C# driver supports LINQ queries
  • Aggregation: querying in a functional style

We’ll be working on the same demo web app as before in this series so have it open in Visual Studio.

The Find methods

We’ve already seen FindOneById when locating a single Car object:

Car car = CarRentalContext.Cars.FindOneById(new ObjectId(id));

We can use the same method to complete the Cars view. Run the web app and navigate to /cars. For each record in the cars list there are 3 links: Edit, Details, Delete. We’ve implemented the Edit and Delete functions but not the Details yet. Let’s do it.

Add the following action methid on CarsController:

public ActionResult Details(string id)
{
	Car car = CarRentalContext.Cars.FindOneById(new ObjectId(id));
	return View(car.ConvertToViewModel());
}

Right-click “Details” and select Add View. Add the following View:

Add Details view for Car object

This will give you a simple table showing the details of the selected Car:

Car details show in Details view

The generic FindOneById method internally calls the FindOneByIdAs method which converts a document into an object. In case you need to convert a document into another object type then you can do it like this:

Customer customer = CarRentalContext.Cars.FindOneByIdAs<Customer>(new ObjectId("dfsdfs"));

This example is a bit stupid, I know, but you get the idea. You can use this method to construct different views of the same domain depending on the rights of the logged on user: FullCar, PartialCar, MinimalCar etc. The properties of each domain version will make sure that only the relevant fields are extracted from the document to construct the object:

LimitedCar limitedCar = CarRentalContext.Cars.FindOneByIdAs<LimitedCar>(new ObjectId("dfsdfs"));

There are other versions of the Find method that each return the first matching document:

  • FindOne(): finds the very first element in the collection
  • FindOne(IMongoQuery query): we’ve seen examples of how to construct an IMongoQuery. The Query object can be used to build query documents

Example: say you want to find the first Car whose rental costs less than or equal to 5:

IMongoQuery query = Query<Car>.LTE(c => c.DailyRentalFee, 5);
Car firstCheapCar = CarRentalContext.Cars.FindOne(query);

You can further refine your search in some overloads of the Find method where you can pass in a FindOneArgs object:

FindOneArgs findOneArgs = new FindOneArgs()
{
	Query = Query<Customer>.NE(c => c.Name, "samsung")
	, ReadPreference = new ReadPreference(ReadPreferenceMode.SecondaryPreferred)
};
Customer cust = CarRentalContext.Cars.FindOneAs<Customer>(findOneArgs);

Here we want to find the first customer whose name is NOT Samsung and we want to read from a secondary replica set member.

I encourage you to the explore the operators available on the Query object. Just type “Query.” or its generic counterpart in VisualStudio and IntelliSense will give you a long list of available options to build your query. There are so many that it’s not possible to cover them all in a single blog post. Keep in mind that you can always chain your queries with the And, Or and Not operators.

You can of course also locate several documents:

  • FindAll(): retrieves all documents in the collection
  • Find(IMongoQuery query): retrieves all documents matching the query

These two methods return a MongoCursor of type T. This object can be enumerated. Say you want to find all cars which have more than 3 doors:

IMongoQuery doorQuery = Query<Car>.GT(c => c.NumberOfDoors, 3);
MongoCursor<Car> bigCars = CarRentalContext.Cars.Find(doorQuery);

MongoCursor implements IEnumerable so you can perform the usual IEnumerable operations on it. E.g. the cursor can be enumerated:

foreach (Car c in bigCars)
{
     //do something
}

…or can also be turned into a List of cars:

List<Car> carsList = bigCars.ToList();

Like in the case of deferred operations in LINQ, the MongoDb Find query is not executed until you call an action on the cursor which enumerates it, such as iterating it or calling ToList.

Before the cursor is enumerated you can set extra options specific to MongoCursor, i.e. which are not part of IEnumerable. E.g. you can set the sort order as follows:

MongoCursor<Car> bigCars = CarRentalContext.Cars.Find(doorQuery);
IMongoSortBy sortByCars = SortBy<Car>.Descending(c => c.DailyRentalFee);
bigCars.SetSortOrder(sortByCars);
foreach (Car c in bigCars)
{
}

MongoCursor has a number of useful methods like that which all start with “Set”:

  • For pagination you can use the SetLimit(int limit) function in conjunction with SetSkip(int i). They behave like the Skip and Take LINQ operators
  • With SetFields you can provide the names of the fields to be returned in case you need to increase the performance. This has an effect similar to a limited SQL query: SELECT name, id FROM …

In case you want to know the number of matching documents without enumerating the cursor there’s the Count method:

MongoCursor<Car> bigCars = CarRentalContext.Cars.Find(doorQuery);
long howMany = bigCars.Count();

Let’s test the sorting method. In CarsController.Index we currently have the following contents:

public ActionResult Index()
{
	List<Car> carsInDb = CarRentalContext.Cars.FindAll().ToList();
        return View(carsInDb.ConvertAllToViewModels());
}

Change it to this so that we show the cars in a descending order by make:

public ActionResult Index()
{
	MongoCursor<Car> carsInDbCursor = CarRentalContext.Cars.FindAll();
        IMongoSortBy sortByCars = SortBy<Car>.Descending(c => c.Make);
	carsInDbCursor.SetSortOrder(sortByCars);
	return View(carsInDbCursor.ConvertAllToViewModels());
}

Run the app and you’ll see that the cars are indeed sorted according to the sort criteria. One thing to keep in mind is that the cursor is frozen as soon as the enumeration in begins by whatever enumeration operator. E.g. you cannot change the ordering of the cursor within a foreach loop.

LINQ

The MongoCollection object provides an AsQueryable extension method which is the starting point of building LINQ expressions:

MongoCursor<Car> carsInDbCursor = CarRentalContext.Cars.FindAll();
IEnumerable<Car> cars = carsInDbCursor.AsQueryable().Where(c => c.NumberOfDoors > 3);

Note that the Linq extensions in the MongoDb driver are evolving continuously. Not every query is possible to build by Linq that are found in the Query builder. In case you’re trying to run an unsupported Linq operator you’ll get an exception. You can read more about LINQ support in the driver here.

Aggregations

The aggregation mechanism in MongoDb provides not only search functions such as FindOne, FindAll etc. It also allows us to perform projections, groupings and summaries on documents to build a set of transformed documents.

You can combine transformation steps in a pipeline to build the final transformed document.

The entry point into creating aggregations is the Aggregate method of MongoCollection. The Aggregate method has a couple of overloads but the most flexible one accepts an AggregateArgs object. It returns an IEnumerable of BsonDocuments which is understandable. The transformed document is very unlikely to look like the original one so it cannot easily be deserialised into a domain object.

Taking the Car domain object we’ve been working with in this series we can have the following AggregateArgs to build a transformation pipeline:

AggregateArgs aggregateArgs = new AggregateArgs()
{
	Pipeline = new[]
	{
		new BsonDocument("$match", Query<Car>.LTE(c => c.DailyRentalFee, 10).ToBsonDocument())
		, new BsonDocument("$match", Query<Car>.GTE(c => c.DailyRentalFee, 3).ToBsonDocument())
		, new BsonDocument("$sort", new BsonDocument("DailyRentalFee", 1))
	}
};
IEnumerable<BsonDocument> documents = CarRentalContext.Cars.Aggregate(aggregateArgs);

This is a simple query but it’s a good starting point. We want to find the cars whose rental fee lies between 3 and 10 and then sort the documents by the rental fee in ascending order. For descending order use -1. This is also a good example to show that the transformation pipeline can be extended as much as you want in the BsonDocument array.

Aggregations can become quite involved. You’ll find lots of great examples on this website.

In the next post we’ll look at storing files in MongoDb.

View the posts related to data storage here.

About Andras Nemes
I'm a .NET/Java developer living and working in Stockholm, Sweden.

One Response to MongoDB in .NET part 8: queries

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

ultimatemindsettoday

A great WordPress.com site

Elliot Balynn's Blog

A directory of wonderful thoughts

HarsH ReaLiTy

A Good Blog is Hard to Find

Softwarearchitektur in der Praxis

Wissenswertes zu Webentwicklung, Domain-Driven Design und Microservices

Technology Talks

on Microsoft technologies, Web, Android and others

Software Engineering

Web development

Disparate Opinions

Various tidbits

chsakell's Blog

WEB APPLICATION DEVELOPMENT TUTORIALS WITH OPEN-SOURCE PROJECTS

Once Upon a Camayoc

Bite-size insight on Cyber Security for the not too technical.

Guru N Guns's

OneSolution To dOTnET.

Johnny Zraiby

Measuring programming progress by lines of code is like measuring aircraft building progress by weight.

%d bloggers like this: