Build Dynamic Select using Expression Trees

Question

How would I generate the following using expression trees...

var people = context.Set<Person>();
var transactions = context.Set<FinancialTransaction>();

var dataview = people.Where( p => p.LastName == "Smith" );

var selection = dataview
        .Select( p => new
        {
            FirstName = p.FirstName,
            LastName = p.LastName,
            LastTransaction =
                transactions
                    .Where( t => t.AuthorizedPersonId == p.Id )
                    .Max( t => t.TransactionDateTime )
        } );

gReport.AutoGenerateColumns = true;
gReport.DataSource = selection.ToList();
gReport.DataBind();

I'm trying to use the LinqRuntimeTypeBuilder solution that Ethan Brown provided here but struggling with how to create the expression for the LastTransaction sub-query and how to bind the query to the GridView.

This is what I have so far...

var people = context.Set<Person>();
var transactions = context.Set<FinancialTransaction>();

var dataview = people.Where( p => p.LastName == "Smith" );

var dynamicFields = new Dictionary<string, Type>();
dynamicFields.Add( "FirstName", typeof( string ) );
dynamicFields.Add( "LastName", typeof( string ) );
dynamicFields.Add( "LastTransaction", typeof( DateTime? ) );

Type dynamicType = Rock.Data.LinqRuntimeTypeBuilder.GetDynamicType( dynamicFields );

ParameterExpression sourceItem = Expression.Parameter( dataview.ElementType, "x" );

// Is this right? if if so how do I bind it to the dynamic field????
Expression<Func<Person, DateTime>> lastTransactionSelect = a => transactions.Where( t => t.AuthorizedPersonId == a.Id && t.TransactionDateTime.HasValue ).Max( t => t.TransactionDateTime.Value );

var bindings = new List<MemberBinding>();
bindings.Add( Expression.Bind( dynamicType.GetField( "FirstName" ), Expression.Property( sourceItem, dataview.ElementType.GetProperty( "FirstName" ) ) ) );
bindings.Add( Expression.Bind( dynamicType.GetField( "LastName" ), Expression.Property( sourceItem, dataview.ElementType.GetProperty( "LastName" ) ) ) );
bindings.Add( Expression.Bind( dynamicType.GetField( "LastTransaction" ), ??? ) );

Expression selector = Expression.Lambda( Expression.MemberInit( Expression.New( dynamicType.GetConstructor( Type.EmptyTypes ) ), bindings ), sourceItem );

var query = dataview.Provider.CreateQuery(
    Expression.Call(
        typeof( Queryable ),
        "Select",
        new Type[] { dataview.ElementType, dynamicType },
    Expression.Constant( dataview ), selector ) ).AsNoTracking();

// Can't bind directly to the query since it's a DBQuery object
gReport.DataSource = ???;

gReport.DataBind();

How can I create the expression for the sub-query, and then also what's the best way to bind the query to the GridView?

Not sure what you meant by GUI? Do you mean the IDE? If so, VS 2013. — David Turner
– David Turner, Commented Dec 6, 2013 at 16:21
No, you're binding it to something right? WPF, WinForms, ASPX? — Henk Holterman
– Henk Holterman, Commented Dec 7, 2013 at 12:26
This is an ASP.NET Web Forms app. I'm binding the query results to a System.Web.UI.WebControls.GridView, but what I really need to figure out is how to build the expression tree for creating an anonymous type that includes a sub-query. We're also using Code-First development with EF6. — David Turner
– David Turner, Commented Dec 7, 2013 at 13:44

David Turner · Accepted Answer · 2013-12-09 06:44:21Z

After using Reflector to evaluate how the compiler generated the linq statement, here's how I ended up creating the expression for the sub-select...

ParameterExpression transactionParameter = Expression.Parameter(typeof(FinancialTransaction), "t");
MemberExpression authorizedPersonIdProperty = Expression.Property(transactionParameter, "AuthorizedPersonId");
MemberExpression transactionDateTime = Expression.Property(transactionParameter,"TransactionDateTime");

MethodInfo whereMethod = GetWhereMethod();
MethodInfo maxMethod = GetMaxMethod();

var personIdCompare = new Expression[] { 
    Expression.Constant(transactions), 
    Expression.Lambda<Func<FinancialTransaction, bool>>( Expression.Equal(authorizedPersonIdProperty, Expression.Convert(idProperty, typeof(int?))), new ParameterExpression[] { transactionParameter } ) 
};
var transactionDate = Expression.Lambda<Func<FinancialTransaction, DateTime?>>( transactionDateTime, new ParameterExpression[] { transactionParameter } );
var lastTransactionDate = Expression.Call( null, maxMethod, new Expression[] { Expression.Call( null, whereMethod, personIdCompare ), transactionDate } );

...

bindings.Add( Expression.Bind( dynamicType.GetField( "LastTransaction" ), lastTransactionDate ) );


...


private MethodInfo GetWhereMethod()
{
    Func<FinancialTransaction, bool> fake = element => default( bool );
    Expression<Func<IEnumerable<FinancialTransaction>, IEnumerable<FinancialTransaction>>> lamda = list => list.Where( fake );
    return ( lamda.Body as MethodCallExpression ).Method;
}

private MethodInfo GetMaxMethod()
{
    Func<FinancialTransaction, DateTime?> fake = element => default( DateTime? );
    Expression<Func<IEnumerable<FinancialTransaction>, DateTime?>> lamda = list => list.Max( fake );
    return ( lamda.Body as MethodCallExpression ).Method;
}

Mike Peterson · Accepted Answer · 2013-12-12 17:54:51Z

0

I know your main question is about building a dynamic linq expression tree, but I might be able to help with your secondary question about binding the Queryable to the grid.

EDIT: Sorry, I actually tried this and doing OfType< object > generated a Casting exception, so here is something that will actually work

var query = dataview.Provider.CreateQuery(
    Expression.Call(
        typeof( Queryable ),
        "Select",
        new Type[] { dataview.ElementType, dynamicType },
    Expression.Constant( dataview ), selector ) ).AsNoTracking();

// enumerate thru the query results and put into a list
var listResult = new List<object>();
var enumerator = query.GetEnumerator();
while ( enumerator.MoveNext() )
{
    reportResult.Add( enumerator.Current );
}

gReport.DataSource = listResult;

gReport.DataBind();

edited Dec 12, 2013 at 17:54

answered Dec 6, 2013 at 17:32

Mike Peterson

1,0898 silver badges9 bronze badges

1 Comment

David Turner Over a year ago

Thanks Mike, that helps with binding to the GridView, but yeah, my main question and biggest struggle is how to build the expression tree (I know I shouldn't have asked two questions in one, sorry.)

Collectives™ on Stack Overflow

Build Dynamic Select using Expression Trees

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related