Entity Framework Core Dynamic Where Clause-CodePudding

I have been searching and I have not seen an example like this.

I want to query a Model table to retrieve all Models from German manufacturers.

My data structure is Model Table has a Navigation Property called "Manufacturer" and an FK called "IdManufacturer"

A Manufacturer Has a Navigation Property called "Origin" and an FK called IdOrigin.

I would like to have a query that includes a where clause something like this:

(m => m.Manufacturer.Origin.Name == "German");

I would like this to be dynamically created at run time. Not only the lookup value might change, but the next time even the fields may change to, for example: (m.Type.Name == "SUV");

Finally, there will not necessarily be a UI associated with this request, it may be generated in code.

Please don't get too hung up on the business of it, I am hoping this made up example will be simple to understand.

Any suggestions on how to handle this would be greatly appreciated. Any general thoughts on performance would be created.

CodePudding user response：

Try the following simple extension method. Note that this realization will work only with sting properties.

Usage:

query = query.Where("Manufacturer.Origin.Name", "German");

Realization:

public static class QueryableExtensions
{
    public static IQueryable<T> Where(this IQueryable<T> query, string propPath, string value)
    {
        var param = Expression.Parameter(typeof(T), "e");

        // e.Prop1.Prop2 == value
        var body = Expression.Equal(MakePropPath(param, propPath), Expression.Constant(value));
        // e => e.Prop1.Prop2 == value
        var predicateLambda = Expression.Lambda<Func<T, bool>>(body, param);

        return query.Where(predicateLambda);
    }

    static Expression MakePropPath(Expression objExpression, string path)
    {
        return path.Split('.').Aggregate(objExpression, Expression.PropertyOrField);
    }
}

CodePudding user response：

My solution:
I have a model RouteList, in this model I created Expression:

public static Expression<Func<RouteList, bool>> FilterByDateOrShiftCode(RoutesFilterViewModel filter)
{
    Expression<Func<RouteList, bool>> result = rl => true;

    if (string.IsNullOrEmpty(filter.ShiftCode))
    {
        result = rl => rl.RouteListDate.Date >= filter.From.Date && rl.RouteListDate.Date <= filter.To.Date && rl.User.Id == filter.User.Id;
    }
    else
    {
        result = rl => rl.ShiftCode == filter.ShiftCode && rl.User.Id == filter.User.Id;
    }

Place where I use it:

model.RouteLists = await _context.RouteLists
    .Include(rl => rl.Status)
    .Include(rl => rl.User)
    .Include(rl => rl.RouteListRows).ThenInclude(rlr => rlr.Address)
    .Where(RouteList.FilterByDateOrShiftCode(filter))
    .ToListAsync();

Speed of executing grow in several times.