Left Join in MongoDB using the C# driver and LINQ

Views: 7208
Comments: 2
Like/Unlike: 1
Posted On: 31-May-2020 00:43 

Share:   fb twitter linkedin
Smith
Participant
42 Points
10 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.

 

2 Comments

Rohit Patil
will this work if my collections are sharded ?

beginer
MongoDB Driver should work with sharded collections. We can refer here: https://mongodb-documentation.readthedocs.io/en/latest/ecosystem/tutorial/use-csharp-driver.html#gsc.tab=0

The available connection modes are automatic (the default), direct, replica set, and shardrouter. The rules for connection mode are as follows:

If a connect mode is specified other than automatic, it is used.
If a replica set name is specified on the connection string (replicaset), then replicaset mode is used.
If their is only one server listed on the connection string, then direct mode is used.
Otherwise, discovery occurs and the first server to respond determines the connection mode.
  
 Log In to Chat