Left Join in MongoDB using the C# driver and LINQ

MongoDB is the popular NoSql database out there and is comparatively easy to use in conjunction with .Net and .Net Core with the official driver. MongoDB is not a relational database, but we can do a left outer join by using the $lookup aggregation stage. In this article we will see left outer join as Mongo $lookup Operator and with C# driver and LINQ syntax.

Prerequisites

• Mongo version: The $lookup operator was added to MongoDB in version 3.2, so make sure you are using latest version of the database.
• Driver version: The C# driver must be at least 2.2.4. Used version 2.10.3

Example

Suppose, we have two collections orders and inventory documents as follow:

db.orders.insert([
   { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
   { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
   { "_id" : 3  }
])

db.inventory.insert([
   { "_id" : 1, "sku" : "almonds", description: "product 1", "instock" : 120 },
   { "_id" : 2, "sku" : "bread", description: "product 2", "instock" : 80 },
   { "_id" : 3, "sku" : "cashews", description: "product 3", "instock" : 60 },
   { "_id" : 4, "sku" : "pecans", description: "product 4", "instock" : 70 },
   { "_id" : 5, "sku": null, description: "Incomplete" },
   { "_id" : 6 }
])

Join Syntax with the Mongo $lookup Operator (From the Official documentation)

db.orders.aggregate([
   {
     $lookup:
       {
         from: "inventory",
         localField: "item",
         foreignField: "sku",
         as: "inventory_docs"
       }
  }
])

In C# with MongoDB Driver and LINQ

Helper methods:

private IMongoDatabase GetDatabase()
{
    MongoClient mongoClient = new MongoClient("MongoDBConnectionString");
    return mongoClient.GetDatabase("MongoDBName")
}

public IMongoCollection<BsonDocument> GetCollection(string collection)
{
    return GetDatabase().GetCollection<BsonDocument>(collection);
}

public IMongoCollection<TDocument> GetCollection<TDocument>(string collection)
{
    return GetDatabase().GetCollection<TDocument>(collection);
}

Without LINQ:

IMongoCollection<BsonDocument> mongoCollection = mongoDBService.GetCollection("orders");
var result = mongoCollection.Aggregate()
    .Lookup("inventory", "item", "sku", @as: "inventory_docs")
    .As<OrderWithInventory>()
    .ToEnumerable();

With LINQ:

var orders = mongoDBService.GetCollection<Orders>("orders");
var inventory = mongoDBService.GetCollection<Inventory>("inventory");

Lambda Expression:

var result = orders.AsQueryable()
    .Join(inventory.AsQueryable(), o => o.Item, i => i.sku,
    (x, y) => new { order = x, inventory = y })
    .SelectMany(
        x => x.inventory.DefaultIfEmpty(),
        (x, y) => new OrderWithInventory
        {
            _id = x.order._id,
            Item = x.order.Item,
            Price = x.order.Price,
            Quantity = x.order.Quantity,
            inventory_docs = y
        });

Linq Query

var result = from o in orders.AsQueryable()
             join i in inventory.AsQueryable()
             on o.Item equals i.sku
             into joinedInventory
             select new OrderWithInventory
            {
                _id = o._id,
                Item = o.Item,
                Price = o.Price,
                Quantity = o.Quantity,
                inventory_docs = joinedInventory
            });

And operation will return something like following collections

{
   "_id" : 1,
   "item" : "almonds",
   "price" : 12,
   "quantity" : 2,
   "inventory_docs" : [
      { "_id" : 1, "sku" : "almonds", "description" : "product 1", "instock" : 120 }
   ]
}
{
   "_id" : 2,
   "item" : "pecans",
   "price" : 20,
   "quantity" : 1,
   "inventory_docs" : [
      { "_id" : 4, "sku" : "pecans", "description" : "product 4", "instock" : 70 }
   ]
}
{
   "_id" : 3,
   "inventory_docs" : [
      { "_id" : 5, "sku" : null, "description" : "Incomplete" },
      { "_id" : 6 }
   ]
}

Conclusion

Having the ability to join the collections is a amazing addition to our toolbox as a MongoDB developer. Hope this article will be helpful.