MongoDB - How to filter and update on a child of a child

Question

I am using C# and MongoDB:

using MongoDB.Driver;
using MongoDB.Driver.Linq;

I have an object like this:

public class Account
{
    public string Name { get; set; }
    public string APIKey { get; set; }
    public string Secret { get; set; }
    public bool Active { get; set; }

    public List<Store> Stores { get; set; } = new List<Store>();

    public List<User> Users { get; set; } = new List<User>();
}

Where the store collection looks like this:

public class Store
{
    public string Name { get; set; }
    public string Location { get; set; }
    public DateTime LastMessageDate { get; set; }
    public List<Customer> Customers = new List<Customer>();
}

I want to search for account first, then the store, then the customer inside the store object. I can filter down to the store level doing this:

var filter = Builders<Account>.Filter.Eq(e => e.ID, headers.AccountID) 
    & Builders<Account>.Filter.ElemMatch(e => e.Stores, Builders<Store>.Filter.Eq(e => e.ID, headers.StoreID))

But I dont know how to filter down to the customer level.

I tried this:

var filter = Builders<Account>.Filter.Eq(e => e.ID, headers.AccountID) 
    & Builders<Account>.Filter.ElemMatch(e => e.Stores, Builders<Store>.Filter.Eq(e => e.ID, headers.StoreID))
    & Builders<Account>.Filter.ElemMatch(e => e.Stores.FirstOrDefault(c=>c.ID == headers.StoreID).Customers, Builders<Customer>.Filter.Eq(e => e.UUID, customerUUID));
var update = Builders<Account>.Update.Set(e => e.Stores.FirstMatchingElement().Customers, customer);

but it doesn't work saying:

Error CS1660 Cannot convert lambda expression to type 'FieldDefinition<Account, Customer>' because it is not a delegate type

Answer 1

1. The .FirstOrDefault() in the filter query will not able to translate into a MongoDB query, instead, you need to use the nested .ElemMatch() for filtering the customer by UUID.

2. To update the nested customer element, you need to work with the filtered positional operator ($[<identifier>]) or .AllMatchingElements() method in MongoDB .NET Driver and arrayFilters.

var filter = Builders<Account>.Filter.Eq(e => e.ID, headers.AccountID)
    & Builders<Account>.Filter.ElemMatch(e => e.Stores, Builders<Store>.Filter.Eq(e => e.ID, headers.StoreID))
    & Builders<Account>.Filter.ElemMatch(e => e.Stores, 
        Builders<Store>.Filter.ElemMatch(e => e.Customers, Builders<Customer>.Filter.Eq(e => e.UUID, customerUUID)));
var update = Builders<Account>.Update.Set(e => 
    e.Stores.FirstMatchingElement().Customers.AllMatchingElements("c"), 
    customer);

var result = await _collection.UpdateOneAsync(filter, update,
    new UpdateOptions
    {
        ArrayFilters = new ArrayFilterDefinition[]
        {
            new BsonDocumentArrayFilterDefinition<Customer>
            (
                new BsonDocument("c.UUID", BsonValue.Create(customerUUID.ToString()))
            )
        }
    });