Joining Collections in MongoDB using the C# driver and LINQ

MongoDB using the C# driver and LINQ

Flexible IoT platforms need a flexible database that can handle dynamic data, and can scale well.

MongoDB is probably the most popular NoSql database out there and is relatively easy to use in conjunction with .Net with the official driver. It’s a document DB which has many advantages in terms of schema-less/dynamic properties. The downside is that it is very difficult to combine data from various entities. In fact, until recently it was impossible to do this in the database itself.

The MongoDB team recently introduced the new $lookup feature in its aggregation platform. This feature allows you to join two collections together, creating an array of type B in returned objects A for the first time.

Here’s a short post showing how the C# driver supports this new functionality when combined with LINQ.

Why MongoDB makes sense for an IoT platform

Why even use MongoDB in IoT platform?

MongoDB has two main strengths for IoT:
  1. IoT applications tend to generate a lot of sensor data – MongoDB scales for big data
  2. There is currently no standard for IoT data, no one protocol that all IoT devices conform to. This means that as an IoT platform, you have to be ready to get all sorts of schemas from your devices. Mongo has great support for dynamic data. Just throw anything at it and it will allow you to work with it. In a SQL server it would be a huge hassle to add support for totally dynamic properties.

Note: These advantages are not unique to MongoDB, many Document based NoSQL databases have them, however MongoDB is by far the most used of these kinds of databases.

Prerequisites

Mongo version

The $lookup operator was added to MongoDB in version 3.2, so make sure you are using that version of the database. To check what version of MongoDB you are using you can use the command db.version() in your mongo client of choice.

Driver version

The C# driver must be at least 2.2.4. This is very important as version 2.2.3 has a bug that did not allow this to work.

Examples

The Mongo $lookup Operator

From the Official documentation:

 

So for example if I wanted to join my devices with a different collection of sensor readings, I would use this command:

db.devices.aggregate([{ “$lookup” : { “from” : “readings”, “localField” : “_id”, “foreignField” : “deviceId”, “as” :“joinedReadings”} }])

and the result will be something like this:

The Mongo $lookup Operator

Note: this is a left outer join, it will return all devices.

Simple C# LINQ Example

In C# you can use the LINQ JOIN to generate this $lookup:

Simple C# LINQ Example

Example with Filtering on left Join

As we can see you are passing in an IQueryable to the join for the $lookup, so you can also add other LINQ expressions to it before doing the join. Let’s update the example but this time only query for connected devices.

Example with Filtering on left Join

Unfortunately, there is no way to filter on the right side of the join (readings in our example). This is a limitation of the MongoDB implementation.

Conclusion

Having the ability to join collections is a wonderful addition to your toolbox as a MongoDB developer. However, the first solution should probably be to try to model your data in a way which does not need this ability. It has both performance implications and limitations on what you can do while joining. That being said, support through LINQ and C# is good once you figure out how to do it.

So, what does it take to successfully break down the silos and orchestrate data from different IoT devices and IT systems? Discover the essential building blocks of an orchestrated IoT application in this whitepaper.

envelope-subscribe-icon
Don't miss out on the latest
Get notified on Industry updates. We promise not to spam