Out of Memory Lambda Compile versus inline delegates

Question

Using 4.5.1 with an application that on the server side shuffles chart data with many REST requests simultaneously.

Use IQueryable to build queries. For example, I originally had the following:

 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 }
   )
   . // bunch of other joins filling out the ChartJoin
   .Where(x => x.Respondent.status == 1)
   . // more Where clauses dynamically applied
   .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 AverageEaterCheque
     {
       Year = x.Key.Year,
       Month = x.Key.Month,
       AverageCheque = (double)(x.Sum(m => m.BrandVisited.DOLLAR_TOTAL) / x.Sum(m => m.BrandVisited.NUM_PAID)),
       Base = x.Count(),
       Days = x.Select(m => m.Respondent.visitDate).Distinct().Count()
   });

To allow for dynamic grouping (via the client), the GroupBy was generated with C# expressions returning a Dictionary. The Select also had to be generated with expressions. The above Select became something like:

 public static Expression<Func<IGrouping<IDictionary<string, object>, ChartJoin>, AverageEaterCheque>> GetAverageEaterChequeSelector()
 {
    // x => 
    var ParameterType = typeof(IGrouping<IDictionary<string, object>, ChartJoin>);
    var parameter = Expression.Parameter(ParameterType);

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

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

    PropertyInfo DollarTotalPropertyInfo = typeof(BrandVisited).GetProperty("DOLLAR_TOTAL");
    PropertyInfo NumPaidPropertyInfo = typeof(BrandVisited).GetProperty("NUM_PAID");

    ....

    return a lambda...
 }

When I did a test run locally I got an Out of Memory error. Then I started reading blogs from Totin and others that Lambda compiles, expression trees in general are expensive. Had no idea it would blow my application. And I need the ability to dynamically add grouping which lead me to using Expression trees for the GroupBy and Select clauses.

Would love some pointers on how to chase down the memory offenders in my application? Have seen some people use dotMemory but would be great with some practical tips as well. Very little experience in monitoring C#, DotNet.

Answer 1

Since you're compiling the expression into a delegate, the operation is performed using LINQ to Objects, rather than using the IQueryable overload. This means that the entirety of the data set is being pulled into memory, and all of the processing done by the application, instead of that processing being done in the database and only the final results being sent to the application.

Apparently pulling down the entire table into memory is enough to run your application out of memory.

You need to not compile the lambda, and leave it as an expression, thus allowing the query provider to translate it into SQL, as is done with your original code.