How do I flatten certain properties in a nested structure using Linq for Cosmos noSQL?

(Code details at the end of the question...)

I have a 3 level data structure, example top.Middles[y].Bottoms[z].Secret and I want a list of all the Outers, Middles, and Bottoms who's innermost Secrets are 'Forbidden'. In SQL I would express that as...

SELECT t.id topId, m.id as middleId, b.id as bottomId
FROM Tops t
join m in t.Middles
join b IN m.Bottoms
where b.Secret = 'Forbidden'

which gives me something like

[
{ "topId": "296f9023-f150-45cd-ad38-a7be2ac83f41", "middleId": "101c24bc-71cb-4b6b-a6cf-6979be61be45", "bottomId": "812ac30c-79d4-4c62-94c2-a8cd3eb19bb6" },
{ "topId": "296f9023-f150-45cd-ad38-a7be2ac83f41", "middleId": "101c24bc-71cb-4b6b-a6cf-6979be61be45", "bottomId": "9b298db2-db68-4a0c-82f4-d5bd270aa72b" },
{ "topId": "296f9023-f150-45cd-ad38-a7be2ac83f41", "middleId": "350f63eb-e41a-493f-9e6d-740818242b7e", "bottomId": "56a51a6c-4e6b-4c52-bbc7-0d71b2486c76" },
{ "topId": "1ebdb15f-5864-4e1e-860d-e3a97696da38", "middleId": "ce3bb505-5412-4368-a30e-7f6ce7838a95", "bottomId": "093c7d30-97bb-4fcf-9bd7-0814cbb92571" },
{ "topId": "1ebdb15f-5864-4e1e-860d-e3a97696da38", "middleId": "1b239750-1a91-4f0c-9fb1-b1d9e333349e", "bottomId": "b7c27305-59a3-4af9-93ed-5ca272472d37" },
{ "topId": "1ebdb15f-5864-4e1e-860d-e3a97696da38", "middleId": "a70b3933-3cde-44d0-80a8-fef6d958ce4a", "bottomId": "30c74577-ef59-41ec-8fd2-0ded3f3c30a2" }
]

And that suits my purpose...

But I would rather do this with LINQ so I get strong typing and compiler awareness of property names.

The code is having all kinds of compiler errors and I can't figure this out. The method name is Example.GetTopsContainingTheSecretWord.

var topsContainingTheSecretWord = new Example().GetTopsContainingTheSecretWord("Forbidden");

Code

public class Outer
{
    public string id { get; set; }
    public string Name { get; set; }
    public List<Middle> Middles { get; set; }
}

public class Middle
{
    public string id { get; set; }
    public string Name { get; set; }
    public List<Inner> Inners { get; set; }
}

public class Inner
{
    public string id { get; set; }
    public string Secret { get; set; }
    public string Name { get; set; }
}

public record ResultRecord(
    Guid TopId,
    Guid MiddleId,
    Guid BottomId
);

public class Example {
    public async Task<List<ResultRecord>> GetTopsContainingTheSecretWord(
        string secretWord,
        CancellationToken cancellationToken = default
    )
    {
        var query = _plansContainer.GetItemLinqQueryable<ResultRecord>(true)
            .SelectMany(t => t.Middles)
            .SelectMany(m => m.Bottoms.Where(b => b.Secret == secretWord))
            .ToFeedIterator();

        var results = new List<ResultRecord>();
        while (query.HasMoreResults)
        {
            var response = await query.ReadNextAsync();
            results.AddRange(response);
        }

        return results;
    }
}

You won't be able to generate the exact query using linq, but you can get the same results. For the example I've used your model but changed the id's to be Guid, which should work aslong as you use guid's consistently as your identifiers.

var feed = container
    .GetItemLinqQueryable<Outer>()
    .SelectMany(t => t.Middles.Select(m => new { t, m }))
    .SelectMany(x => x.m.Inners.Select(i => new { x.t, x.m, i }))
    .Where(x => x.i.Secret == "Forbidden")
    .Select(x => new
    {
        BottomId = x.i.id,
        MiddleId = x.m.id,
        TopId = x.t.id,
    })
    .ToFeedIterator();

List<ResultRecord> results = [];

while (feed.HasMoreResults)
{
    foreach (var item in await feed.ReadNextAsync())
    {
        results.Add(new ResultRecord(item.TopId, item.MiddleId, item.BottomId));
    }
}