IQueryable performance problems with C# Expression, Lambda compile, Dictionaries

With Expressions

/** Here is the service call, the withFilters basically fills out the ChartJoin 
 * structure with the correct tables joins and where predicates
 */
public AveragePartySize[] overview(Context[] filters)
{
    using (var ctx = new FSMDbContext(dbConnection))
    {
        NameValueCollection fields = new NameValueCollection();
        fields.Add("Year", "Respondent.currentVisitYear");
        fields.Add("Month", "Respondent.currentVisitMonth");

        var grouping =
            withFilters(
                Chart.JoinBrandVisited(Enumerable.Empty<ChartJoin>().AsQueryable(), ctx), 
                ctx, 
                filters
            )
            .Where(x => x.Respondent.status == 1)
            .GroupBy(
                Chart.GetGroupByDictionary(fields).Compile(),
                new DictionaryComparer<string, object>()
            );

        var ordering = Chart.GetOrdering(fields, grouping);

        return ordering
            .Select(
                GetAveragePartySizeSelector().Compile()
            )
            .ToArray();
    }
}

/**
 * This is what creates the GroupBy dictionary
 */
public static Expression<Func<ChartJoin, Dictionary<string, object>>> GetGroupByDictionary(NameValueCollection fields)
{
    var parameter = Expression.Parameter(typeof(ChartJoin));

    var addMethod = typeof(Dictionary<string, object>)
        .GetMethod(
            "Add",
            new[] { typeof(string), typeof(object) }
        );

    var selector = Expression.ListInit(
        Expression.New(typeof(Dictionary<string, object>)),
        fields.AllKeys.Select(
            key => Expression.ElementInit(
                addMethod,
                Expression.Constant(key),
                Expression.Convert(
                    Chart.getNestedPropertyOrField(parameter, fields[key]),
                    typeof(object)
                )
            )
        )
    );

    var lambda = Expression.Lambda<Func<ChartJoin, Dictionary<string, object>>>(selector, parameter);

    return lambda;
}

private static Expression getNestedPropertyOrField(ParameterExpression context, String path)
{
    MemberExpression member = null;

    try
    {
        foreach (string field in path.Split(new Char[] { '.' }))
            member = member == null ? Expression.PropertyOrField(context, field) : Expression.PropertyOrField(member, field);
    }
    catch (ArgumentException e)
    {
        return Expression.Throw(Expression.Constant(e));
    }


    return member;
}

/**
 * Creates the OrderBy and ThenBy clauses
 */ 
 public static IOrderedEnumerable<IGrouping<IDictionary<string, object>, ChartJoin>> GetOrdering(NameValueCollection fields, IEnumerable<IGrouping<IDictionary<string, object>, ChartJoin>> grouping) 
 {
    var ordering = grouping
            .OrderBy(x => x.Key[fields.GetKey(0)]);
  
    for (int index = 1; index < fields.Count; index++)
    {
        int other = new int();  // resolution of the Expression at a later date would mean everything keyed of "index" would be the last value of index
        other = index;
  
        ordering = ordering
            .ThenBy(x => x.Key[fields.GetKey(other)]);
    }
  
    return ordering;
  }
  
  
/**
 * Select clause
 */
 public static Expression<Func<IGrouping<IDictionary<string, object>, ChartJoin>, AveragePartySize>> GetAveragePartySizeSelector()
{
    // x => 
    var ParameterType = typeof(IGrouping<IDictionary<string, object>, ChartJoin>);
    var parameter = Expression.Parameter(ParameterType);

    // x => x.Sum(m => (m.BrandVisited.NUM_PAID * m.Respondent.MWEIGHT)) / x.Sum(m => m.Respondent.MWEIGHT)
    var m = Expression.Parameter(typeof(ChartJoin), "m");

    var mRespondent = Expression.PropertyOrField(m, "Respondent");
    var mBrandVisited = Expression.PropertyOrField(m, "BrandVisited");

    PropertyInfo MWeightPropertyInfo = typeof(Respondent).GetProperty("MWEIGHT");
    PropertyInfo NumPaidPropertyInfo = typeof(BrandVisited).GetProperty("NUM_PAID");

    LambdaExpression SumNumeratorSelector = Expression.Lambda(
        Expression.Convert(
            Expression.Multiply(
                Expression.Convert(
                    Expression.MakeMemberAccess(mRespondent, MWeightPropertyInfo),
                    typeof(double)
                ),
                Expression.Convert(
                    Expression.MakeMemberAccess(mBrandVisited, NumPaidPropertyInfo),
                    typeof(double)
                )
            ),
            typeof(double)
        ),
        m
    );

    var SumNumeratorExpression = Sum(parameter, SumNumeratorSelector, typeof(double));


    LambdaExpression SumDenominatorSelector = Expression.Lambda(
        Expression.Convert(
            Expression.MakeMemberAccess(mRespondent, MWeightPropertyInfo),
            typeof(double)
        ),
        m
    );

    MethodInfo SumDenominatorMethod = (typeof(Enumerable))
        .GetMethods()
        .First(
            method => method.Name == "Sum"
                && method.ReturnType == typeof(double)
                && method.IsGenericMethod
        )
        .MakeGenericMethod(typeof(ChartJoin));

    var SumDenominatorExpression = Expression.Call(null, SumDenominatorMethod, parameter, SumDenominatorSelector);

    var FractionExpression = Expression.Divide(SumNumeratorExpression, SumDenominatorExpression);


    // Results
    MemberInfo PartySize = typeof(AveragePartySize).GetMember("PartySize")[0];
    MemberBinding Measurement = Expression.Bind(
        PartySize,
        Expression.Convert(FractionExpression, typeof(double))
    );

    MemberInitExpression selector = Results(parameter, typeof(AveragePartySize), Measurement);


    // Lambda
    var lambda = Expression.Lambda<Func<IGrouping<IDictionary<string, object>, ChartJoin>, AveragePartySize>>(selector, parameter);
    return lambda;
}
}

Working

var query = ctx.Respondents
      .Join(
          ctx.Respondents,
          other => other.RespondentId,
          res => res.RespondentId,
          (other, res) => new ChartJoin { Respondent = res, Occasion = null, BrandVisited = null, BrandInfo = null, Party = null, Item = null }
      )
      .Join(
          ctx.Occasions,
          join => join.Respondent.RespondentId,
          occ => occ.RespondentId,
          (join, occ) => new ChartJoin { Respondent = join.Respondent, Occasion = occ, BrandVisited = join.BrandVisited, BrandInfo = join.BrandInfo, Party = join.Party, Item = join.Item }
      )
      .Join(
          ctx.BrandVisits,
          join => new { RespondentId = join.Occasion.RespondentId, OccasionId = join.Occasion.OccasionId },
          bv => new { RespondentId = bv.RespondentId, OccasionId = bv.OccasionId },
          (join, bv) => new ChartJoin { Respondent = join.Respondent, Occasion = join.Occasion, BrandVisited = bv, BrandInfo = join.BrandInfo, Party = join.Party, Item = join.Item }
      )
      .Join(
          ctx.Brands,
          join => new { BrandId = join.BrandVisited.BrandId },
          bi => new { BrandId = bi.BrandId },
          (join, bi) => new ChartJoin { Respondent = join.Respondent, Occasion = join.Occasion, BrandVisited = join.BrandVisited, BrandInfo = bi, Party = join.Party, Item = join.Item }
      )
      .Where(x => x.BrandInfo.englishName == "Burger King" && x.Respondent.currentDateYear == 2014 && x.Respondent.currentVisitMonth >= 2 && x.Respondent.currentVisitMonth <= 8)
      .Where(x => x.Respondent.status == 1)
      .GroupBy(x => new CommonGroupBy { Year = (int)x.Respondent.currentVisitYear, Month = (int)x.Respondent.currentVisitMonth })
      .OrderBy(x => x.Key.Year)
      .ThenBy(x => x.Key.Month)
      .Select(x => new AveragePartySize
      {
          Year = x.Key.Year,
          Month = x.Key.Month,
          PartySize = (double)(x.Sum(m => (m.BrandVisited.NUM_PAID * m.Respondent.MWEIGHT)) / x.Sum(m => m.Respondent.MWEIGHT)),
          Base = x.Count(),
          Days = x.Select(m => m.Respondent.visitDate).Distinct().Count()
      });