通过linq实现CosmosDB自连接

Question

我有一个文档集合，其文档如下所示

{
  id: "123123541234"
  items: [
    {Name = "Item 1", Amount = 12.12},
    {Name = "Item 2", Amount = 4.00},
  ]
}

我可以编写一个sql自联接查询，如下所示，以返回我想要的内容：

select c.id, i.Name, i.Amount
from c
join i in c.items

如您所见，对于嵌套数组中的每个项，我的id 123123541234文档将被复制一次，因此输出如下：

[ 
  {id = "123123541234", Name = "Item 1", Amount = 12.12 }, 
  {id = "123123541234", Name = "Item 2", Amount = 4.00} 
] 

但是，我想使用linq来编写这个查询，以保持我的对象引用和类型定义强大。我不知道怎样才能通过linq实现这种“扁平”，

TL;DR:如何通过linq进行自动连接到cosmosdb？

Answer 1

假设你的类型像这样-

public class Container
{
    [JsonProperty(PropertyName = "id")]
    public string Id { get; set; }

    [JsonProperty(PropertyName = "items")]
    public Item[] Items { get; set; }
}

public class Item
{
    public string Name  { get; set; }
    public double Amount { get; set; }
}

public class FlattenedContainer
{
    public string Id { get; set; }
    public string Name { get; set; }
    public double Amount { get; set; }
}

你可以这么做-

var response = client.CreateDocumentQuery<Container>
            (
                UriFactory.CreateDocumentCollectionUri(...),
                new FeedOptions { ... }
            )
            .SelectMany(c => c.Items
                .Select(i => new FlattenedContainer 
                { 
                    Id = c.Id, 
                    Name = i.Name, 
                    Amount = i.Amount 
                }))
            .AsDocumentQuery();

var results = new List<FlattenedContainer>();
while (response.HasMoreResults)
{
    results.AddRange(await response.ExecuteNextAsync<FlattenedContainer>());
}