Dynamic Linq Query with multiple select value'

Question

I want to implement a filter on multiple columns, but I don't want to write for every column a new query. So I implemented a GetDistinctProperty function which looks like this :

public ActionResult GetDistinctProperty(string propertyName)
{
    var selector = CreateExpression<TDomain>(propertyName);
    var query = this.InventoryService.GetAll(Deal);
    var results = query.Select(selector).Distinct().ToList();
    return Json(results, JsonRequestBehavior.AllowGet);
}

private static Expression<Func<T, object>> CreateExpression<T>(string propertyName)
{
    // Specify the type we are accessing the member from
    var param = Expression.Parameter(typeof(T), "x");
    Expression body = param;

    // Loop through members in specified property name
    foreach (var member in propertyName.Split('.'))
    {
        // Access each individual property
        body = Expression.Property(body, member);
    }

    var conversion = Expression.Convert(body, typeof(object));
    // Create a lambda of this MemberExpression 
    return Expression.Lambda<Func<T, object>>(conversion, param);
}

Let's take as example that I have as propertyName SiteIdentifier.

The selector gives me as value

{x => Convert(x.SiteIdentifier)}

and when I want to see the results it gives me the following error :

Unable to cast the type 'System.String' to type 'System.Object'.
LINQ to Entities only supports casting EDM primitive or enumeration types.

When I try the select as follow :

var results = query.Select(x=>x.SiteIdentifier).Distinct().ToList();

it works.

Anyone any Idea?

Answer 1

The problem is that although IQueryable<T> interface is covariant, covariance is not supported for value types, so IQueryable<int> cannot be treated as IQueryable<object>. From the other side, EF does not like casting value type to object.

So in order to make it work, you need to resort to non generic IQueryable interface. Unfortunately almost all Queryable extension methods are build around IQueryable<T>, so you have to manually compose a corresponding calls.

For instance, in order to select property by name (path), you'll need something like this:

public static partial class QueryableExtensions()
{
    public static IQueryable SelectProperty(this IQueryable source, string path)
    {
        var parameter = Expression.Parameter(source.ElementType, "x");
        var property = path.Split(',')
            .Aggregate((Expression)parameter, Expression.PropertyOrField);
        var selector = Expression.Lambda(property, parameter);
        var selectCall = Expression.Call(
            typeof(Queryable), "Select", new[] { parameter.Type, property.Type },
            source.Expression, Expression.Quote(selector));
        return source.Provider.CreateQuery(selectCall);
    }
}

But then you'll need a Distinct method that works on IQueryable:

public static partial class QueryableExtensions()
{
    public static IQueryable Distinct(this IQueryable source)
    {
        var distinctCall = Expression.Call(
            typeof(Queryable), "Distinct", new[] { source.ElementType },
            source.Expression);
        return source.Provider.CreateQuery(distinctCall);
    }
}

Now you have all the necessary pieces to implement the method in question. But there is another important detail though. In order to be able to create List<object> you need to call Cast<object>. But if you use IQueryable.Cast extension method you'll get the same not supported exception from EF. So you need to call explicitly the IEnumerable.Cast instead:

public ActionResult GetDistinctProperty(string propertyName)
{
    var query = this.InventoryService.GetAll(Deal);
    var results = Enumerable.Cast<object>(
        query.SelectProperty(propertyName).Distinct()).ToList();
    return Json(results, JsonRequestBehavior.AllowGet);
}

Answer 2

There is no reason to pass the property name as string when using Linq in a statically typed language. Generics and delegates (Func) got introduced to make this kind of logic obsolete.

You can simply pass the expression instead of passing the property name:

public ActionResult GetDistinctProperty(Expression<Func<TDomain, TProp> selector)
{
    var query = this.InventoryService.GetAll(Deal);
    var results = query.Select(selector).Distinct().ToList();
    return Json(results, JsonRequestBehavior.AllowGet);
}

Usage:

GetDistinctProperty(x=> x.SiteIdentifier);