C# LINQ .Any not working on DocumentDb CreateDocumentQuery

Question

I'm trying to query Art that has a product of a certain type. Here is my model for Art:

  public string Title { get; set; }
  public string Description { get; set; }
  public List<Product> Products { get; set; }
  public string PaintedLocation { get; set; }

From here all I'm doing is the following LINQ query:

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

I get the following error:

"Method 'Any' is not supported."

I went to the page that the code references for seeing what is supported but I don't see it saying that Any() is not supported, so I'm probably doing something incorrect. Any help is appreciated.

UPDATE

This is really odd to me, so I broke it up to see what was being returned from the two results to better debug the issue to this:

List<Art> items = DocumentDbHelper.Client.CreateDocumentQuery<Art>(collection.DocumentsLink)
                       .Where(i => i.Id.Contains("art"))
                       .AsEnumerable()
                       .ToList();

items = items.Where(i => i.Products.Any(p => p.Name == productType))
             .AsEnumerable()
             .ToList();

For some reason this works, I'm not a fan of this because since I'm converting it to a list it's running the query twice - but it is at least proof that Any() and Select() should technically work.

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

The most performant solution currently is to use the SQL syntax since that allows document DB to use the collection's index.
example:

SELECT a 
  FROM a
  JOIN p in a.Products
 WHERE ARRAY_CONTAINS(a.Id, 'art') 
   AND p.Name = 'My Product Type'

The disadvantage is that you might get non-unique results and have to distinct the result client-side.

To get this issue into DocumentDB, it would help to vote on the following item: https://feedback.azure.com/forums/263030-documentdb/suggestions/14829654-support-sub-query-functions-like-exists-not-exist

Answer 5

Why don't you try this one?

 List<Art> items =  DocumentDbHelper.Client.CreateDocument(collection.DocumentsLink)
                           .Where(i => i.type == "art" && i.Products.Any(p => p.Name == productType))
                           .AsEnumerable()
                           .ToList();