Filtering CosmosDb using LINQ Query; .Contains or .Any LINQ causing exception

Question

I am using CosmosDB and what to load filtered data. I want to do the filtering on the database side but I had some problems, which I think I managed to resolve but not sure why one way works but not another.

I have a list of CarPositions

// I fill this list from cosmosdb data  
var carPosition = new List<CarPositions>()

My goal is to only get the cars if I have their positions in the carPosition list

I tried to do something like this

var iterator = CarContainer
.GetItemLinqQueryable<Car>(true)
.Where(x => carPosition.Any(cp => cp.Id == x.Id))
.ToFeedIterator();

Which throws an exception; "Input is not of type IDocumentQuery"

From what I understood from looking online is that CosmosDb provider does not support ANY linq when translating the query to SQL

This then lead me to try doing the same thing but with using Select and Contain

1)

var iterator = CarContainer
.GetItemLinqQueryable<Car>(true)
.Where(x => carPosition.Select(cp => cp.Id).ToList().Contain(x.Id))
.ToFeedIterator();

Again causing the same exception

I then tried this which works but to me this is doing the exact same thing

2)

var carPosIds = carPosition.Select(cp => cp.Id).ToList();

var iterator = CarContainer
.GetItemLinqQueryable<Car>(true)
.Where(x => carPosIds.Contain(x.Id))
.ToFeedIterator();

Can someone explain why the second one works but not the first? Also why the Any linq did not work?

Answer 1

Because the linq statement needs to be translated into a sql query which will run on the database server. The Contains statement inside the Where statement is supported because the SDK has implemented a translation using the sql IN clause (presumably).

While your Any query seems the same, just imagine it being a different expression and how difficult (or nearly impossible) it would become to translate it into an SQL query as the expression grows more complex. E.g. .Any(cp => cp.Id * 2 - x.Id / 2 == x.Id).

If this would be pure C# code running in memory instead of being translated by the SDK into a query for Cosmos all above methods would work (some better than others).