Left Join in MongoDB using the C# driver and LINQ

Views: 1477
Comments: 0
Like/Unlike: 0
Posted On: 31-May-2020 00:43 

Share:   fb twitter linkedin
Smith
Participant
14 Points
7 Posts


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")
    .Unwind("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.

 

0 Comments
  
 Log In to Chat