LINQ Expression Tree Any () inside Where ()

Question

I am trying to create 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 to do this when I need to use reflection and runtime types). Note that in real code (see below), AdAccountAlert is actually dynamic through reflection and the for loop.

My problem : I can generate everything before the .Where () clause. The whereExpression method call explodes due to incompatible types. Usually I know what to put there, but calling the Any () method confuses me. I tried every type that I can think of, and no luck. Any help with the .Where () and .ToDictionary () parameters would be appreciated.

Here is what I still have:

 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 is after ToList() and IQueryable<T> not included, but on IEnumerable<T> . Because of this, there is no need to create expression trees. Of course, this is not something that is interpreted by EF or similar.

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

Example:

The following code:

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

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

Notice how it generates expressions for everyone before ToList . Everything after, including the usual calls to extension methods.

If you don't imitate this in your code, you are actually sending an Enumerable.ToList call to the LINQ provider, which then tries to convert to SQL and fail.

Answer 2

It seems that when building whereLambda your second parameter should be subscribersParameter , not subscriptionParameter . At least that would be the reason for your exclusion.