I have got a working code to join two mongo collections using $lookup.
[HttpGet("mongojoin")]
public IActionResult GetMongoJoinCollection([FromQuery(Name = "schoolId")] Guid schoolId)
{
var tabAssessmentCollection = mongoDatabase.GetCollection<TabAssessmentDocument>(MongoSettings.TabAssessmentDocumentName);
var tabComponentCollection =
mongoDatabase.GetCollection<TabComponentDocument>(MongoSettings.TabComponentDocumentName);
var resultMongo = tabAssessmentCollection
.Aggregate()
.Match(d => d.SchoolId.Equals(schoolId))
.Lookup(
foreignCollection: tabComponentCollection,
localField: ta => ta.ComponentId,
foreignField: tc => tc.TblId,
@as: (TabAssessmentDocument ta) => ta.Component
)
.Unwind(d => d.Component)
.ToList();
var resultList = new List<TabSubjectDocumentViewModel>();
foreach (var row in resultMongo)
{
resultList.Add(new TabSubjectDocumentViewModel()
{
Id = row["TblId"].AsGuid,
SchoolId = row["SchoolId"].AsGuid,
Name = row["Name"].AsString,
ShortName = row["ShortName"].AsString,
Active = row["Active"].AsNullableBoolean,
TabComponent = row["Component"]["Name"].AsString
});
}
return Ok(resultList);
}
}
public class TabSubjectDocumentViewModel
{
public Guid Id { get; set; }
public Guid? SchoolId { get; set; }
public string Name { get; set; }
public string ShortName { get; set; }
public bool? Active { get; set; }
public string TabComponent { get; set; }
}
public class TabAssessmentDocument
{
[BsonId]
[BsonRepresentation(BsonType.ObjectId)]
public string Id { get; set; }
public Guid TblId { get; set; }
public Guid? SchoolId { get; set; }
public string Name { get; set; }
public string ShortName { get; set; }
public Guid? ComponentId { get; set; }
public bool? Active { get; set; }
}
public class TabComponentDocument
{
[BsonId]
[BsonRepresentation(BsonType.ObjectId)]
public string Id { get; set; }
public Guid TblId { get; set; }
public Guid? SchoolId { get; set; }
public string Name { get; set; }
public string ShortName { get; set; }
public string ComponentType { get; set; }
public bool? Active { get; set; }
}
I tried using the Project method just after the .Unwind(d => d.Component), but, then I was not getting the $lookup collection data (TabComponent) in the Bsondocument. If I could Serialize this to a List, how should I be doing it ? Can anyone tell me how I should using the Projection and Serialization here ?