C# LINQ .Any not working on DocumentDb CreateDocumentQuery

One of the biggest confusion with LINQ queries against IQueryable<T> is that they look exactly the same as queries against IEnumerable<T>. Well, the former is using Expression<Func<..>> whenever the later is using Func<..>, but except if one is using explicit declarations this is not so noticeable and seems unimportant. However, the big difference comes at runtime.

Once the IEnumerable<T> query is successfully compiled, at runtime it just works, which is not the case with IQueryable<T>. A IQueryable<T> query is actually an expression tree which is processed at runtime by the query provider.

From one side this is a big benefit, from the other side, since the query provider is not involved at query compile time (all the methods are provided as extension methods by Queryable class), there is no way to know if the provider supports some construct/method or not until runtime. People that use Linq to Entities know that very well. To make the things harder, there is no clear documentation what the specific query provider supports and more importantly, what it doesn't support (as you noticed from the "what is supported" link you provided).

What's the solution? (and why your second code works)

The trick is to write the maximum possible (i.e.supported by the query provider) query part against the IQueryable<T>, and then switch to IEnumerable<T> and do the rest (remember, once compiled, IEnumerable<T> query just works). The switch is performed by AsEnumerable() call. And that's why your second code is working - because unsupported Any method is no more in the DocumentDb query provider context. Note that ToList call is not needed and the query is not executed twice - in fact this way there is no single query, but two - one in database and one in memory.

So something like this would be sufficient:

List<Art> items = DocumentDbHelper.Client.CreateDocumentQuery<Art>(collection.DocumentsLink)
                               .Where(i => i.type == "art")
                               .AsEnumerable() // The context switch!
                               .Where(i => i.Products.Any(p => p.Name == productType))
                               .ToList();

Finally, what really is supported by the DocumentDb query provider

It's not quite clear from the documentation, but the answer is: exactly (and only) what is included there. In other words, the only supported query operators (or better say Queryable or Enumerable extension methods) are

• Select
• SelectMany
• Where
• OrderBy
• OrderByDescending

As you may see, it's very limited. Forget about join and grouping operators, Any, Contains, Count, First, Last etc. The only good thing is that it's easy memorizable :)

How do I know that? Well, as usual when something is unclear from the documentation, one either use trial and error or decompiler. Apparently in this case the former is not applicable, so I've used the later. If you are curious, use your favorite decompiler and check the code of the internal class DocumentQueryEvaluator inside the Microsoft.Azure.Documents.Client.dll.

I am using the latest Azure DocumentDB nuget targetting .Net 4.6.

<package id="Microsoft.Azure.DocumentDB" version="1.5.0" targetFramework="net46" />

Here's the sample code which is working fine for me.

using System.Collections.Generic;
using System.Linq;
using Microsoft.Azure.Documents.Client;
using Microsoft.Azure.Documents.Linq;

var book = client.CreateDocumentQuery<Book>(collectionLink)
                    .Where(b => b.Title == "War and Peace")
                    .Where(b => b.Publishers.Any(p => p.IsNormalized()))
                    .AsEnumerable().FirstOrDefault();
public class Book
{
    [JsonProperty("title")]
    public string Title { get; set; }

    public Author Author { get; set; }

    public int Price { get; set; }

    public List<string> Publishers { get; set; }

}

public class Author
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

You should try using IEnumerable.Contains link here

DbHelper.Client.CreateDocumentQuery<Art>(collection.DocumentsLink)
   .Where(i => i.type == "art")
   .Where(i => i.Products
       .Select(p => p.Name).Contains(productType))
                               .AsEnumerable()
                               .ToList();