LINQ Expression Tree Any() inside Where()

Question

I'm trying to generate the following LINQ query:

//Query the database for all AdAccountAlerts that haven't had notifications sent out
//Then get the entity (AdAccount) the alert pertains to, and find all accounts that
//are subscribing to alerts on that entity.
var x = dataContext.Alerts.Where(a => a.NotificationsSent == null)
  .OfType<AdAccountAlert>()
  .ToList()
  .GroupJoin(dataContext.AlertSubscriptions,
    a => new Tuple<int, string>(a.AdAccountId, typeof(AdAccount).Name),
    s => new Tuple<int, string>(s.EntityId, s.EntityType),
    (Alert, Subscribers) => new Tuple<AdAccountAlert, IEnumerable<AlertSubscription>> (Alert, Subscribers))
  .Where(s => s.Item2.Any())
  .ToDictionary(kvp => (Alert)kvp.Item1, kvp => kvp.Item2.Select(s => s.Username));

Using Expression Trees (which seems to be the only way I can do this when I need to use reflection and run-time types). Note that in the real code (see below) the AdAccountAlert is actually dynamic through reflection and a for-loop.

My problem: I can generate everything up to the .Where() clause. The whereExpression method call blows up because of incompatible types. Normally I know what to put there, but the Any() method call has me confused. I've tried every type I can think of and no luck. Any help with both the .Where() and .ToDictionary() would be appreciated.

Here's what I have so far:

var alertTypes = AppDomain.CurrentDomain.GetAssemblies()
  .Single(a => a.FullName.StartsWith("Alerts.Entities"))
  .GetTypes()
  .Where(t => typeof(Alert).IsAssignableFrom(t) && !t.IsAbstract && !t.IsInterface);

var alertSubscribers = new Dictionary<Alert, IEnumerable<string>>();

//Using tuples for joins to keep everything strongly-typed
var subscribableType = typeof(Tuple<int, string>);
var doubleTuple = Type.GetType("System.Tuple`2, mscorlib", true);

foreach (var alertType in alertTypes)
{
  Type foreignKeyType = GetForeignKeyType(alertType);
  if (foreignKeyType == null)
    continue;

  IQueryable<Alert> unnotifiedAlerts = dataContext.Alerts.Where(a => a.NotificationsSent == null);

  //Generates: .OfType<alertType>()
  MethodCallExpression alertsOfType = Expression.Call(typeof(Enumerable).GetMethod("OfType").MakeGenericMethod(alertType), unnotifiedAlerts.Expression);

  //Generates: .ToList(), which is required for joins on Tuples
  MethodCallExpression unnotifiedAlertsList = Expression.Call(typeof(Enumerable).GetMethod("ToList").MakeGenericMethod(alertType), alertsOfType);

  //Generates: a => new { a.{EntityId}, EntityType = typeof(AdAccount).Name }
  ParameterExpression alertParameter = Expression.Parameter(alertType, "a");
  MemberExpression adAccountId = Expression.Property(alertParameter, alertType.GetProperty(alertType.GetForeignKeyId()));
  NewExpression outerJoinObject = Expression.New(subscribableType.GetConstructor(new Type[] { typeof(int), typeof(string)}), adAccountId, Expression.Constant(foreignKeyType.Name));
  LambdaExpression outerSelector = Expression.Lambda(outerJoinObject, alertParameter);

  //Generates: s => new { s.EntityId, s.EntityType }
  Type alertSubscriptionType = typeof(AlertSubscription);
  ParameterExpression subscriptionParameter = Expression.Parameter(alertSubscriptionType, "s");
  MemberExpression entityId = Expression.Property(subscriptionParameter, alertSubscriptionType.GetProperty("EntityId"));
  MemberExpression entityType = Expression.Property(subscriptionParameter, alertSubscriptionType.GetProperty("EntityType"));
  NewExpression innerJoinObject = Expression.New(subscribableType.GetConstructor(new Type[] { typeof(int), typeof(string) }), entityId, entityType);
  LambdaExpression innerSelector = Expression.Lambda(innerJoinObject, subscriptionParameter);

  //Generates: (Alert, Subscribers) => new Tuple<Alert, IEnumerable<AlertSubscription>>(Alert, Subscribers)
  var joinResultType = doubleTuple.MakeGenericType(new Type[] { alertType, typeof(IEnumerable<AlertSubscription>) });
  ParameterExpression alertTupleParameter = Expression.Parameter(alertType, "Alert");
  ParameterExpression subscribersTupleParameter = Expression.Parameter(typeof(IEnumerable<AlertSubscription>), "Subscribers");
  NewExpression joinResultObject = Expression.New(
    joinResultType.GetConstructor(new Type[] { alertType, typeof(IEnumerable<AlertSubscription>) }),
    alertTupleParameter,
    subscribersTupleParameter);

  LambdaExpression resultsSelector = Expression.Lambda(joinResultObject, alertTupleParameter, subscribersTupleParameter);

  //Generates:
  //  .GroupJoin(dataContext.AlertSubscriptions,
  //    a => new { a.AdAccountId, typeof(AdAccount).Name },
  //    s => new { s.EntityId, s.EntityType },
  //    (Alert, Subscribers) => new Tuple<Alert, IEnumerable<AlertSubscription>>(Alert, Subscribers))
  IQueryable<AlertSubscription> alertSubscriptions = dataContext.AlertSubscriptions.AsQueryable();
  MethodCallExpression joinExpression = Expression.Call(typeof(Enumerable),
    "GroupJoin",
    new Type[]
    {
      alertType,
      alertSubscriptions.ElementType,
      outerSelector.Body.Type,
      resultsSelector.ReturnType
    },
    unnotifiedAlertsList,
    alertSubscriptions.Expression,
    outerSelector,
    innerSelector,
    resultsSelector);

  //Generates: .Where(s => s.Item2.Any())
  ParameterExpression subscribersParameter = Expression.Parameter(resultsSelector.ReturnType, "s");
  MemberExpression tupleSubscribers = Expression.Property(subscribersParameter, resultsSelector.ReturnType.GetProperty("Item2"));
  MethodCallExpression hasSubscribers = Expression.Call(typeof(Enumerable),
    "Any",
    new Type[] { alertSubscriptions.ElementType },
    tupleSubscribers);
  LambdaExpression whereLambda = Expression.Lambda(hasSubscribers, subscriptionParameter);
  MethodCallExpression whereExpression = Expression.Call(typeof(Enumerable),
    "Where",
    new Type[] { joinResultType },
    joinExpression,
    whereLambda);

Answer 1

Please note: Everything after and including ToList() won't work on IQueryable<T> but on IEnumerable<T>. Because of this, there is no need to create expression trees. It certainly is nothing that is interpreted by EF or similar.

If you would look at the code that is generated by the compiler for your original query, you would see that it generates expression trees only until just before the first call to ToList.

Example:

The following code:

var query = new List<int>().AsQueryable();
query.Where(x => x > 0).ToList().FirstOrDefault(x => x > 10);

Is translated by the compiler to this:

IQueryable<int> query = new List<int>().AsQueryable<int>();
IQueryable<int> arg_4D_0 = query;
ParameterExpression parameterExpression = Expression.Parameter(typeof(int), "x");
arg_4D_0.Where(Expression.Lambda<Func<int, bool>>(Expression.GreaterThan(parameterExpression, Expression.Constant(0, typeof(int))), new ParameterExpression[]
{
    parameterExpression
})).ToList<int>().FirstOrDefault((int x) => x > 10);

Please note how it generates expressions for everything up to ToList. Everything after and including it are simply normal calls to extension methods.

If you don't mimick this in your code, you will actually send a call to Enumerable.ToList to the LINQ provider - which it then tries to convert to SQL and fail.