LINQ multiple join IQueryable modify result selector expression

Question

Imagine the following table structure

---------
TableA
ID
Name

---------
TableB
ID
TableAID

---------
TableC
ID
TableBID

I want to define a function that joins these three tables and accepts an Expression<Func<TableA, TableB, TableC, T>> as a selector.

So I'd like something like the following:

public IQueryable<T> GetJoinedView<T>(Expression<Func<TableA, TableB, TableC, T>> selector)
{
    return from a in DbContext.Set<TableA>()
           join b on DbContext.Set<TableB>() a.ID equals b.TableAID
           join c on DbContext.Set<TableC>() b.ID equals c.TableBID
           select selector;
}

Now, obviously the above doesn't do what I want it to do, this will give me an IQueryable of the expression type. I could use method chaining syntax, but then I end up needing multiple selectors, one for each method chain invocation. Is there a way to take the selector and apply it to an anonymous type like in the following incomplete function:

public IQueryable<T> GetJoinedView<T>(Expression<Func<TableA, TableB, TableC, T>> selector)
{
    var query = from a in DbContext.Set<TableA>()
                join b on DbContext.Set<TableB>() a.ID equals b.TableAID
                join c on DbContext.Set<TableC>() b.ID equals c.TableBID
                select new
                {
                    A = a, B = b, C = c
                };

    // I need the input selector to be modified to be able to operate on
    // the above anonymous type
    var resultSelector = ModifyInputSelectorToOperatorOnAnonymousType(selector);

    return query.Select(resultSelector);
}

Any ideas on how this could be done?

Answer 1

You can define a throwaway intermediary object to select into instead of using an anonymous type:

public class JoinedItem
{
    public TableA TableA { get; set; }
    public TableB TableB { get; set; }
    public TableC TableC { get; set; }
}

New method:

public IQueryable<T> GetJoinedView<T>(Expression<Func<JoinedItem, T>> selector)
{
    return DbContext.Set<TableA>()
                    .Join(DbContext.Set<TableB>(),
                          a => a.ID,
                          b => b.TableAID,
                          (a, b) => new { A = a, B = b})
                    .Join(DbContext.Set<TableC>(),
                          ab => ab.B.ID,
                          c => c.TableBID
                          (ab, c) => new JoinedItem
                              {
                                  TableA = ab.A,
                                  TableB = ab.B,
                                  TableC = c
                              })
                     .Select(selector);
}

Will you really be joining on these three tables enough to make the use of this method clearer than just expressing what you want to do directly in LINQ? I would argue that the extra lines needed to create this query every time would be clearer than using this method.

Answer 2

So what we can do is start out with the exact method that you have of joining the data into an anonymous object.

The first thing we'll do is start out with this simple helper class and method to allow us to replace all instance of one expression with another expression in a given expression:

public class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}

Now for our actual method. In order to map a sequence of these anonymous objects using a three parameter constructor what we can do is have our method accept an expression representing mapping the input sequence into the first parameter, as well as selectors for the other two parameters. We can then replace all instances if the first parameter in the body of the "real" selector with the body of the first parameter's selector.

Note that we need to have a parameter added to the start to allow for type inference on the anonymous type.

public static Expression<Func<TInput, TOutput>>
    ModifyInputSelectorToOperatorOnAnonymousType
    <TInput, TOutput, TParam1, TParam2, TParam3>(
    //this first param won't be used; 
    //it's here to allow type inference
    IQueryable<TInput> exampleParam,
    Expression<Func<TInput, TParam1>> firstSelector,
    Expression<Func<TInput, TParam2>> secondSelector,
    Expression<Func<TInput, TParam3>> thirdSelector,
    Expression<Func<TParam1, TParam2, TParam3, TOutput>> finalSelector)
{
    var parameter = Expression.Parameter(typeof(TInput), "param");

    var first = firstSelector.Body.Replace(firstSelector.Parameters.First(),
        parameter);
    var second = secondSelector.Body.Replace(secondSelector.Parameters.First(),
        parameter);
    var third = thirdSelector.Body.Replace(thirdSelector.Parameters.First(),
        parameter);

    var body = finalSelector.Body.Replace(finalSelector.Parameters[0], first)
        .Replace(finalSelector.Parameters[1], second)
        .Replace(finalSelector.Parameters[2], third);

    return Expression.Lambda<Func<TInput, TOutput>>(body, parameter);
}

Now to call it we can pass in the query, just to satisfy type inference, then a selector for the anonymous object's first, second, and third parameters, as well as our final selector:

var resultSelector = ModifyInputSelectorToOperatorOnAnonymousType(
    query, x => x.A, x => x.B, x => x.C, selector);

And the rest you already have.