I am using LINQ to Objects to aggregate:
var summary = from esc in objs
where esc.time.Month == month && esc.time.Year == year
group esc by esc.rlf_id into g
select new {
ID = g.Key,
Total = g.Count(),
Preventable = g.Where(a => a.preventable).Count()
};
My query works as I would expect, but I also want to order the query by arbitrary field(s) in the anonymous type. I found LINQ: Order By Anonymous Type, but it's in VB.NET and requires strongly specifying what field to sort by. I can conceptualize what I want to accompilish with this pseudocode:
query = get all esc in obj
aggregate into anonymous type with key ID
ID = g.Key, Total = g.Count, Preventable = g.Count of preventable
orderby inputField[0], (optional thenby inputField[1], ..., thenby inputField[n])
How do I accomplish:
I am open to dot syntax or query syntax.
Edit: With Marko Stanojevic's answer, I am able to partially meet my requirements. I was unaware that I am able to chain LINQ methods together like that. I now am able to (and get expected results from):
var summary = from esc in objs
where esc.time.Month == month && esc.time.Year == year
group esc by esc.rlf_id into g
select new {
ID = g.Key,
Total = g.Count(),
Preventable = g.Where(a => a.preventable).Count()
};
summary = summary.OrderBy(e => e.Total);
What I need is something that lets me do: (pseudocode)
summary = summary.<Order/Then>By<(optional)Descending>(e => e.<someUserInput>)
Given some string that specifies how the user wants to sort, obviously I can do:
if (sortQuery.Equals("Total", StringComparison.OrdinalIgnoresCase), bool descending) {
summary = descending ? summary.OrderByDescending(e => e.Total) : summary.OrderBy(e => e.total)
} else if (sortQuery.Equals( /* ... etc */
However, this ends up being ugly quick-like, especially because I would like to use this for a (potentially nearly infinite) growing number of queries. It would also need to account for OrderBy() versus ThenBy().
I wish I was working with C# 4 so I could use dynamic right about now...
The fact that the data type is anonymous is not important and does not change the problem. An anonymous type is a type as another (it just has a special name). Like other types it is completely known at compile time! You can read Anonymous Type vs Dynamic Type to learn more about difference between anonymous and dynamic types.
The difficulty is that the methods (eg OrderBy or OrderByDescending) to invoke and their parameters (eg the keySelector item => item.MyFieldName) are known only at runtime.
The solution is to use Reflection.
The code below implements the OrderByRules function as an extension method that applies to any collection of type IQueryable<T> (So, to any collection of type IEnumerable<T> using simply the AsQueryable<T>() operator.
The first rule is processed in a special way to use the OrderBy operator and not ThenBy. Then the others are processed recursively.
The call to sort operator is performed in the function OrderByFieldOrPropertyName. From the field or property reflection information, we construct a lambda expression of the form item => item.fieldName. The MakeGenericMethod function is used to construct the concrete method. Basically, it allows you to switch from OrderBy<T> to OrderBy<MyData>.
I hope that answers your question.
/// <summary>
/// Express an order rule based on property name
/// </summary>
public class OrderRule
{
public OrderRule(string fieldOrPropertyName, bool descending)
{
FieldOrPropertyName = fieldOrPropertyName;
Descending = descending;
}
public string FieldOrPropertyName { get; private set; }
public bool Descending { get; private set; }
}
/// <summary>
/// Static class holding the OrderByRules extension method
/// </summary>
static public class MyLINQExtensions
{
/// <summary>
/// Order <paramref name="dataCollection"/> according to <paramref name="rules"/> sequence
/// </summary>
/// <typeparam name="T">Collection item type</typeparam>
/// <param name="dataCollection">Queryable collection</param>
/// <param name="rules">Order rules to apply</param>
/// <returns>Ordered queryable collection</returns>
public static IOrderedQueryable<T> OrderByRules<T>(this IQueryable<T> dataCollection, IEnumerable<OrderRule> rules)
{
if (!rules.Any())
throw new ArgumentException("Rules list is empty", "rules");
// apply first rule (special case: use OrderBy operator and not ThenBy)
OrderRule rule = rules.First();
MethodInfo orderOperator = rule.Descending ? OrderByDescendingMethodInfo : OrderByMethodInfo;
IOrderedQueryable<T> orderedDataCollection = OrderByFieldOrPropertyName(dataCollection, orderOperator, rule.FieldOrPropertyName);
// apply next rules recursivly
return OrderByRulesRecursivly(orderedDataCollection, rules.Skip(1).ToList());
}
static IOrderedQueryable<T> OrderByFieldOrPropertyName<T>(IQueryable<T> dataCollection, MethodInfo orderOperator, string fieldOrPropertyName)
{
// member corresponding to fieldOrPropertyName
MemberInfo memberInfo = typeof(T).GetField(fieldOrPropertyName);
Type memberType = null;
if (memberInfo == null)
memberInfo = typeof(T).GetProperty(fieldOrPropertyName);
else
memberType = (memberInfo as FieldInfo).FieldType;
if (memberInfo == null)
throw new ArgumentException(String.Format("Field or property '{0}' doesn't exist on type '{1}'", fieldOrPropertyName, typeof(T)));
else
memberType = (memberInfo as PropertyInfo).PropertyType;
// build lambda expression: item => item.fieldName
ParameterExpression paramExp = Expression.Parameter(typeof(T));
LambdaExpression keySelectorExp = Expression.Lambda(Expression.MakeMemberAccess(paramExp, memberInfo), paramExp);
// build concrete MethodInfo from the generic one
orderOperator = orderOperator.MakeGenericMethod(typeof(T), memberType);
// invoke method on dataCollection
return orderOperator.Invoke(null, new object[] {
dataCollection,
keySelectorExp
}) as IOrderedQueryable<T>;
}
static IOrderedQueryable<T> OrderByRulesRecursivly<T>(IOrderedQueryable<T> dataCollection, List<OrderRule> rules)
{
if (!rules.Any())
return dataCollection;
// apply first rule
OrderRule rule = rules.First();
MethodInfo orderOperator = rule.Descending ? ThenByDescendingMethodInfo : ThenByMethodInfo;
IOrderedQueryable<T> orderedDataCollection = OrderByFieldOrPropertyName(dataCollection, orderOperator, rule.FieldOrPropertyName);
// apply next rules recursivly
return OrderByRulesRecursivly(orderedDataCollection, rules.Skip(1).ToList());
}
/// <summary>
/// Static constructor. Initialize Reflection informations about Order operators
/// </summary>
static MyLINQExtensions()
{
// public static IOrderedQueryable<TSource> OrderBy<TSource, TKey>(this IQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector);
OrderByMethodInfo = typeof(Queryable)
.GetMethods()
.First(m => m.Name == "OrderBy" && m.GetParameters().Count() == 2);
// public static IOrderedQueryable<TSource> OrderByDescending<TSource, TKey>(this IQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector);
OrderByDescendingMethodInfo = typeof(Queryable)
.GetMethods()
.First(m => m.Name == "OrderByDescending" && m.GetParameters().Count() == 2);
// public static IOrderedQueryable<TSource> ThenBy<TSource, TKey>(this IOrderedQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector);
ThenByMethodInfo = typeof(Queryable)
.GetMethods()
.First(m => m.Name == "ThenBy" && m.GetParameters().Count() == 2);
// public static IOrderedQueryable<TSource> ThenByDescending<TSource, TKey>(this IOrderedQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector);
ThenByDescendingMethodInfo = typeof(Queryable)
.GetMethods()
.First(m => m.Name == "ThenByDescending" && m.GetParameters().Count() == 2);
}
static MethodInfo OrderByMethodInfo;
static MethodInfo OrderByDescendingMethodInfo;
static MethodInfo ThenByMethodInfo;
static MethodInfo ThenByDescendingMethodInfo;
}
To be compiled, the code requires that the following namespaces are declared in the header:
using System.Linq.Expressions;
using System.Reflection;
Now you can use OrderByRules on your context:
var summaryOrdered = summary.OrderByRules(new List<OrderRule> {
new OrderRule("Total", true),
new OrderRule("Preventable", false)
});
This will order the collection by Total (descending) and then by Preventable (ascending).