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
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.
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.
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
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
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 ?
{
_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'
}
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 .