CODEWITHSUNDEEP
.netlinqazure-cosmosdb
Nathan Tregillus
Nathan Tregillus

Reputation: 6334

CosmosDB selfjoin via linq

I have a document collection with documents that look like the following

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

I can write a sql self joined query like the following to return what I want:

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

As you can see, my doc with the id 123123541234 would be duplicated, once for each item in the nested array, so the output looks like:

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

However, I'd like to use linq to write this query to keep my object references and type definition strong. I am failing to see how i can do this type of "flattening" via linq,

TL;DR: how do I do a self-join via linq to cosmosdb?

Upvotes: 0

Views: 443

Answers (1)

Kiryl
Kiryl

Reputation: 1511

Assuming your types look something like this - 

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; }
}

Here is how you could do this - 

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>());
}

Upvotes: 1

Related Questions