Blog

  • Joining Collections in MongoDB using the C# driver and LINQ

IntroA flexible IoT platforms needs a flexible database that can handle dynamic data, and can scale well.

MongoDB is probably the most popular NoSql Database out there and it’s relatively easy to use in conjunction with .Net with the official driver. It is 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, up 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 is the first time you can join two collections together, creating an array of type B in returned objects A.

I wanted to write a short post showing how the C# driver supports this new functionality when combined with LINQ.

Why MongoDB makes sense for an IOT platformWhy 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.

PrerequisitesMongo versionThe $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 versionThe 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.

ExamplesThe Mongo $lookup OperatorFrom the Official documentation:

{

$lookup:

{

from: <collection to join>,

localField: <field from the input documents>,

foreignField: <field from the documents of the “from” collection>,

as: <output array field>

}

}

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:

Joining Collections in MongoDB using the C# driver and LINQ

Note: this is a left outer join, it will return all devices.
Simple C# LINQ ExampleIn C# you can use the LINQ JOIN to generate this $lookup:
Joining Collections in MongoDB using the C# driver and LINQ

Example with Filtering on left JoinAs 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.

Joining Collections in MongoDB using the C# driver and LINQ

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.

ConclusionHaving 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.