MongoDB and LINQ: "NOT IN" clause-CodePudding

I have two collections, one is a list of image names, the second is a subset of that list. When a task has been completed its name is inserted into the second collection.

I need to retrieve a set of not yet completed image names from the first collection. I have achieved this successfully with:

var processedNames = processed.AsQueryable().Select(x => x.ImageName).ToArray();
foreach (var result in results.Where(x => !processedNames.Contains(x.ImageName))

However this brings a large list of strings back from the database and then sends it back to the database in a single document, which as well as being inefficient will break eventually.

So I tried to rewrite it so it's all performed server side with:

            var results = from x in captures
                          join complete in processed.AsQueryable() on x.ImageName equals complete.ImageName into completed
                          where !completed.Any()
                          select x;

This fails with:

System.NotSupportedException: '$project or $group does not support {document}.'

I also tried using the non LINQ API:

                var xs = capturesCollection.Aggregate()
                    .Lookup("Processed", "ImageName", "ImageName", @as: "CompletedCaptures")
                    .Match(x => x["CompletedCaptures"] == null)
                    .ToList();

This fails with:

MongoDB.Bson.BsonSerializationException: 'C# null values of type 'BsonValue' cannot be serialized using a serializer of type 'BsonValueSerializer'.'

How can I achieve this query completely server side with the C# driver? A pure LINQ solution is preferable for portability.

CodePudding user response：

I worked out how to do it with the Aggregate API:

                var results = capturesCollection.Aggregate()
                    .As<CaptureWithCompletions>()
                    .Lookup(processed, x => x.ImageName, x => x.ImageName, @as:(CaptureWithCompletions x) => x.CompletedCaptures)
                    .Match(x => !x.CompletedCaptures.Any())
                    //.Limit(2)
                    .ToList();

CodePudding user response：

The following should be equivalent to your first code block except the work is done in the database. Perform a sub-query in the where clause and use Contains():

var results = from x in captures   
              where !(from p in processed.AsQueryable() select p.ImageName)    
                    .Contains(x.ImageName)    
              select x;

Not 100% sure whether it will work with MongoDb, though.