Searching by a field in another collection in mongodb c#

Question

I've been looking at the aggregation and lookup functions of mongo to figure out how to search by a field in a different collection but could not work it out.

The data structure looks like this:

public class User
{ 
    public string Id
    public string Name
    public string GroupId
}

and

public class Group
{
    public string Id
    public string Name
}

What I'm trying to accomplish here is: return a list of Users with Group name "xyz".

Below is my returned IExecutable with no matching field for the group name.

return userCollection.Aggregate(new AggregateOptions
            {
                Collation = new Collation("en",
                    strength: CollationStrength.Primary)
            })
            .Match(u=>u.Name.Contains("xyz")
            .AsExecutable();

The provide code looks unrelated to the initial question. You should use Lookup, see https://stackoverflow.com/questions/35638372/how-to-lookup-with-mongodb-c-sharp-driver — dododo, Sep 14 '22 at 16:26

score 0 · Answer 1 · answered Sep 15 '22 at 06:57

You can use a $lookup stage to achieve this; this stage looks up information in another collection and adds the matching documents to an array for further processing. In order to use it in a type-safe manner, create a class with a groups enumeration, e.g.:

public class UserWithGroups : User
{
    public IEnumerable<Group> Groups { get; set; }
}

In addition, you need to create a collection for the groups, e.g.:

var grpCollection = db.GetCollection<Group>("groups");

Then you can extend your statement as follows:

return userCollection.Aggregate(new AggregateOptions
    {
        Collation = new Collation("en",
            strength: CollationStrength.Primary)
    })
    .Match(u=>u.Name.Contains("xyz")
    .Lookup<User, Group, UserWithGroups>(
        grpCollection, 
        x => x.GroupId, 
        x => x.Id, 
        x => x.Groups)
    .Match(x => x.Groups.Any(y => y.Name.Contains("abc")))
    .AsExecutable();

If you need to query for group names often, you can also check out the Extended Reference pattern and store the group name with the user.

Searching by a field in another collection in mongodb c#

1 Answers1