Issue with data return in MongoDB

Question

I am facing a join issue in c# but I implement a join with MongoDB query successfully.

I created a database and create two collections.

MongoDB database and join

> db.childDocument.find().pretty()
{
        "_id" : 1,
        "sku" : "almonds",
        "description" : "product 1",
        "instock" : 120
}
{ "_id" : 2, "sku" : "bread", "description" : "product 2", "instock" : 80 }
{
        "_id" : 3,
        "sku" : "pecans",
        "description" : "product 4",
        "instock" : 70
}
{ "_id" : 4, "sku" : "rms", "description" : "product5", "instock" : 80 }
> db.masterDocument.find().pretty()
{ "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 }
{ "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 }
{ "_id" : 3, "item" : "rms", "price" : 56, "quantity" : 3 }

I am implementing a join functionality in MongoDB successfully

> db.masterDocument.aggregate([{ $lookup:{ from:"childDocument",localField:"item",foreignField:"sku",as:"data"  }  }])
{ "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2, "data" : [ { "_id" : 1, "sku" : "almonds", "description" : "product 1", "instock" : 120 } ] }
{ "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1, "data" : [ { "_id" : 3, "sku" : "pecans", "description" : "product 4", "instock" : 70 } ] }
{ "_id" : 3, "item" : "rms", "price" : 56, "quantity" : 3, "data" : [ { "_id" : 4, "sku" : "rms", "description" : "product5", "instock" : 80 } ] }

but I am trying to join in C# but not properly return a data in Postman:

I want to below output in ASP.NET Core Web API:

{ "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2, "data" : [ { "_id" : 1, "sku" : "almonds", "description" : "product 1", "instock" : 120 } ] }
{ "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1, "data" : [ { "_id" : 3, "sku" : "pecans", "description" : "product 4", "instock" : 70 } ] }
{ "_id" : 3, "item" : "rms", "price" : 56, "quantity" : 3, "data" : [ { "_id" : 4, "sku" : "rms", "description" : "product5", "instock" : 80 } ] }

Web API

public BookDB(IOptions<BooksConfiguration> settings)
{
    _settings = settings.Value;
    var ConnectionString = new MongoClient(_settings.ConnectionString);
    var database = ConnectionString.GetDatabase("document");
    _childDocument = database.GetCollection<childDocument>("childDocument");
    _masterDocument = database.GetCollection<masterDocument>("masterDocument");
}

public async Task<List<masterDocument>> getBookDescription()
{
    //var query = _childDocument.Aggregate().Match(e => e.sku == "almonds").ToList();

    var masterdocument =
                     from u in _childDocument.AsQueryable<childDocument>()
                     join a in _masterDocument.AsQueryable<masterDocument>() on u.sku equals a.item
                     //join c in _childDocument.AsQueryable<childDocument>() on c._id equals c._id   //master id
                     select new masterDocument()
                     {
                         _id = u._id,
                         item = a.item,
                         price = a.price,
                         quantity = a.quantity,
                         //_id = u._id,
                         //childDocuments = new List<childDocument>
                         //{
                         //    new childDocument { sku = a.item,description = "",instock = 0 }
                         //}
                         //childDocuments =  u.childDocuments
                         //childDocuments = (List<childDocument>)data
                         //childDocuments = new List<childDocument>()
                         //{
                         //    new childDocument()
                         //    {
                         //        //sku = x.item,
                         //        //description = "",
                         //        //instock = 0

                         //    }
                         //}
                         //childDocuments = (List<childDocument>)data
                         // childDocuments =            //array return
                     }
                    ;
            
    var alldocument = masterdocument.ToList();

    return alldocument;
}

Classes

public class masterDocument
{
    public double _id { get; set; }
    public string item { get; set; }
    public double price { get; set; }
    public double quantity { get; set; }
    public List<childDocument> childDocuments { get; set; }
}

public class childDocument
{
    public double _id { get; set; }
    public string sku { get; set; }
    public string description { get; set; }
    public int instock { get; set; }
}

Current output in Postman:

[
    {
        "_id": 1,
        "item": "almonds",
        "price": 12,
        "quantity": 2,
        "childDocuments": null
    },
    {
        "_id": 3,
        "item": "pecans",
        "price": 20,
        "quantity": 1,
        "childDocuments": null
    },
    {
        "_id": 4,
        "item": "rms",
        "price": 56,
        "quantity": 3,
        "childDocuments": null
    }
]

I am trying a join in c# but not properly working return data.

Please help.

Answer 1

Solution 1: With LINQ query

You need to .GroupBy MasterDocument._id as below:

var alldocument =
            (from a in _masterDocument.AsQueryable<masterDocument>()
            join u in _childDocument.AsQueryable<childDocument>() on a.item equals u.sku
            select new { master = a, child = u } into au
            group au by au.master._id into masterGroup
            select new masterDocument()
            {
                _id = masterGroup.Key,
                item = masterGroup.First().master.item,
                price = masterGroup.First().master.price,
                quantity = masterGroup.First().master.quantity,
                childDocuments = masterGroup.Select(x => x.child).ToList()
            })
            .ToList();

---

Solution 2: With BsonDocument (Export query from MongoDB Compass)

If you are unsure how to write the MongoDB query in C#, you can export your query to C# via MongoDB.

Pre-requisites

public class masterDocument
{
    ...

    [BsonElement("data")]
    public List<childDocument> childDocuments { get; set; }
}

PipelineDefinition<masterDocument, masterDocument> pipeline = new BsonDocument[]
{
    new BsonDocument("$lookup",
        new BsonDocument
        {
            { "from", "childDocument" },
            { "localField", "item" },
            { "foreignField", "sku" },
            { "as", "data" }
        })
};

var alldocument = _masterDocument
    .Aggregate(pipeline)
    .ToList();

---

Solution 3: With AggregateFluent

Pre-requisites

public class masterDocument
{
    ...

    [BsonElement("data")]
    public List<childDocument> childDocuments { get; set; }
}

var alldocument = _masterDocument
    .Aggregate()
    .Lookup<childDocument, masterDocument>(
        "childDocument", 
        "item", 
        "sku",
        "data")
    .ToList();

---

Result