IQueryable extension to group dynamicly by minutes ...and other

Question

I wanted to create an IQueryable extension to allow other developers to group entities by minutely interval but also by custom group key result.

My idea was to create a method with the following signature:

public static IQueryable<IGrouping<TKey, TSource>> GroupByMinutelyTimePeriode<TSource, TKey>(this IQueryable<TSource> source
            , Expression<Func<DateTime, TSource, TKey>> keySelector
            , Func<TSource, DateTime> timestampSelector
            , int minutes)
{

Something that you can use just like this example:

var query = dataContext.Datas
   .Where(d => d.Timestamp >= lowerTimestampRange && d.Timestamp < upperTimestampRange)
   
   ////// extension /////
   .GroupByMinutelyTimePeriode((t, d) => new
   {
       DeviceId = d.DeviceId,
       TimestampBoundary = t
   }
   , d => d.Date
   , 15)
   /////////////////////
   
   .Select(g => new
   {
       DeviceId = g.Key.DeviceId,
       Date = g.Key.TimestampBoundary,
       Value = g.Sum(d => d.Value)
   });

Within the extension something like this should happens (for sure not working because not translateable by linq-to-sql):

public static IQueryable<IGrouping<TKey, TSource>> GroupByMinutelyTimePeriode<TSource, TKey>(this IQueryable<TSource> source
            , Func<DateTime, TSource, TKey> keySelector
            , Func<TSource, DateTime> timestampSelector
            , int minutes)
    {
        return source.GroupBy(d => keySelector(new DateTime(timestampSelector(d).Year
            , timestampSelector(d).Month
            , timestampSelector(d).Day
            , timestampSelector(d).Hour
            , timestampSelector(d).Minute / minutes * minutes, 0)
            , d
            ));
    }

I must say I completely failed to translate this into a proper working IQueryable expression syntax. I try to understand how to work with Expressions from existing IQueryable extensions like GroupBy from GitHub.

Maybe someone can help me to find a good example.

Answer 1

It is possible, but instead of Func<,> you have to pass Expression<Func<,>> as parameters. Only in this case you can reuse selectors body.

Some magic with Expression Tree transformation:

public static class QueryableExtensions
{
    public static IQueryable<IGrouping<TKey, TSource>> GroupByMinutlyTimePeriode<TSource, TKey>(this IQueryable<TSource> source
        , Expression<Func<DateTime, TSource, TKey>> keySelector
        , Expression<Func<TSource, DateTime>> timestampSelector
        , int minutes)
    {
        Expression<Func<DateTime, int, DateTime>> dateTimeTemplate = (t, m) => new DateTime(t.Year
            , t.Month
            , t.Day
            , t.Hour
            , t.Minute / m * m, 0);

        var entityParam = keySelector.Parameters[1];

        var dateTimeBody =
            ExpressionReplacer.GetBody(dateTimeTemplate, ExpressionReplacer.GetBody(timestampSelector, entityParam), Expression.Constant(minutes));

        var keyBody = ExpressionReplacer.GetBody(keySelector, dateTimeBody, entityParam);
        var keyLambda = Expression.Lambda<Func<TSource, TKey>>(keyBody, entityParam);

        return source.GroupBy(keyLambda);
    }


    class ExpressionReplacer : ExpressionVisitor
    {
        readonly IDictionary<Expression, Expression> _replaceMap;

        public ExpressionReplacer(IDictionary<Expression, Expression> replaceMap)
        {
            _replaceMap = replaceMap ?? throw new ArgumentNullException(nameof(replaceMap));
        }

        public override Expression Visit(Expression node)
        {
            if (node != null && _replaceMap.TryGetValue(node, out var replacement))
                return replacement;
            return base.Visit(node);
        }

        public static Expression Replace(Expression expr, Expression toReplace, Expression toExpr)
        {
            return new ExpressionReplacer(new Dictionary<Expression, Expression> { { toReplace, toExpr } }).Visit(expr);
        }

        public static Expression Replace(Expression expr, IDictionary<Expression, Expression> replaceMap)
        {
            return new ExpressionReplacer(replaceMap).Visit(expr);
        }

        public static Expression GetBody(LambdaExpression lambda, params Expression[] toReplace)
        {
            if (lambda.Parameters.Count != toReplace.Length)
                throw new InvalidOperationException();

            return new ExpressionReplacer(Enumerable.Range(0, lambda.Parameters.Count)
                .ToDictionary(i => (Expression)lambda.Parameters[i], i => toReplace[i])).Visit(lambda.Body);
        }
    }
}