Use Linq.Any() inside a Linq.Where() on CosmosDb

Question

I am trying to nest an .Any() inside a .Where() clause to query a local CosmosDb emulator.

The code looks like below; where permittedStundentIds is a variable (List<long>) and a is a Document within the CosmosDb

.Where(a => permittedStudentIds.Any(sId => a.Students.Any(s => s.Id == sId)));

When I execute the query, I get the error:

Method 'Any' is not supported. ActivityId: 800000a8-0002-d600-b63f-84710c7967bb, documentdb-dotnet-sdk/1.22.0 Host/64-bit MicrosoftWindowsNT/10.0.16299.0

I have tried multiple variations to get an equivalent expression, but to no avail. The only one that worked was using a .Contains() and hard coding the student index; which is not feasible since the number of students may not be known.

.Where(a => permittedStudentIds.Contains(a.Students[0].Id));

I do understand that certain lambda extensions are not yet supported on Sql API for CosmosDb, but is there a workaround for this?

Would it work to replace Any with Where and add a Count()!=0 ? — Hans Keﬆing
I tried this a => a.Students.Where(s => permittedStudentIds.Where(pSId => pSId == s.Id).Count() > 0).Count() > 0 . It doesn't not work. Throws a similar error message related to Where is not supported — bit
Tried a => permittedStudentIds.Exists(sId => a.Students.Exists(s => s.Id == sId)) doesn't work. Similar error. — bit
From what I see in the List of supported LINQ operators, the problem is exactly the same as in C# LINQ .Any not working on DocumentDb CreateDocumentQuery — Ivan Stoev

bit bit · Accepted Answer · 2018-07-08T07:59:56

After trying out numerous combination of various lambda expressions, here is what worked out for me.

I added a StudentIds property to my DocumentModel class; redundant but used for filtering alone.

Thereafter, I OR-ed the query with .Contains(), something like this:

Expression<Func<MyDocumentModel, bool>> query = a => a.StudentIds.Contains(permittedStudentIds[0]);
foreach (var id in permittedStudentIds.Skip(1))
{
    query = query.Or(a => a.StudentIds.Contains(id));
}

and then used the query like:

.Where(query);

For the query.Or() part I used the following classes:

// See: https://blogs.msdn.microsoft.com/meek/2008/05/02/linq-to-entities-combining-predicates/
public static class ExpressionExtensions
{
    public static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
    {
        // build parameter map (from parameters of second to parameters of first)
        var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] }).ToDictionary(p => p.s, p => p.f);

        // replace parameters in the second lambda expression with parameters from the first
        var secondBody = ParameterVistor.ReplaceParameters(map, second.Body);

        // apply composition of lambda expression bodies to parameters from the first expression 
        return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.AndAlso);
    }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.OrElse);
    }
}


public class ParameterVistor : ExpressionVisitor
{
    private readonly Dictionary<ParameterExpression, ParameterExpression> map;

    public ParameterVistor(Dictionary<ParameterExpression, ParameterExpression> map)
    {
        this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
    }

    public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
    {
        return new ParameterVistor(map).Visit(exp);
    }

    protected override Expression VisitParameter(ParameterExpression p)
    {
        ParameterExpression replacement;
        if (map.TryGetValue(p, out replacement))
        {
            p = replacement;
        }
        return base.VisitParameter(p);
    }
}

Use Linq.Any() inside a Linq.Where() on CosmosDb

2 Answers