MongoDb C# Driver - DeleteMany doesn't work with In Filter

Question

I tried to delete dublicate records using MongoDb C# Driver. The script below neither raise an error nor delete any record. Any advice ?

[BsonIgnoreExtraElements]
public class ApiUsers
{
    [BsonId]
    [BsonRepresentation(BsonType.String)]
    public ObjectId Id { get; set; }
    
    public string UserEMail { get; set; }
}

var users = _mongoDbContext.ApiUsers.Find(f => f.UserEMail == email).ToList();
var oneUser = users[0];

var idsToDelete = users.Where(x => !x.Id.Equals(oneUser.Id)).Select(x => x.Id);
if (idsToDelete.Any())
{   //Delete Dublicates
    
    var idsToDeleteFilter = Builders<ApiUsers>.Filter.In(t => t.Id, idsToDelete);
    var result = _mongoDbContext.ApiUsers.DeleteMany(idsToDeleteFilter);
} 

I tried also DeleteOne method like below, but it didn't work either,

foreach (var idToDelete in idsToDelete)                        
   _mongoDbContext.ApiChildUsers.DeleteOne(Builders<ApiChildUsers>.Filter.Eq(u => u.Id, idToDelete)); 

Answer 1

you could try the following approach where you get back an Id to keep and delete the rest like so:

var filter = Builders<ApiUsers>.Filter.Where(u => u.UserEMail == "test@test.com");
var projection = Builders<ApiUsers>.Projection.Expression(u=>u.Id);
var options = new FindOptions<ApiUsers, string> { Projection = projection, Limit = 1 };

var idToKeep = collection.FindSync(filter, options).ToList()[0];

collection.DeleteMany(
    u => u.UserEMail == "test@test.com" &&
         u.Id != idToKeep);

as a sidenote, i'd like to suggest you store the IDs in the server as ObjectId instead of string because it's less efficient as ObjectId only takes 12 bytes to store which would be less than the hexadecimal string representation of it.

here's a less verbose test program using mongodb.entities:

using MongoDB.Entities;
using System.Threading.Tasks;

namespace TestApp
{
    public class ApiUsers : Entity
    {
        public string UserEMail { get; set; }
    }

    internal static class Program
    {
        private static async Task Main()
        {
            await DB.InitAsync("test", "localhost");

            await new[] {
                new ApiUsers { UserEMail = "test@test.com"},
                new ApiUsers { UserEMail = "test@test.com"},
                new ApiUsers { UserEMail = "test@test.com"},
                new ApiUsers { UserEMail = "teX@teX.com"},
            }.SaveAsync();

            var idToKeep = (await DB.Find<ApiUsers, string>()
                                    .Match(u => u.UserEMail == "test@test.com")
                                    .Project(u => u.ID)
                                    .Limit(1)
                                    .ExecuteAsync())[0];

            await DB.DeleteAsync<ApiUsers>(
                u => u.UserEMail == "test@test.com" &&
                     u.ID != idToKeep);
        }
    }
}