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":"pms@xtrastaff.com",
     "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

score 7 · Accepted Answer · answered Aug 11 '18 at 07:57

7

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" : "pms@xtrastaff.com",
            "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();

answered Aug 11 '18 at 07:57

mickl

48,568
9
60
89

Thank u mickl,I have one doubt, If I am adding new participants (objectId) to the _participiants_array in comment collection.I want to insert the ObjectId if it does not exist in this array,if its exist please ignore it.how its possible?.I used AddtoSet() but its showing some serialization isues. – Ajas Aju Aug 13 '18 at 12:09
Yes, you should be able to do it using AddToSet. please open new question if the issue still exists – mickl Aug 13 '18 at 15:40
Thank you Mickl,I have got the result.I used my update set like `var updateSet = Builders.Update.AddToSet(x=>x.participants, ParticipentObjId);` – Ajas Aju Aug 14 '18 at 06:01
Hi can you please help me for this question.https://stackoverflow.com/questions/52550759/store-json-string-as-mongodb-array-in-c-sharp – Ajas Aju Sep 28 '18 at 07:51
Any reason you can think of why I'm getting `Unable to determine the serialization information for x => x.Users` if I put Users directly on the Comment class itself (getting rid of CommentWithUsers)? – l p May 02 '20 at 22:58
@lp the output of `$lookup` always returns "joined" entity as a separate field so your c# type has to have the same model, please open a separate question if you can't figure it out – mickl May 02 '20 at 23:32
@mickl, I guess it's because I have the BsonIgnore attribute on the property (obviously I don't want that relational copy written back to the database). – l p May 04 '20 at 16:54
For anyone else wondering, I created my own attribute `[DontSerialize]` using `BsonIgnoreIfNull` [source](https://github.com/mongodb/mongo-csharp-driver/blob/010e7ee46b085cdd3762894ece9e2d258b66ab0d/src/MongoDB.Bson/Serialization/Attributes/BsonIgnoreIfNullAttribute.cs) as a starting point and in the Apply method I put `memberMap.SetShouldSerializeMethod(o => false);` – l p May 04 '20 at 17:30
@mickl I have been following this approach, but the problem is I need to create a model for every different join. Is there some help with that? – Abhishek Tewari May 31 '20 at 16:10
@AbhishekTewari there's another version of `Lookup` and you can use `BsonDocument` instead of strongly typed approach – mickl May 31 '20 at 16:15
@mickl Thanks. But is there any way to prepare a strongly typed db layer for MongoDB in c# to withstand complex queries? Moreover, for complex queries, is it better to follow the strongly typed approach or used the `BsonDocument` instead? – Abhishek Tewari May 31 '20 at 16:19
It really depends on your projects infrastructure. Strongly-typed is safer because c# will be able to verify your code in case you refactor and something breaks. The downside is that you need to have these additional classes – mickl May 31 '20 at 16:31

MongoDB join using Linq

1 Answers1