31

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.

Improve this question
8
  • Hi, are you able to put the details error here? Commented Nov 21, 2015 at 4:59
  • I added it in, but it won't add much more to this. The source of the error is Microsoft.Azure.Documents.Client Commented Nov 21, 2015 at 5:03
  • Does the documentation say it's supported? I don't think you should assume it is just because they didn't mention explicitly that it's not. Commented Nov 21, 2015 at 5:08
  • 3
    I never worked with Microsoft.Azure.Documents.Linq but I had some similiar issues with Data.Linq. This smells like you are mixing Linq-to-Sql and Linq-to-Object... What element type is i in i => i.type? Is the i.Products.Any not the IEnumerable.Any call? Commented Dec 2, 2015 at 17:15
  • 1
    In your update you first run a ToList and then do the nested query. This means that it doesn't get executed against documentdb but rather against your list implementation. It can still mean that the Any isn't supported on NESTED queries in linq to documentdb Commented Dec 3, 2015 at 10:41

5 Answers 5

Reset to default
93
+100

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.

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

9 Comments

Similar thing happened to me using unit tests against real environment. The unit tests was mocking the context using an IEnumerable<> to hold the data so .Any() works on the unit test. Published it to test and boom: exception.
Thank you so much - this was indeed the issue and I greatly appreciate you not only providing the solution but the reasoning behind it, "Teach a man to fish.." and all that. Thanks!!
Just want to add, please note there are a bunch of new LINQ operators supported in DocumentDB including Math, String, Spatial and Array operators (including Contains). Details here: azure.microsoft.com/en-us/blog/…
That can cause high RU usage
@CrhistianRamirez Correct.
|
6

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; }
}
Improve this answer

5 Comments

I'm using all of the same stuff, .NET 46, DB version 1.5 and even your example switching ToList() to FirstOrDefault() does not work and throws the aggregate exception that Any() is not supported. I agree that it should work, and said above that if I just fetch the first Where(), and then apply the Any() it does work, it seems to be the chaining. Any ideas?
What is the Visual Studio version and the Azure SDK installed on your platform?
I am running Visual Studio 2015 Enterprise. The specific SDK is kind of hard to nail down because it's a bunch of pieces, but most point to Windows Azure Tools for Visual Studio 2012 or 2013. I'm installing 2015 now to see if that helps.
I'm curious how you made that query work, as the ".Any" is still not supported by DocumentDb
Can confirm this is working for me as well. I'm on .NET Framework 4.7 and using the most recent stable version of Microsoft.Azure.DocumentDB v2.2.2 Make sure you're updating it in any project you're using it. That tripped me up initially
2

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();
Improve this answer

3 Comments

I am getting the same error - now just with Select() not being supported. This is very strange.
Interesting. What happens if you change the select so that it generates an anonymous type first, then use .Any?
I did this same thing, just not generating it as anonymous type, see my update in the initial question. This seems to work. I guess I should also state that Name on the product is a string. But technically, that shouldn't be of much consequence since the follow up query does work, it's the chaining that seems to cause the issue.
1

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

Improve this answer

1 Comment

distinct now exists fyi
-1

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();
Improve this answer

1 Comment

Sorry to comment on such an old response, but you missed the point, where Any() cannot be used within the DocumentDb-query provider.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.