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?

Pubilis

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.

Estep

The method would be called from several different places and needs to be able to perform the select on the database. I don't think the solution provided will run on the database, might actually get a TargetInvocationException – Pubilis

I stand corrected, EF does allow one to use unmapped types in the IQueryable expression trees. – Pubilis

Yeah, as long as the resulting expression tree can be converted to SQL it's all good. – Estep

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.

Sverdlovsk

Apologies for digging up dead bodies, but I wanted to personally thank Servy for expanding my mind when it comes to solving problems that go against the grain. This single post sparked a life long interest in the meta and has led to incredibly novel, performant and elegant solutions to otherwise extremely complex problems. I find hindsight appreciation isn't shared often enough on SO (or anywhere really) -- so with that in mind, thank you @Sverdlovsk for opening my eyes to the vast solution spaces I had never considered before fully understanding the beauty and flexibility of C#'s expression trees. – Pubilis

@Pubilis I appreciate that. – Sverdlovsk

Answer 3

Maybe it is not the solution you are looking for, but i will post it:

I would recommend a DataModel for each 'select' you perform against your database like the following:

 public class JoinedDataModel
 {
     public TableA DataA { get; set; }
     public TableB DataB { get; set; }
     public TableC DataC { get; set; }
 }

your 'select' does the same as you already do

public IQueryable<JoinedDataModel> GetJoinedView( )
{
    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 new JoinedDataModel( )
           {
                DataA = a,
                DataB = b,
                DataC = c
           };
}

and then you need some kind of 'mapper' which represents your 'selector' or at least what i think you mean with selector:

public static Mapper( )
{
    private static Dictionary<MapTuple, object> _maps = new Dictionary<MapTuple, object>();

    public static void AddMap<TFrom, TTo>(Action<TFrom, TTo, DateTime> map)
    {
        Mapper._maps.Add(MapTuple.Create(typeof(TFrom), typeof(TTo)), map);
    }

    public static TTo Map<TFrom, TTo>( TFrom srcObj )
    {
        var typeFrom = typeof(TFrom);
        var typeTo = typeof(TTo);
        var key = MapTuple.Create(typeFrom, typeTo);
        var map = (Action<TFrom, TTo, DateTime>) Mapper._maps[key];

        TTo targetObj = new TTo( );

        map( srcObj, targetObj );

        return targetObj;
    }

then you need to define at least one mapping method:

AddMap<JoinedDataModel, YourResultModel>( 
    ( src, trg ) =>
    {
        trg.SomePropertyA = src.DataA.SomeProperty;
        trg.SomePropertyB = src.DataB.SomeProperty;
    }
);

then you can simply call:

 public IList<YourResultModel> CallDb( )
 {
      return ( from item in GetJoinedView( )
               select Mapper.MapTo<JoinedDataModel, YourResultModel>( item ) 
             ).ToList( );
 }

i know that you want to pass in some kind of Expression into the method but i think this isn't going to work, but maybe someone came up with a solution.

Esra