LINQ multiple join IQueryable change result selection 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 Expression<Func<TableA, TableB, TableC, T>> as a selector.

So, I would 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, this does not do what I want it to do, it will give me an IQueryable type of expression. I could use the method chain syntax, but then I need several selectors, one for each method chain call. Is there a way to make a selector and apply it to an anonymous type, for example, 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 to do this?

Answer 1

You can define an intermediate intermediate object for selection 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, (ab) => 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 join these three tables to make this method clearer than just expressing what you want to do directly in LINQ? I would say 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 with the exact method that you bind to the data in an anonymous object.

The first thing we will do is start with this simple helper class and method, so that we can replace all instances of one expression with another expression in this 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. To match the sequence of these anonymous objects using the three parameter constructors, we can take our method, which takes an expression representing the mapping of the input sequence to the first parameter, as well as selectors for the other two parameters. Then we can replace all instances if the first parameter in the body is a "real" selector with the body of the first parameter selector.

Note that we need to add a parameter to the beginning to enable type inference for an anonymous type.

 public static Expression<Func<TInput, TOutput>> ModifyInputSelectorToOperatorOnAnonymousType <TInput, TOutput, TParam1, TParam2, TParam3>( //this first param won't be used; //it 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 the request, just to satisfy the type inference, then the selector for the anonymous object, the first, second and third parameters, as well as our final selector:

 var resultSelector = ModifyInputSelectorToOperatorOnAnonymousType( query, x => xA, x => xB, x => xC, selector); 

And you already have the rest.

Answer 3

This may not be the solution you are looking for, but I will post it:

I would recommend a DataModel for every “selection” you make in your database, for example:

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

your 'select' does what 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” that represents your “selector”, or at least what you think you have in the 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 matching method:

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

then you can just call:

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

I know that you want to pass some kind of Expression into a method, but I think that this will not work, but maybe someone came up with a solution.