I have a LINQ-to-SQL query that I need to write for production code involving generics and dynamic types. With the help of others on SO, I've been able to determine that I need to create the query using expression trees. In order to get familiar with expression trees, I'm trying to write a simpler statically typed version first, but I'm having trouble figuring out the nested query part.
I've been using MSDN as a reference, primarily this article.
This is the type that will be used in the example query. It's a simplified version of a class used to create a grid with a dynamic number of columns from an SQL database.
public class PivotElement {
public int Key { get; set; }
public string DataField { get; set; }
public string ColumnText { get; set; }
}
This is the query I would like to eventually build, written in extension syntax:
IQueryable<PivotElement> iQ = ...;
var copy = iQ;
// filterColumn and filterValue are strings that were passed in
copy = copy.Where(
pe1 => theIQ.Where(
pe2 =>
pe1.Key == pe1.Key &&
pe2.DataField == filterColumn &&
pgr2.ColumnText.Contains( filterValue )
).Any()
);
This query filters out rows that don't contain the requested text in the requested column before the elements have been pivoted.
This is what I've got so far. I think it's mostly right, but I'm not sure how to indicate that the inner Where should always be called on theIQ.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
namespace IQueryableWhereTypeChange {
class Program {
static void Main( string[] args ) {
var list = new List<PivotElement>() {
new PivotElement() {
Key = 1, DataField = "FirstName", ColumnText = "Bob"
},
new PivotElement() {
Key = 1, DataField = "LastName", ColumnText = "Sanders"
},
new PivotElement() {
Key = 2, DataField = "FirstName", ColumnText = "Bob"
},
new PivotElement() {
Key = 2, DataField = "LastName", ColumnText = "Smith"
},
new PivotElement() {
Key = 3, DataField = "FirstName", ColumnText = "John"
},
new PivotElement() {
Key = 3, DataField = "LastName", ColumnText = "Smith"
}
};
var theIQ = list.AsQueryable();
var iQCopy = theIQ;
var elementType = typeof( PivotElement );
var delegateType = typeof( Func<PivotElement, bool> );
var filterColumn = "LastName";
var filterValue = "Smith";
// Query is
// iQCopy = iQCopy.Where(
// pe1 => iQ.Where(
// pe2 =>
// pe1.Key == pe1.Key &&
// pe2.DataField == filterColumn &&
// pgr2.ColumnText.Contains( filterValue )
// ).Any()
//);
// So all the elements for keys 2 and 3 should be in the
// result set, as those keys have a last name of Smith
// build pe1
Type elementType = typeof( PivotElement );
ParameterExpression pe1 = Expression.Parameter( elementType, "pe1" );
// build pe2
ParameterExpression pe2 = Expression.Parameter( elementType, "pe2" );
// pe1.Key
Expression pe1KeyProp = Expression.Property(
pe1, elementType.GetProperty( "Key" )
);
// pe2.Key
Expression pe2KeyProp = Expression.Property(
pe2, elementType.GetProperty( "Key" )
);
// build pe1.Key == pe2.Key
Expression keyEquals = Expression.Equal( pe1KeyProp, pe2KeyProp );
// build pe2.Datafield
Expression pe2Datafield = Expression.Property(
pe2, elementType.GetProperty( "DataField" )
);
// build pe2.DataField == filterColumn
Expression columnExpression = Expression.Constant( filterColumn );
Expression dataEquals = Expression.Equal(
pe2Datafield, columnExpression
);
// build pe2.ColumnText
Expression pe2ColumnText = Expression.Property(
pe2, elementType.GetProperty( "ColumnText" )
);
// build pe2.ColumnText.Contains( filterValue )
Type stringType = typeof(string);
Expression valueExpression = Expression.Constant( filterValue );
Expression textContains = Expression.Call(
pe2ColumnText,
stringType.GetMethod(
"Contains",
new Type[] { stringType } ),
new Expression[] { valueExpression }
);
// build pe1.Key == pe2.Key &&
// pe2.DataField == filterColumn &&
// pe2.ColumnText.Contains( filterValue )
Expression innerCondition = Expression.AndAlso(
keyEquals, Expression.AndAlso( dataEquals, textContains )
);
// build theIQ.Where( pe2 => innerCondition )
// build theIQ.Where( pe2 => innerCondition ).Any()
// build iQCopy.Where( pe1 => anyCall )
// create the final query
// enumerate results
foreach( var pe in results ) {
Console.WriteLine(
"Key: " + pe.Key +
", DataField: " + pe.DataField +
", ColumnText: " + pe.ColumnText
);
}
}
}
public class PivotElement {
public int Key { get; set; }
public string DataField { get; set; }
public string ColumnText { get; set; }
}
}
The missing bit hit me as I was getting that question all formatted. Since I already had it all typed up, I went ahead and posted it. Here are the missing bits, if anyone is interested:
// build theIQ.Where( pe2 => innerCondition )
Type queryableType = typeof( Queryable );
var delegateType = typeof( Func<PivotElement, bool> );
MethodCallExpression innerWhere = Expression.Call(
queryableType,
"Where",
new Type[] { elementType },
new Expression[] {
theIQ.Expression,
Expression.Lambda(
delegateType, innerCondition, new ParameterExpression[] { pe2 }
)
}
);
// build theIQ.Where( pe2 => innerWhere ).Any()
MethodCallExpression anyCall = Expression.Call(
queryableType, "Any", new Type[] { elementType }, innerWhere
);
// build iQCopy.Where( pe1 => anyCall )
MethodCallExpression outerWhere = Expression.Call(
queryableType,
"Where",
new Type[] { elementType },
new Expression[] {
iQCopy.Expression,
Expression.Lambda(
delegateType, anyCall, new ParameterExpression[] { pe1 }
)
}
);
// create the final query
var results = iQCopy.Provider.CreateQuery<PivotElement>( outerWhere );
The theIQ.Expression is what tells it to use query structure from theIQ, iQCopy.Expression is what tells it to use query structure from iQCopy, and the final iQCopy.Provider is what tells it to apply the constructed query to the actual iQCopy instance.