MongoDB join using Linq

Question

CommentCollection
{
   "_id":"5b63f0f23846b70011330889",
   "CommentType":"task",
   "EntityReferenceId":"6082ef25-6f9a-4874-a832-f72e0f693409",
   "CommentLink":null,
   "EntityName":"task2",
   "participants":[
                  ObjectId("52ffc4a5d85242602e000000"),
                  ObjectId("52ffc4a5d85242602e000001")    
 ],
"Threads":[
  {
     "_id":"69bcef71-3695-4340-bdec-4a6e4c58c490",
     "CommentType":"task",
     "UserId":ObjectId("52ffc4a5d85242602e000000"),         
     "CommentByUserType":"Admin",
     "EntityReferenceId":"6082ef25-6f9a-4874-a832-f72e0f693409",
     "Content":"fdffd",
     "ProjectName":null,
     "PostedDate":"2018-08-03T13:03:05.939Z",
     "Active":true,
     "Attachment":[

     ]
  }

another Collection is

userCollection
{  
     "Id":ObjectId("52ffc4a5d85242602e000000"),
     "Name":"Pms Admin",
     "Email":"[email protected]",
     "Type":"Admin",
     "UserId":"6082ef25-6f9a-4874-a832-f72e0f693409",
     "UserImage":"6082ef25-6f9a-4874-a832-f72e0f693409"  
}

In the CommentCollection there is an array of "participants" which is storing the id's of users (from usercollection).

My requirement is join these two collections for getting user details in my asp.net core project(Linq).Participants contains list of id's

Answer 1

In Mongo shell you would use $lookup which can be used on arrays like in this example and your query could look like this:

db.Comment.aggregate([
    {
        $lookup: {
            from: "user",
            localField: "participants",
            foreignField: "Id",
            as: "participants"
        }
    }
])

Which simply replaces participants with array of objects from second collection:

{
    "_id" : "5b63f0f23846b70011330889",
    "CommentType" : "task",
    "EntityReferenceId" : "6082ef25-6f9a-4874-a832-f72e0f693409",
    "CommentLink" : null,
    "EntityName" : "task2",
    "participants" : [
        {
            "_id" : ObjectId("5b6e875b9d52833fbe9879c2"),
            "Id" : ObjectId("52ffc4a5d85242602e000000"),
            "Name" : "Pms Admin",
            "Email" : "[email protected]",
            "Type" : "Admin",
            "UserId" : "6082ef25-6f9a-4874-a832-f72e0f693409",
            "UserImage" : "6082ef25-6f9a-4874-a832-f72e0f693409"
        }
    ],
    "Threads" : //...
}

In C# you can express that using Lookup syntax. First option allows you to get a list of BsonDocument type which simply skips type checking:

var collection = db.GetCollection<Comment>("Comment"); 
List<BsonDocument> result = collection.Aggregate()
                       .Lookup("user", "participants", "Id", "participants")
                       .ToList();

The reason why you can't use regular LINQ join here is that actually you're compaing an array with a scalar value (that's what should be in equals part of join). However if you need strongly typed result instead of BsonDocuments you can use different version of Lookup method which takes types and expressions instead of strings. So you need another class for $lookup result which might be solved using inheritance:

public class Comment
{
    public string _id { get; set; }
    public string CommentType { get; set; }
    public string EntityReferenceId { get; set; }
    public string CommentLink { get; set; }
    public string EntityName { get; set; }
    public ObjectId[] participants { get; set; }
    public Thread[] Threads { get; set; }
}

public class CommentWithUsers : Comment
{
    public User[] Users { get; set; }
}

Then you can get a list of CommentWithUser:

var comments = mydb.GetCollection<Comment>("Comment");
var users = mydb.GetCollection<User>("user");

List<CommentWithUser> result = comments.Aggregate()
                                       .Lookup<Comment, User, CommentWithUsers>(
                                            users, 
                                            x => x.participants, 
                                            x => x.Id, 
                                            x => x.Users).ToList();