Making a Generic MVC API Query

Question

I'm building an MVC API in .NET Core. I have several really big tables (3rd party system) and I'm trying to allow the name of any column to be passed as a URL parameter with a value to use in a Where clause. This uses the Entity Framework and .NET Core.

The idea is to grab the column matching the parameter name and use it in the Where clause with the parameter's value. I want the lambda in the Where query to end up looking like:

//GET: api/DWRWorkItems?Parameter=Value
...
dWRWorkItem = dWRWorkItem.Where(m => 
   m.Parameter == Value
);

This is my attempt at a clean solution, the route code from the controller. The Model and Views are unchanged from the default created by Entity Framework.

    // GET: api/DWRWorkItems
    [HttpGet]
    public IEnumerable<DWRWorkItem> GetTDwrWrkItm()
    {
        IQueryable<DWRWorkItem> dWRWorkItem = _context.TDwrWrkItm.Where(m => 1 == 1);
        var q = HttpContext.Request.Query;
        foreach (string p in q.Keys)
        {

            dWRWorkItem = dWRWorkItem.Where(m => 
                m.GetType().GetProperty(p).GetValue(m, null) == q[p]
            );

        }

        return dWRWorkItem.ToList();
    }

Intellisense (VS 2017) doesn't expect an errors, but when I run this, I get:

The operands for operator 'Equal' do not match the parameters of method 'op_Equality'.

Stepping through the code, it appears that the reflection in the lambda isn't working as expected. Here is what dWRWorkItem.Expression.Arguments[1] looks like:

{m => (GetProperty(m.GetType(), value(EBOOKS.Controllers.DWRWorkItemsController+<>c__DisplayClass0#1).p).GetValue(m, null) == value(EBOOKS.Controllers.DWRWorkItemsController+<>c__DisplayClass0#1).CS$<>8__locals1.q.get_Item(value(EBOOKS.Controllers.DWRWorkItemsController+<>c__DisplayClass0#1).p))}

While an example where the parameter is not dynamic looks like this:

{m => (m.ContId == value(EBOOKS.Controllers.DWRWorkItemsController+<>c__DisplayClass3_0).id)}

Answer 1

In general query providers other than LINQ to Objects do not like reflection calls inside expression tree, so it's better to compose dynamically expression using the System.Linq.Expressions.Expression class methods.

Here is a custom extension method suitable for your case:

public static partial class QueryableExtensions
{
    public static IQueryable<T> WhereEquals<T>(this IQueryable<T> source, string memberPath, string value)
    {
        var parameter = Expression.Parameter(typeof(T), "e");
        var left = memberPath.Split('.').Aggregate((Expression)parameter, Expression.PropertyOrField);
        var right = Expression.Constant(ToType(value, left.Type), left.Type);
        var predicate = Expression.Lambda<Func<T, bool>>(Expression.Equal(left, right), parameter);
        return source.Where(predicate);
    }

    private static object ToType(string value, Type type)
    {
        if (type == typeof(string)) return value;
        if (string.IsNullOrEmpty(value)) return null;
        return Convert.ChangeType(value, Nullable.GetUnderlyingType(type) ?? type);
    }
}

Additionally to using Expression.Equal (which is the equivalent of == operator) it has to deal with string to actual value type conversion, which btw is the cause of your original exception.

The usage is like this:

var dWRWorkItem = _context.TDwrWrkItm.AsQueryable();
var q = HttpContext.Request.Query;
foreach (string p in q.Keys)
    dWRWorkItem = dWRWorkItem.WhereEquals(p, q[p]);    
return dWRWorkItem.ToList();