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?

Answer 1

So you have a sequence of permittedStudentIds and a Document with a sequence of Students. Every Student has an Id.

You want to know whether there are any permittedStudentsId that is also an Id of one (or more) of your Document's Students.

In other words, if permittedStudentIds has values 1, 2, you want to know if there is any Student in Document.Students with Id 1 or 2.

Why not extract the Ids of all Students, Intersect them with your permittedStudentIds and see if the result is empty or not?

var studentIds = Document.Students.Select(student => student.Id);
var intersection = studentIds.Intersect(permittedStudentIds);
var result = intersection.Any();
// TODO: make one statement.

This works if both sequences are AsQueryable, but it should also work if your Document.Students is an IQueryable and your permittedStudentIds is an IEnumerable. My best guess is that this will become an SQL contains. See Queryable.Intersect

Answer 2

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