Tiramonium Tiramonium - 3 years ago 174
C# Question

Ordering by ICollection property using its name as string with Dynamic Linq Library

I've searched pretty much every question, blog or documentation I could find to help me get to this question's answer. A few actually helped me get closer to it, but I still haven't solved it yet, since most were either outdated or not really on point with what I needed. Don't bother telling me about Expressions, since if I were to take the longer route, I wouldn't be using this library.

My project goal is to supply filtering and ordering by using generic methods, by using only the tables/columns name strings and the values used for filtering. Long story short, I have two tables, with a relation of 1 to n, connected basically like this and I want to order by the customer's name:

public partial class Transactions{
public ICollection<Customer> customer { get; set; };
}

public partial class Customer{
public string name { get; set; };
}


So far I have pretty much I should need to achieve what I want, except figuring out how to structure the OrderBy string properly, in a way I'd use a single ICollection result.

What I have so far is pretty much like this (sorry for being too verbose on my documentation):

using System.Linq.Dynamic;
using System.Reflection;

namespace com.betha.common
{
public class Filter
{
/// <summary>
/// Dictionary of codenames and column names populated by a different, specific class (properties.Add("customer", "tb_customer.name"))
/// </summary>
public Dictionary<string, string> properties;

public Filter(Dictionary<string, string> properties)
{
this.properties = properties;
}

/// <summary>
/// Generic method designed to filter and order using just lists of column names and values
/// </summary>
/// <typeparam name="T">Type of the first IQueryable</typeparam>
/// <typeparam name="T2">Type of the second IQueryable</typeparam>
/// <param name="query">IQueryable containing the results from the parent table (context.table1).AsQueryable();</param>
/// <param name="query2">IQueryable containing a single result from a descendant table (context.table2.Select(t2 => t2.field).FirstOrDefault()).AsQueryable();</param>
/// <param name="prop">Property codename that if used, matches a properties codename Key</param>
/// <param name="descend">Condition for ascending or descending results</param>
/// <returns>Ordered and/or filtered IQueryable</returns>
public IQueryable<T> FilterandOrder<T, T2>(IQueryable<T> query, IQueryable<T2> query2, string prop = null, string descend = null)
{
if (!String.IsNullOrEmpty(prop))
{
foreach (KeyValuePair<string, string> item in properties)
{
if (prop == item.Key)
{
prop = item.Value;
}
}
T2 subprop = query2.FirstOrDefault();

if (prop.Contains("."))
{
switch (prop.Split('.'))
{
default:
PropertyInfo property = subprop.GetType().GetProperty(prop.Split('.')[1]);
ParameterInfo[] index = property.GetIndexParameters();
object value = subprop.GetType().GetProperty(prop.Split('.')[1]).GetValue(subprop, index);

//This is the main issue, I have pretty much everything I should need, but I can't imagine how to write this OrderBy string properly.
//If I were to do it without the library: table.OrderBy(t => t.table2.Select(t2 => t2.field).FirstOrDefault());
//Without ordering by an ICollection property, like in the else below, string should look like: "field".
//Or in case of a 1 to 1 relationship, "table2.field".
query = DynamicQueryable.OrderBy(query, prop + (descend == "dec" ? " descending" : ""), value);
break;
}
}
else
{
query = DynamicQueryable.OrderBy(query, prop + (descend == "dec" ? " descending" : ""));
}
}

return query;
}
}
}

Answer Source

You can replace the t => t.table2.Select(t2 => t2.field).FirstOrDefault() with a supported function, Min():

OrderBy("table2.Min(field)");
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download