Replace OrderBy with Where in LINQ query

Question

There is a known issue with CosmosDb where if you use an ORDER BY clause it excludes documents that do not have this property defined

To work round this I'm trying to create functionality that takes a LINQ query and replaces the Order clause with a check for documents where the property is not defined so we can then run both queries and combine the results.

So:

ordersDb.Where(x => x.Name == customerName).OrderBy(x => x.CompanyName)

would become:

ordersDb.Where(x => x.Name == customerName)
.Where(x => !x.CompanyName.IsDefined()) // IsDefined is a built in CosmosDb function

Using Expression Builder I've created the following. However, I'm having issues trying to call my expression as a Where method - :

private sealed class OrderByToIsNotDefinedVisitor : ExpressionVisitor
{
    protected override Expression VisitMethodCall(MethodCallExpression node)
    {
        if (node.Method.DeclaringType == typeof(Queryable) &&
            (node.Method.Name == "OrderBy" || node.Method.Name == "OrderByDescending"))
        {
            // Get the IsDefined method
            var methodIsDefined = typeof(TypeCheckFunctionsExtensions).GetMethod("IsDefined",
                BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.Public, null,
                new Type[] { typeof(object) }, null);

            // Apply the IsDefined method to the property that was being used for OrderBy
            var isDefinedItem = Expression.Call(methodIsDefined, node.Arguments[1]);

            // Alter the expression to check for !IsDefined()
            var isNotDefinedItem = Expression.Not(isDefinedItem);

            var entityType = node.Method.GetGenericArguments()[0];

            var genericWhere = BuildGenericWhere();

            var methodWhere = genericWhere.MakeGenericMethod(entityType);

            var param = Expression.Parameter(entityType);

            Expression newExpression =
                Expression.Call(
                    methodWhere,
                    node.Arguments[0],
                    Expression.Lambda(
                        typeof(Func<,>).MakeGenericType(entityType, typeof(bool)),
                        isNotDefinedItem,
                        param));

            return newExpression;
        }

        return base.VisitMethodCall(node);
    }
}

private static MethodInfo BuildGenericWhere()
{
    var genericWhereMethod = typeof(Enumerable).GetMethods(BindingFlags.Public | BindingFlags.Static)
        .Where(x => x.Name == "Where" && x.GetGenericArguments().Length == 1)
        .Select(x => new { Method = x, Parameters = x.GetParameters() })
        .Where(x => x.Parameters.Length == 2 &&
                    x.Parameters[0].ParameterType.IsGenericType &&
                    x.Parameters[0].ParameterType.GetGenericTypeDefinition() == typeof(IEnumerable<>) &&
                    x.Parameters[1].ParameterType.IsGenericType &&
                    x.Parameters[1].ParameterType.GetGenericTypeDefinition() == typeof(Func<,>))
        .Select(x => x.Method)
        .Single();

    return genericWhereMethod;
}

This compiles OK, but when I execute the query I get:

Microsoft.Azure.Documents.Linq.DocumentQueryException: Expression with NodeType Lambda is not supported.

...as documentDb cannot handle the lambda clause

I've also tried replacing the lambda clause with a direct call to the where method, so the call becomes:

var updatedQueryExpression = Expression.Call(node.Arguments[0], methodWhere, isNotDefinedItem);

return updatedQueryExpression;

...however, this results in:

System.ArgumentException: Static method requires null instance, non-static method requires non-null instance

You may have already considered this, but you could accomplish your goal by removing the ORDER BY from your query and sorting the results on the client side. This would allow you to perform your operation with one call to the database rather than two. — Paul, Jan 01 '20 at 16:42
Good suggestion @Paul, ordering on the front end would work, but we actually only want to bring back the first 10 rows so doing that would involve potentially bringing a lot of extra data to the client side — ElGringo, Jan 01 '20 at 22:21

score 0 · Answer 1 · answered Jan 03 '20 at 09:26

Firstly,we are told that we can only sort with properties of document, not derived values.

So,for me,i also recommand you following the suggestion which is mentioned by @Paul in the comment: Querying all the matching data from cosmos db,then try to sort the result list and take the top elements.I believe that you already know the expression How to get first N elements of a list in C#?:

var firstFiveArrivals = myList.OrderBy(i => i.ArrivalTime).Take(5);

Since you have to get the top elements and if your matching data set is large enough, whether you use LINQ or SQL, you will encounter thresholds which could be solved by Continuation Token.

Replace OrderBy with Where in LINQ query

1 Answers1