NOT IN filter in a query with a join of two collections using MongDB C# driver

Question

I have two collections that can be modelled with these classes (simplified version):

public class Profile
{
    [BsonId]
    [BsonRepresentation(BsonType.String)]
    public Guid? Id { get; set; }
    public string Name { get; set; }        
    [BsonRepresentation(BsonType.String)]
    public Guid UserId { get; set; }
}

public class User
{
    [BsonId]
    [BsonRepresentation(BsonType.String)]
    public Guid? Id { get; set; }
    public string UserName { get; set; }        
    public bool IsConfirmed { get; set; }        
}

I need a query to get all the UserProfiles whose User has the IsConfirmed flag equals true, and then I need to filter out the ones whose Id is included in a list of excluded Ids:

IList<Guid> profileIdsToExclude

This is the query I've built so far:

var professionalProfilesQuery = 
    (from profileCollection in _mongoContext.Database.GetCollection<Profile>("profiles").AsQueryable()
     join userCollection in _mongoContext.Database.GetCollection<User>("users").AsQueryable()
     on profileCollection.UserId equals userCollection.Id.Value
     into users
     orderby profileCollection.Name
     select new ProfessionalProfile
     {
         Id = profileCollection.Id,
         Name = profileCollection.Name,                                                 
         UserId = profileCollection.UserId,
         IsConfirmed = users.First().IsConfirmed,                                                 
     })
     .Where(p => p.IsConfirmed && !profileIdsToExclude.Contains(p.Id.Value));

Where ProfessionalProfile is a class to return the query result:

public class ProfessionalProfile
{
    public Guid? Id { get; set; }
    public string Name { get; set; }        
    public Guid UserId { get; set; }
    public bool IsConfirmed { get; set; }        
}

I get UserProfiles, only the ones with IsConfirmed equals true. But the ones whose Id is in the list of excluded Ids are not filtered out and are returned by the query.

Any idea if what I want to do is possible and how?

Thanks in advance.

Answer 1

Your problem here is caused by a little type confusion.

The query that the driver creates contains a $match stage at the end that looks kind of like this:

{
    $match: {
        IsConfirmed: true,
        Id: {
            $nin: [ BinData(3, "DBA38D51FC28094BA2D6439E95643A49") ]
        }
    }
}

So it is actually trying to exclude results that have a field with a particular Guid value. You are, however, storing strings for your Guids so the filter does not exclude anything. In order to fix that here is what you could do:

Change

.Where(p => p.IsConfirmed && !profileIdsToExclude.Contains(p.Id.Value));

into

.Where(p => p.IsConfirmed && !profileIdsToExclude.Contains(p.Id));

and

IList<Guid> profileIdsToExclude

into

IList<Guid?> profileIdsToExclude