Consider the following document type
class Info
{
public string Id { get; set; }
public string UserId { get; set; } // used as partition key
public DateTime CreatedAt { get; set; }
}
I've created a collection using this
var bson = new BsonDocument
{
{ "shardCollection", "mydb.userInfo" },
{ "key", new BsonDocument(shardKey, "hashed") }
};
database.RunCommand(new BsonDocumentCommand<BsonDocument>(bson));
To delete all documents that are older than a certain date, I tried this
collection.DeleteManyAsync(t => t.CreatedAt >= date);
But this fails with Command delete failed: query in command must target a single shard key. My question is, how should I efficently delete these documents across multiple partitions? I'm not looking for answers how to choose the partition key in this case. I think that there will be always cases where I have to run modifiying queries across all partitions.
I could first query for documents with collection.Find(t => t.CreatedAt >= date) and then run a DeleteManyAsync(t => idsInThatPartition.Contains(t.Id) && t.UserId == thatPartitionKey) for each group of partition key, but I really hope that there is a better way. Example code:
var affectedPartitions = await collection.Aggregate()
.Match(i => i.CreatedAt >= date)
.Group(i => i.UserId, group => new { Key = group.Key })
.ToListAsync();
foreach (var partition in affectedPartitions)
{
await collection.DeleteManyAsync(
i => i.CreatedAt >= date && i.UserId == partition.Key);
}
