I am working on a library that generates reports automatically. The reports are never known in advanced, but the manner in which they are called are configured in an Oracle database. Each report has its own class; in each class, there is a database query that returns a DataTable.
The report generation process works great, but I need to create a report index file for each. The configuration is also found in the database, and has the following format:
REPORT_ID / QUERY / ELEMENT_NAME
12 GROUPBY Reference 12 GROUPBY Internal_Seq 12 COUNT Item_Count 12 MIN Process_Date 12 MAX Job_Id 12 SUM Paid_Amount
Since each report has a different configuration, I need to query the contents of the datatable dynamically. That is when I saw the information from ScottGu's Blog and Help with Dynamic Linq Query to get me to where I need to be. I did modify a bit of the code to fit my needs, but this is what I have:
public void GetIndexData(DataTable p_Table)
{
string location = Utilities.GetLocation(this.GetType(), MethodBase.GetCurrentMethod());
m_log.Info(location + "Starting...");
try
{
//Create the datatable to contain the fields/aggregates from the datasource
if (m_Fields.Count > 0)
{
List<ArchiveField> groupFields = new List<ArchiveField>();
List<ArchiveField> aggreFields = new List<ArchiveField>();
foreach (ArchiveField _field in m_Fields)
{
if (_field.Query == "GROUPBY")
{
groupFields.Add(_field);
}
else
{
aggreFields.Add(_field);
}
}
//Build the GroupBy parameters using an anonymous type
List<string> fields = new List<string>();
object[] param = new object[groupFields.Count];
int counter = 0;
foreach (ArchiveField _field in groupFields)
{
fields.Add("get_Item(@" + counter.ToString() + ") as " + _field.Name);
param[counter] = _field.Name;
counter++;
}
var query = p_Table.AsEnumerable().AsQueryable().GroupBy("New(" + string.Join(",", fields.ToArray()) + ")", "it", param);
var groupType = query.ElementType;
//Build the Select parameters using Dynamic Lambda invocation
fields = new List<string>();
param = new object[aggreFields.Count];
counter = 0;
foreach (ArchiveField _field in aggreFields)
{
fields.Add("@" + counter.ToString() + "(it) as " + _field.Name);
param[counter] = GetGroupByExpression(groupType, _field.Name, typeof(long), _field.Query);
counter++;
}
foreach (ArchiveField _field in groupFields)
{
fields.Add("it.Key." + _field.Name + " as " + _field.Name);
}
query = query.Select("New(" + string.Join(",", fields.ToArray()) + ")", param);
//Convert the IQueryable to a datatable
PropertyInfo[] _props = query.ElementType.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
DataTable dt = new DataTable();
foreach (PropertyInfo p in _props)
{
dt.Columns.Add(p.Name, p.PropertyType);
}
foreach (var l in query)
{
var temp = l;
dt.Rows.Add(_props.Select((PropertyInfo p) => p.GetValue(temp, null)).ToArray());
}
m_DataSource = dt;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
m_log.Info(location + "Ending...");
}
}
private LambdaExpression GetGroupByExpression(Type p_groupType, string p_ColumnName, Type p_ColumnType, string p_Aggregate)
{
MethodInfo fieldMethod = typeof(DataRowExtensions).GetMethod("Field", new Type[] { typeof(DataRow), typeof(string) });
fieldMethod = fieldMethod.MakeGenericMethod(p_ColumnType);
ConstantExpression colParam = Expression.Constant(p_ColumnName, typeof(string));
ParameterExpression rowParam = Expression.Parameter(typeof(DataRow), "r");
MethodCallExpression fieldMethodCall = Expression.Call(fieldMethod, rowParam, colParam);
var columnExpression = Expression.Lambda(fieldMethodCall, rowParam);
ParameterExpression groupParam = Expression.Parameter(p_groupType, "g");
MethodInfo aggrMethod = null;
MethodCallExpression aggrMethodCall = null;
if (p_Aggregate.ToUpper() == "COUNT")
{
aggrMethod = typeof(Enumerable).GetMethods().Single(m => m.Name.ToUpper() == p_Aggregate.ToUpper() & m.IsStatic & m.GetParameters().Length == 1);
aggrMethod = aggrMethod.MakeGenericMethod(typeof(DataRow));
aggrMethodCall = Expression.Call(aggrMethod, groupParam);
}
else
{
aggrMethod = typeof(Enumerable).GetMethods().Single(m => m.Name.ToUpper() == p_Aggregate.ToUpper() & m.ReturnType.Equals(p_ColumnType) & m.IsGenericMethod);
aggrMethod = aggrMethod.MakeGenericMethod(typeof(DataRow));
aggrMethodCall = Expression.Call(aggrMethod, groupParam, columnExpression);
}
return Expression.Lambda(aggrMethodCall, groupParam);
}
In the code, m_DataSource is defined as a DataTable in my class, which should contain the final result.
This is an excerpt from the System.Linq.Dynamic class from Microsoft:
public static IQueryable GroupBy(this IQueryable source, string keySelector, string elementSelector, params object[] values)
{
if (source == null) throw new ArgumentNullException("source");
if (keySelector == null) throw new ArgumentNullException("keySelector");
if (elementSelector == null) throw new ArgumentNullException("elementSelector");
LambdaExpression keyLambda = DynamicExpression.ParseLambda(source.ElementType, null, keySelector, values);
LambdaExpression elementLambda = DynamicExpression.ParseLambda(source.ElementType, null, elementSelector, values);
return source.Provider.CreateQuery(
Expression.Call(
typeof(Queryable), "GroupBy",
new Type[] { source.ElementType, keyLambda.Body.Type, elementLambda.Body.Type },
source.Expression, Expression.Quote(keyLambda), Expression.Quote(elementLambda)));
}
public static IQueryable GroupBy(this IQueryable source, string keySelector, string elementSelector, params object[] values)
{
if (source == null) throw new ArgumentNullException("source");
if (keySelector == null) throw new ArgumentNullException("keySelector");
if (elementSelector == null) throw new ArgumentNullException("elementSelector");
LambdaExpression keyLambda = DynamicExpression.ParseLambda(source.ElementType, null, keySelector, values);
LambdaExpression elementLambda = DynamicExpression.ParseLambda(source.ElementType, null, elementSelector, values);
return source.Provider.CreateQuery(
Expression.Call(
typeof(Queryable), "GroupBy",
new Type[] { source.ElementType, keyLambda.Body.Type, elementLambda.Body.Type },
source.Expression, Expression.Quote(keyLambda), Expression.Quote(elementLambda)));
}
My code fails at this part, with the error "Specified cast is not valid", whenever it tries to find the elements for the query object:
foreach (var l in query)
I require a solution for this, knowing that:
- I must work in Framework 3.5 (there is no chance to change this).
- All calls are dynamic, as the report classes are called through late binding, and I never know in advance which aggregate to use (the configuration changes from one report to the next).
- I must limit the number of third-party libraries. If everything can be done within my code without external influences, the better.
- I do not have access to the database to generate a secondary query with the aggregates, as the data comes from the report class.
What am I missing to get the values necessary for my datatable? Was the original example flawed to begin with, so that the cast cannot be found that could enumerate the datarows that I need?