MongoDB selecting records which match value in array

Question

I am really very new to MongoDb, I have two Mongodb documents Role and User

ROLE

{"_id":"5d0124b858d49243306deaa2",   
"mdt":"2019-06-12T16:13:44.037Z",
"mby":"000000000000000000000000",
"IsDeleted":false,
"Name":"Manager"}

and my another document is User with below definition and user can have multiple roles.

USER

{ 
  "firstName" : "John",
  "lastName" : "Doe",
  "Role" : {"5d0124b858d49243306deaa2", "6d0125b858749243306deaa2", "9b0124a852d49245306deba2"} \\ Array of role Id's assigned to users
}

How I can query firstName and lastName of user with matching Roles using MongoDB .Net Driver and Linq.

May sounds funny but I tried little like below

public List<User> GetCaseAssigneesByRoles(string roles)
        {

            return User.Query.Find(User.Query.EQ(a=>a.FirstName, roles)).ToList();
        } 

Answer 1

here's a simple way of doing it with LINQ using the library MongoDB.Entities [disclaimer: i'm the author]

using MongoDB.Entities;
using MongoDB.Driver.Linq;
using System.Linq;

namespace StackOverflow
{
    public class Program
    {
        public class User : Entity
        {
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public string[] Roles { get; set; }
        }

        public class Role : Entity
        {
            public string Name { get; set; }
        }

        static void Main(string[] args)
        {
            new DB("test");

            var managerRole = new Role { Name = "Manager" };
            var superRole = new Role { Name = "Supervisor" };

            managerRole.Save();
            superRole.Save();

            var user = new User
            {
                FirstName = "John",
                LastName = "Doe",
                Roles = new[] { managerRole.ID, superRole.ID }
            };

            user.Save();

            var findRoles = new[] { managerRole.ID, superRole.ID };

            var managers = DB.Queryable<User>()
                             .Where(u => u.Roles.Any(r => findRoles.Contains(r)))
                             .Select(u => u.FirstName + " " + u.LastName)
                             .ToArray();
        }
    }
}

here's the aggregation query it sends to mongodb:

db.User.aggregate([{
    "$match": {
        "Roles": {
            "$elemMatch": {
                "$in": ["5d02691bada517167415c326", "5d02691cada517167415c327"]
            }
        }
    }
}, {
    "$project": {
        "__fld0": {
            "$concat": ["$FirstName", " ", "$LastName"]
        },
        "_id": 0
    }
}])

Answer 2

You should be able to query users like so:

public List<User> GetCaseAssigneesByRoles(string role)
{
    return UserCollection
        .Find(Builders<User>.Filter.AnyEq(u => u.Roles, role))
        .ToEnumerable()
        .ToList();
}

Essentially AnyEq takes an array (Roles) and checks if any single item in the list equals the value you specify. You might have to change some of the field names in this example.

Or for a list of roles (where a single role has to match):

public List<User> GetCaseAssigneesByRoles(List<string> roles)
{
    return UserCollection
        .Find(Builders<User>.Filter.AnyIn(u => u.Roles, roles))
        .ToEnumerable()
        .ToList();
}

Or for a list of roles (where all roles have to match):

public List<User> GetCaseAssigneesByRoles(List<string> roles)
{
    return UserCollection
        .Find(Builders<User>.Filter.All(u => u.Roles, roles))
        .ToEnumerable()
        .ToList();
}