Nested lookup 'join' using .net driver

Working with Data Drivers & ODMs
#1

Hello,
i want to know how to do complicated nested lookup in c#

see this code for example:
Inbox.cs

public class Inbox : TDocument
{
    public string Name {get;set;}
    public List<MongoDBRef> WorkOrdersRef {get;set;}
    public List<InboxRule> Rules {get;set;}

    [BsonIgnore] public List<WorkOrder> WorkOrders {get;set;}
}


InboxRule.cs

public InboxRule : TDocument
{
    public MongoDBRef ServiceRef {get;set;}
    public MongoDBRef WorkOrderStatusRef {get;set;}


    [BsonIgnore] public Service Service {get;set;}
    [BsonIgnore] public WorkOrderStatus WorkOrderStatus {get;set;}
}

how to perform nested lookup or join with one call to return an array of Inboxes with its Rules and the Rules other objects such as Service and WorkOrderStatus

#2

Hi @Moataz_Al-HANASH , welcome!

Based on the class mapping, it looks like Rules is an embedded list inside of Inbox. You should be able to query any Inbox or multiple Inbox and be able to retrieve the Rules as well.

Also you may find $lookup aggregation stage a useful resource.

If you still have further questions, it would be useful to provide:

  • Example documents
  • Desired results
  • MongoDB .NET/C# driver version
  • MongoDB server version
  • Attempts that you’ve tried

Note: I noticed that you have MongoDBRef as type, if that refers to DBRefs depending on the use case you may find a manual reference of _id would be easier to use. See also database references.

Regards,
Wan.

#3

furthermore :

A)How to perform Join using LINQ or ‘Aggregation’ to fill the property WorkOrders based on the references in the property WorkOrdersRef

B)How to perform Join using LINQ or ‘Aggregation’ to fill the properties of the embedded document InboxRule known as Service and WorkOrderStatus

Or simply, how to populate the [BsonIgnore] -properties for an Inbox object and it’s embedded document property known as Rules

#4

Thanks for our replay.

that is right , Rules are embedded inside of Inbox object
but if you notice Service and WorkOrderStatus are NOT embedded inside Rules , i want to retrieve an Inbox object joined with WorkOrder and the embedded Rules should also be joined with Service and WorkOrderStatus and all mapped back to Inbox object

so basiclly im looking for something like this:

Inbox {
   WorkOrders : [
               {
                   WorkOrderId : "123",
                    ...
               }
               {
                   WorkOrderId : "445",
                    ...
               }
    ],
   Rules : [
                         { 
                            Service : {
                                      Name : "Service 1"
                                           } ,
                            WorkOrderStatus : {
                                      Name : "Status 1"
                                          }  
                         }
                         { 
                            Service : {
                                      Name : "Service 2"
                                           } ,
                            WorkOrderStatus : {
                                      Name : "Status 2"
                                          }  
                         }
    ]
}

i tried this in C# to get the results needed but failed:

_dbContext.DbSet<Inbox>().Aggregate()
                     .Lookup("WorkOrder", inboxKeyToWorkOrders, woKey, navProp) // to fill WorkOrders property in Inbox
                     .Unwind(a => a.Rules, new AggregateUnwindOptions<InboxRule>() { PreserveNullAndEmptyArrays = true })
                     .Lookup("Service", inboxRuleKeyToService, serviceId, inboxRuleNavPropService) // to fill Service property in InboxRule
                     .Lookup("WorkOrderStatus", inboxRuleKeyToStatus, statusId, inboxRuleNavPropStatus) // to fill WorkOrderStatus property in InboxRule
                     .ToList();

but the problem is the above code does not return list of Inbox , it returns list of InboxRule

how to perform nested lookup and map results to Inbox object ?

Iam using 2.10.4 MongoDB .NET/C# driver
also using Atlas 4.2.6 as my MongoDb server

Thanks in advance

#5

@wan
i added a replay sir

#6

Hi @Moataz_Al-HANASH ,

Based on your C# code snippet, there are 4 collections involved (Inbox, WorkOrder, Service, and WorkOrderStatus), and you’re trying to perform multiple lookups to combine them.

Could you clarify the question by providing example documents for the collections ?
Also, what does the current output document that you’re getting ?

Regards,
Wan.

#7

@wan

This is the example documents

Inbox Document

    {
              _id : ObjectId('3213asdaadda'),
              Name : 'InboxName',
              WorkOrdersRef : [
                              0:ObjectId('1'),
                              1:ObjectId('2'),
                              2:ObjectId('3'),
                          ],
              Rules : [
                     {
                          ServiceRef: ObjectId('2134'),
                          WorkOrderStatusRef: ObjectId('213asd4'), 
                     },
                     {
                          ServiceRef: ObjectId('21341523'),
                          WorkOrderStatusRef: ObjectId('2112131134'), 
                     }
                  ]
    }

WorkOrder document:

    {
         _id:Object('1'),
         ... *some other not related to the question fields and arrays*
    }
    {
         _id:Object('2'),
         ... *some other not related to the question fields and arrays*
    }
    {
         _id:Object('3'),
         ... *some other not related to the question fields and arrays*
    }

WorkOrderStatus

    {
           _id : ObjectId('12313'),
           Name : 'Initiated'
    },
{
       _id : ObjectId('12313'),
       Name : 'Closed'
}

as for second question , i dont get a result yet

#8

Hi @Moataz_Al-HANASH,

Given the example document for inbox, workorder, and workorderstatus collections, you could perform an aggregation as below example:

var collection = database.GetCollection<Inbox>("inbox");            
var docs = collection.Aggregate()
                     .Lookup("workorder", "WorkOrdersRef", "_id", "WorkOrders")
                     .Unwind("Rules")
                     .Lookup("workorderstatus", "Rules.WorkOrderStatusRef", "_id", "Rules.WorkOrderStatus")
                     .ToList();

The use of Lookup and Unwind will change the class shape, I’d recommend to either define a new class that matches the result/output type, or just use BsonDocument .

Regards,
Wan.

#9

@wan
Thanks for your replay

can you please provide me a way to map the result to ‘Inbox’ class ?