SqlQuerySpec QueryBuilder for CosmosDb

Question

We did have to move away from Linq-Queries to our DocumentDB/CosmosDB.

Reason being mainly two use cases:

• Partial select - the document has at least one large field that I only want returned in certain cases. Specifying the fields directly saves RU costs. I was unable to achieve that in Linq.

• Joins like this (example is a bit weird).

      SqlQuerySpec spec = new SqlQuerySpec(@"
          SELECT value(n)
          FROM books b
          join p in b.author.parents
          where b.isbn = @isbnId
          AND lower(p.address.street) = @parentStreet
      ");
  

So our queries look something like this:

IQueryable<Book> queryable = client.CreateDocumentQuery<Book>(
            collectionSelfLink,
            new SqlQuerySpec
    {
                QueryText = "SELECT * FROM books b WHERE (b.Author.Name = @name)", 
                Parameters = new SqlParameterCollection() 
        { 
                      new SqlParameter("@name", "Herman Melville")
                }
    });

However, with our requirements becoming more complex, we need the query to look different depending on given parameters. We also have "in"-queries that require us to add multiple parameters.

So now our code looks like this...

        var sqlParameterCollection = new SqlParameterCollection();
        for (int i = 0; i < ids.Length; i++)
        {
            var key = "@myid" + i;
            sqlParameterCollection.Add(new SqlParameter(key, ids[i]));
        }
 [...]
        var query = $@"
            {select}
            FROM collection m
            WHERE m.myid IN ({string.Join(",", sqlParameterCollection.Select(p => p.Name))})
        ";

Next, the where clause will need to be extended with an additional filter depending on some parameters

Since this is getting worse and worse: Are there any query builders available for this? I am thinking about a fluent api that could ideally also include the SqlParameters, not only the query text.

Pseudo code:

queryBuilder
    .from("m")
    .select("field1")
    .select("field2")
    .where("myid", Operators.In, ...)
           .And(...

Answer 1

You have to rewrite your IN query as an ARRAY_CONTAINS query. Then you can easily parameterize the arguments.

For example, SELECT * FROM book WHERE book.isbn IN (1,2,3) should be rewritten as SELECT * FROM book WHERE ARRAY_CONTAINS(@bookIsbnList, Book.ISBN)and @bookIsbnList = [1,2,3].

As to your original question, LINQ is supposed to be the query builder for more complicated scenarios. Can you please elaborate on why you had to move away?

Answer 2

We did not need to build a query builder in the end. We were underestimating the power of using SelectMany.

We ended up doing something like this

var query = client.CreateDocumentQuery<Book>(collectionSelfLink)
    // Join with select many
    .SelectMany(book => book.author.parents
        // Stay within the original SelectMany to do the filtering
        .Where(parents => parents.address.street == "parentstreet")
        // Stay within the original SelectMany to return the root node
//          .Select(x => book)
        // Or do a partial select
//          .Select(x => new {book.title, book.author.name})
    )
    .AsDocumentQuery();

This answer and the other examples helped a lot:

documentdb join with linq

https://github.com/Azure/azure-cosmos-dotnet-v2/blob/58d740dde9474800c62ece42ef2ac67e426c0915/samples/code-samples/Queries/Program.cs

    private static void QueryWithTwoJoins(string collectionLink)
    {
        // SQL
        var familiesChildrenAndPets = client.CreateDocumentQuery<dynamic>(collectionLink,
            "SELECT f.id as family, c.FirstName AS child, p.GivenName AS pet " +
            "FROM Families f " +
            "JOIN c IN f.Children " +
            "JOIN p IN c.Pets ");

        foreach (var item in familiesChildrenAndPets)
        {
            Console.WriteLine(item);
        }

        // LINQ
        familiesChildrenAndPets = client.CreateDocumentQuery<Family>(collectionLink)
                .SelectMany(family => family.Children
                .SelectMany(child => child.Pets
                .Select(pet => new
                {
                    family = family.Id,
                    child = child.FirstName,
                    pet = pet.GivenName
                }
                )));

        foreach (var item in familiesChildrenAndPets)
        {
            Console.WriteLine(item);
        }
    }