CODEWITHSUNDEEP
azure-cosmosdb
Simon Bob
Simon Bob

Reputation: 405

Document db linq query, SelectMany after SelectMany

I'm using linq to make a query in a documentdb collection which consists of items with two individual arrays like:

{
 name: "Family 1",
 cars: ["Toyota", "Honda"],
 pets: ["Cat", "Dog"]
}

Lets say that I want to find families with a Toyota, I would use a query like this:

var families = Client.CreateDocumentQuery<T>(dLink)
    .SelectMany(f => f.Cars
                .Where(car=> car == "Toyota")
                .Select(car => f));

This works just fine. When inspecting the generated sql query it also looks as expected:

SELECT VALUE root FROM root JOIN car IN root[\"Cars\"] WHERE (car= \"OU=Brugere\")

If I want to find all families with a Toyota and a dog, I would expect to be able to use a query that looks something like this:

var families = Client.CreateDocumentQuery<T>(dLink)
    .SelectMany(f => f.Cars
                .Where(car=> car == "Toyota")
                .Select(car => f))
    .SelectMany(f => f.Pets
                .Where(pet => pet == "Dog")
                .Select(pet => f));

This fails with a cryptic error message:

 {"errors":[{"severity":"Error","location":{"start":109,"end":111},"code":"SC2001","message":"Identifier 'pet' could not be resolved."}]}

When inspecting the generated sql query, it does not look as expected:

SELECT VALUE root FROM root JOIN car IN root[\"Cars\"] WHERE ((car= \"Toyota\") AND (pet = \"Dog\")) "}

This makes it obvious why 'pet' cannot be resolved, it's missing a Join for pet in root[\"Pets"]

How do I make two distinct joins via linq in documentdb?

Upvotes: 0

Views: 1004

Answers (1)

Andrew Liu
Andrew Liu

Reputation: 8119

Given the SQL output from toString(), it looks like there is a bug in the LINQ provider. We'll work on a fix.

In the meantime, I'd advise issuing the query as a SQL query:

SELECT *
FROM c
WHERE ARRAY_CONTAINS(c.cars, "Toyota")
      AND ARRAY_CONTAINS(c.pets, "Cat")

Upvotes: 0

Related Questions