Convert into LINQ expression tree

Question

I have a LINQ statement I want to convert it into Expression Tree

public class tblEmpLocation
{       
    public uint EmpLocationId { get; set; }            
    public uint? EmpId { get; set; }      
    public uint? LocationId { get; set; }      
    public DateTime EffectiveDt { get; set; } = DateTime.Now;      
}

We have employee location class basically have the location the Employee Id and Location Id.

 public class temptable
{
   public uint Id{ get; set; }      
   public DateTime EffectiveDt { get; set; } 
}

We have the temp class which basically contain the Id and Effective date that is a sample class. Now we have similar table like employee Department, employee salary etc. so we want to create an linq extension that basically take the class as input and get the desired result

List<tblEmpLocation> tbls = new List<tblEmpLocation>();


var data=tbls.GroupBy(p => p.EmpId).Select(q => new temptable{ Id=q.Key, EffectiveDt=q.Max(r => r.EffectiveDt) });

thanks for help

Answer 1

This is universal implementation of DistinctBy method, which returns last record of the group.

Schematically when you make the following call:

query = query.DistinctBy(e => e.EmpId, e => e.EffectiveDt);
// or with complex keys
query = query.DistinctBy(e => new { e.EmpId, e.Other }, e => new { e.EffectiveDt, e.SomeOther});

Or fully dynamic

query = query.DistinctBy("EmpId", "EffectiveDt");

Function generates the following query:

query = 
   from d in query.Select(d => d.EmpId).Distinct()
   from e in query
        .Where(e => e.EmpId == d)
        .OrderByDescending(e => e.EffectiveDt)
        .Take(1)
    select e;

Or with complex keys:

query = 
   from d in query.Select(d => new { d.EmpId, d.Other }).Distinct()
   from e in query
        .Where(e => e.EmpId == d.EmpId && e.Other == d.Other)
        .OrderByDescending(e => e.EffectiveDt)
        .ThenByDescending(e => e.SomeOther)
        .Take(1)
    select e;

And realisation:

public static class QueryableExtensions
{
    public static IQueryable<T> DistinctBy<T>(
        this IQueryable<T> source,
        string distinctPropName,
        string maxPropName)
    {
        var entityParam = Expression.Parameter(typeof(T), "e");
        var distinctBy = Expression.Lambda(MakePropPath(entityParam, distinctPropName), entityParam);
        var maxBy = Expression.Lambda(MakePropPath(entityParam, maxPropName), entityParam);

        var queryExpression = Expression.Call(typeof(QueryableExtensions), nameof(QueryableExtensions.DistinctBy),
            new[] { typeof(T), distinctBy.Body.Type, maxBy.Body.Type },
            Expression.Constant(source),
            Expression.Quote(distinctBy),
            Expression.Quote(maxBy));

        var executionLambda = Expression.Lambda<Func<IQueryable<T>>>(queryExpression);
        return executionLambda.Compile()();
    }

    public static IQueryable<T> DistinctBy<T, TKey, TMax>(
        this IQueryable<T>        source, 
        Expression<Func<T, TKey>> distinctBy, 
        Expression<Func<T, TMax>> maxBy)
    {
        var distinctQuery = source.Select(distinctBy).Distinct();

        var distinctParam = Expression.Parameter(typeof(TKey), "d");
        var entityParam   = distinctBy.Parameters[0];

        var mapping = MapMembers(distinctBy.Body, distinctParam).ToList();

        var orderParam  = maxBy.Parameters[0];
        var oderMapping = CollectMembers(maxBy.Body).ToList();

        var whereExpr = mapping.Select(t => Expression.Equal(t.Item1, t.Item2))
            .Aggregate(Expression.AndAlso);
        var whereLambda = Expression.Lambda(whereExpr, entityParam);

        // d => query.Where(x => d.distinctBy == x.distinctBy).Take(1)
        Expression selectExpression = Expression.Call(typeof(Queryable), nameof(Queryable.Where), new[] { typeof(T) },
            source.Expression,
            whereLambda);

        // prepare OrderByPart
        for (int i = 0; i < oderMapping.Count; i++)
        {
            var orderMethod = i == 0 ? nameof(Queryable.OrderByDescending) : nameof(Queryable.ThenByDescending);

            var orderItem = oderMapping[i];
            selectExpression = Expression.Call(typeof(Queryable), orderMethod, new[] { typeof(T), orderItem.Type },
                selectExpression, Expression.Lambda(orderItem, orderParam));
        }

        // Take(1)
        selectExpression = Expression.Call(typeof(Queryable), nameof(Queryable.Take), new[] { typeof(T) },
            selectExpression,
            Expression.Constant(1));

        var selectManySelector =
            Expression.Lambda<Func<TKey, IEnumerable<T>>>(selectExpression, distinctParam);

        var selectManyQuery = Expression.Call(typeof(Queryable), nameof(Queryable.SelectMany),
            new[] { typeof(TKey), typeof(T) }, distinctQuery.Expression, selectManySelector);

        return source.Provider.CreateQuery<T>(selectManyQuery);
    }

    static Expression MakePropPath(Expression objExpression, string path)
    {
        return path.Split('.').Aggregate(objExpression, Expression.PropertyOrField);
    }

    private static IEnumerable<Tuple<Expression, Expression>> MapMembers(Expression expr, Expression projectionPath)
    {
        switch (expr.NodeType)
        {
            case ExpressionType.New:
            {
                var ne = (NewExpression)expr;

                for (int i = 0; i < ne.Arguments.Count; i++)
                {
                    foreach (var e in MapMembers(ne.Arguments[i], Expression.MakeMemberAccess(projectionPath, ne.Members[i])))
                    {
                        yield return e;
                    }
                }
                break;
            }

            default:
                yield return Tuple.Create(projectionPath, expr);
                break;
        }
    }

    private static IEnumerable<Expression> CollectMembers(Expression expr)
    {
        switch (expr.NodeType)
        {
            case ExpressionType.New:
            {
                var ne = (NewExpression)expr;

                for (int i = 0; i < ne.Arguments.Count; i++)
                {
                    yield return ne.Arguments[i];
                }
                break;
            }

            default:
                yield return expr;
                break;
        }
    }
}