LINQ Expression Tree: Call Any method against a DbSet

Question

I'm trying to translate the following LINQ query into expression tree

var queryActivity = uow.PromoActivityMeasuresRepository.ToQueryable();
var queryMeasure = uow.PromoMeasuresRepository.ToQueryable();
queryActivity.Where(pa => pa.WorkSpaceId == 28 && 
                          !queryMeasure.Any(pm => pm.WorkSpaceId == pa.WorkSpaceId && 
                                                  pm.Organization == pa.Organization && 
                                                  pm.MeasureCode == pa.MeasureCode));

I've been able to get expressions for what is related to the simple constraints, but now I'm stuck at how create the expression that relates on queryMeasure.Any

With code

//I've translated pm.WorkSpaceId == pa.WorkSpaceId
var childParameter = Expression.Parameter(childEntityType, "pa");
var parentParameter = Expression.Parameter(validatingEntityType, "pm");
var parentWorkSpace = Expression.Property(parentParameter, "WorkSpaceId");
var childWorkSpace = Expression.Property(childParameter, "WorkSpaceId");
var parentChildWorkSpaceConstraint = Expression.Equal(parentWorkSpace, childWorkSpace); 

and with code

// I've translated pm => pm.WorkSpaceId == pa.WorkSpaceId && 
//                       pm.Organization == pa.Organization && 
//                       pm.MeasureCode == pa.MeasureCode)

Expression logicalAnd = null;
foreach (var field in FKChildEntity.Value.Fields)
{
    var parentLeft = Expression.Property(parentParameter, field);
    var childRight = Expression.Property(childParameter, field);
    var parentChildConstraint = Expression.Equal(parentLeft, childRight);
    if (logicalAnd == null)
    {
        logicalAnd = Expression.AndAlso(parentChildWorkSpaceConstraint, parentChildConstraint);
        continue;
    }
    //parentConstraints.Add(parentChildConstraint);
    logicalAnd = Expression.AndAlso(logicalAnd, parentChildConstraint);
}

And here the issue...

I'm not able to understand how can I call the queryMeasure.Any to be used after in the NegateExpression

var parentDelegateType = typeof(Func<,>).MakeGenericType(validatingEntityType, typeof(bool));
var parentPredicate = Expression.Lambda(parentDelegateType, logicalAnd, parentParameter);
var promoMeasuresParameter = Expression.Constant(dataRepository, dataRepository.GetType());

var AnyMethod = Expression.MakeMemberAccess(promoMeasuresParameter, promoMeasuresParameter.GetType().GetMember("Any").FirstOrDefault());

var parentQueryable = dataRepository.GetType().InvokeMember("ToQueryable", BindingFlags.InvokeMethod, null, dataRepository, null);
var collectionParameter = Expression.Parameter(parentQueryable.GetType(), "parentCollection");

var AnyMethodExpression = Expression.Call(parentQueryable.GetType(), "Any", null , parentPredicate);
var negateExpression = Expression.Negate(AnyMethodExpression);

Can I ask someone of you some hints on how to proceed ?

Thank you all

Answer 1

The Any method you want to call is generic (so is Where):

public static bool Any<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate);

You need to get your hands on a constructed MethodInfo, with the element type of your second queryable as the type argument.

Finding reflection objects by name is tricky, especially with overloads and/or generics. That's why I always use my GetMethod helper function (see below) and let the compiler find/overload resolve/type infer the required MethodInfo.

Having solved this issue, it's more straight forward to write a queryable extension method WhereNotAnyOtherThatEqualsOnCorrespondingFields that does what you want (feel free to rename it :) ).

public static class MyExtensions
{
    /// <summary>
    /// Helper method to get a <see cref="MethodInfo"/>.
    /// </summary>
    public static MethodInfo GetMethod<TSource, TResult>(this Expression<Func<TSource, TResult>> lambda)
        => ((MethodCallExpression)(lambda.Body)).Method;

    // some well known methods
    private static readonly MethodInfo _miAny1 = GetMethod((IQueryable<int> q) => q.Any()).GetGenericMethodDefinition();
    private static readonly MethodInfo _miAny2 = GetMethod((IQueryable<int> q) => q.Any(x => false)).GetGenericMethodDefinition();
    private static readonly MethodInfo _miWhere = GetMethod((IQueryable<int> q) => q.Where(x => false)).GetGenericMethodDefinition();

    public static IQueryable<TSource> WhereNotAnyOtherThatEqualsOnCorrespondingFields<TSource, TOther>(
        this IQueryable<TSource> source,
        Expression<Func<IQueryable<TOther>>> other,
        params string[] fieldNames)
    {
        var pThis = Expression.Parameter(typeof(TSource), "pa");
        var pOther = Expression.Parameter(typeof(TOther), "pm");

        Expression anyPredicateBody = null;
        foreach (var field in fieldNames)
        {
            var equal = Expression.Equal(Expression.PropertyOrField(pThis, field), Expression.PropertyOrField(pOther, field)); 
            anyPredicateBody = anyPredicateBody == null ? equal : Expression.AndAlso(anyPredicateBody, equal);
        }

        Expression any;
        if (anyPredicateBody == null)
            any = Expression.Call(_miAny1.MakeGenericMethod(typeof(TOther)), other.Body);
        else
        {
            var anyPredicate = Expression.Quote(Expression.Lambda<Func<TOther, bool>>(anyPredicateBody, pOther));
            any = Expression.Call(_miAny2.MakeGenericMethod(typeof(TOther)), other.Body, anyPredicate);
        }

        var whereNotAnyPredicate = Expression.Quote(Expression.Lambda<Func<TSource, bool>>(Expression.Not(any), pThis));
        var whereNotAny = Expression.Call(_miWhere.MakeGenericMethod(typeof(TSource)), source.Expression, whereNotAnyPredicate);

        return source.Provider.CreateQuery<TSource>(whereNotAny);
    }
}

And here's the test (using anonymous types because lazy):

var dbMock = new
{
    PromoActivityMeasures = new[]
    {
        new { Id = 1, WorkSpaceId = 27, Organization = "Org1", MeasureCode = 4711 },
        new { Id = 2, WorkSpaceId = 28, Organization = "Org1", MeasureCode = 4711 },
        new { Id = 3, WorkSpaceId = 28, Organization = "Org2", MeasureCode = 4711 },
    }.AsQueryable(),

    PromoMeasures = new[]
    {
        new { WorkSpaceId = 28, Organization = "Org1", MeasureCode = 4711 },
    }.AsQueryable(),
};

var result = dbMock.PromoActivityMeasures
    .Where(pa => pa.WorkSpaceId == 28)
    .WhereNotAnyOtherThatEqualsOnCorrespondingFields(() => dbMock.PromoMeasures, "WorkSpaceId", "Organization", "MeasureCode")
    .Select(pa => pa.Id)
    .Single();
// 3