CosmosDB selfjoin via linq

Question

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?

I need [ {id = "123123541234", Name="Item 1", Amount = 12.12 }, {id="123123541234", Name="Item 2", Amount = 4.00} ] — Nathan Tregillus
– Nathan Tregillus, Commented Jun 29, 2018 at 19:15
The intent is to use a linq statement, not a string query. This is because if the model changes, I will get a compilation error, not a runtime error. — Nathan Tregillus
– Nathan Tregillus, Commented Jun 30, 2018 at 1:47

Kiryl · Accepted Answer · 2018-06-30 12:05:33Z

1

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

answered Jun 30, 2018 at 12:05

Kiryl

1,53610 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nathan Tregillus Over a year ago

ah, sneaky nesting! didn't think of doing it that way... does that get turned into the same query, or does it do the mapping in memory?

Kiryl Over a year ago

@NathanTregillus It ought to be the same query

Collectives™ on Stack Overflow

CosmosDB selfjoin via linq

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related