Making a generic MVC API request

I am creating MVC API in .NET Core. I have some really large tables (3rd party system) and I am trying to allow any column name to be passed as a URL parameter with the value used in the Where clause. This uses Entity Framework and .NET Core.

The idea is to grab the column corresponding to the parameter name and use it in the Where clause with the parameter value. I want the lambda in the Where query to look like this:

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

      

This is my attempt at a clean solution, route code from controller. Model and views are not changed from the default generated 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 errors, but when I run this I get:

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

After going through the code, it seems that the reflection in the lambda is not working as expected. This 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__Display # 1) .cs $ <> 8__locals1.q.get_Item (value (EBOOKS.Controllers.DWRWorkItemsController + <> c__DisplayClass0 # 1) .p))}

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

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

+3


source to share


1 answer


In general, query providers other than LINQ to Objects do not like calls to reflection inside an expression tree, so it is better to link a dynamic expression using 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);
    }
}

      



In addition to using Expression.Equal

(which is the equivalent of an operator ==

) it has to deal with string

before the actual value type conversion, which is the reason for your original exception by the way.

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();

      

+3


source







All Articles