Build Dynamic Select using Expression Trees
c#, entity-framework, expression-trees, linq-to-entities, webforms
Solution
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;
}
Problem
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?