If you’re using MongoDB, you know as a document database it doesn’t provide the “join” feature you’ve come to rely on in standard relation databases (sql server, mySql, postgres). In many instances, you’re not using Mongo as a replacement for your existing data structure / methods – but it would be nice to relate some documents (without embedding everything [potentially duplicating data] into a single document.
I am a big fan of MongoDB and the .net / c# driver. Below is a method I’ve found that works well for relating different documents using MongoDBRef. In my scenario – which I’m sure many have better methods – I’m using a property to keep a list of related documents – then a method to retrieve the related documents if needed.
I’m my below example, I’m creating trains, train cars, and passengers – then relating them. In short: I’m using IList<MongoDBRef> to store the list, and FetchDBRefAs to get the documents again.
This works really well and is flexible. I’ve tested this against many records and have found that if you’re looking to get thousands of records with their related thousands of records – you won’t experience the same performance as a standard relational db join. But for a few records (hundreds in my testing) it’s fast and efficient. I’m also using .AsParallel() on the query – this gained me about 10%+ performance.
One note: if you’re allowing Mongo to generated the document id on insertion, queuing up records in a bulk operation (ie: InitializeOrderedBulkOperation) you’ll soon realize there isn’t an id yet to use for the MongoDBRef.
Comment if you’ve found a better method you prefer for linking documents.
My example:
using MongoDB.Bson; using MongoDB.Driver; using MongoDB.Driver.Linq; using System; using System.Collections.Generic; using System.Diagnostics; using System.Linq; namespace mongoDBlinked { public class passenger { public ObjectId Id { get; set; } public string Name { get; set; } } public class trainCar { public ObjectId Id { get; set; } public string carNo { get; set; } public string serial { get; set; } public string note { get; set; } public IList<MongoDBRef> passengers { get; set; } public IList<passenger> GetPassengers(MongoDatabase db) { if (passengers.Count == 0) return new List<passenger>(); IList<passenger> tpass = new List<passenger>(); foreach (var related in passengers) { tpass.Add(db.FetchDBRefAs<passenger>(related)); } return tpass; } } public class train { public ObjectId Id { get; set; } public string trainNo { get; set; } public string note { get; set; } public IList<MongoDBRef> trainCars { get; set; } public IList<trainCar> relTrainCards { get; set; } public IList<trainCar> GetTraincars(MongoDatabase db) { if (trainCars.Count == 0) { return new List<trainCar>(); } IList<trainCar> tcars = new List<trainCar>(); foreach (var related in trainCars) { tcars.Add(db.FetchDBRefAs<trainCar>(related)); } return tcars; } } internal class Program { private static void Main(string[] args) { var server = new MongoClient("mongodb://localhost").GetServer(); var database = server.GetDatabase("testmongodb"); var collection = database.GetCollection<trainCar>("trainCars"); var trainCollection = database.GetCollection<train>("trains"); var peopleCollection = database.GetCollection<passenger>("passengers"); //add some data for (int t = 0; t < 20; t++) { train newTrain = new train(); newTrain.note = "nyc" + t.ToString(); newTrain.trainNo = "345"; newTrain.trainCars = new List<MongoDBRef>(); //add some cars: for (int i = 0; i < 50; i++) { trainCar tcar = new trainCar(); tcar.carNo = "0" + i.ToString(); tcar.note = "Needs new brakes"; tcar.serial = "1234"; tcar.passengers = new List<MongoDBRef>(); for (int p = 0; p < 3; p++) { passenger pass = new passenger(); pass.Name = "name" + p.ToString(); peopleCollection.Insert(pass); tcar.passengers.Add(new MongoDBRef(peopleCollection.Name, pass.Id)); } collection.Insert(tcar); newTrain.trainCars.Add(new MongoDBRef(collection.Name, tcar.Id)); } trainCollection.Insert(newTrain); } Console.WriteLine("saved"); Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); //get the data //asparallel gained me about 15% of qry time var query = from v in trainCollection.AsQueryable<train>().AsParallel() select v; foreach (train aTrain in query) { Console.WriteLine("train: " + aTrain.note); foreach (trainCar tcar2 in aTrain.GetTraincars(database)) { if (tcar2 != null) { Console.WriteLine("car: " + tcar2.carNo); foreach (passenger pass in tcar2.GetPassengers(database)) { Console.WriteLine("car: " + tcar2.carNo + " pass: " + pass.Name); } } } } Console.WriteLine("trains loaded"); stopWatch.Stop(); TimeSpan ts = stopWatch.Elapsed; string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10); Console.WriteLine("RunTime " + elapsedTime); Console.ReadLine(); } } }
Chris Bitting 