Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Dynamic Query Builder in C# / .NET Entity Framework

Let's say we are using EF6, and db is the EF DbContext. Let's say the user could enter five possible queries:

  1. Get cars where age EQUALS 10.
  2. Get cars where age IS LESS THAN 10.
  3. Get cars where age IS MORE THAN 10.
  4. Get cars where age is NOT EQUAL to 10.
  5. Get carss where age is ANY.
    enum Operator
    {
        EQUAL,
        NOT_EQUAL,    
        LESS_THAN, 
        MORE_THAN,
        ANY
    };

    var operator = //some user selected operator, out of the ones available in the enum above
    var carAge = //int selected by user to specify desired age
    List cars; 

    switch(operator)
    {
        case Operator.EQUAL 
        {
            cars = db.Cars.Where(c=> c.Age == carAge).ToList(); 
        }
        case Operator.NOT_EQUAL 
        {
            cars = db.Cars.Where(c=> c.Age != carAge).ToList(); 
        }
        case Operator.LESS_THAN 
        {
            cars = db.Cars.Where(c=> c.Age  c.Age > carAge).ToList(); 
        }
        case Operator.ANY 
        {
            cars = db.Cars.ToList(); 
        }
    }

How do I do this more efficiently? The switch operator seems silly. It looks like this article is answering the question, but I don't fully understand.

We do have an answer on dynamically building multiple queries, but that's a different part of what I want (would apply if question was "User can search for both car age and mileage, or neither).

Edit: To clarify in response to a comment, my "goal" is any standard search, using the car example, a user would have a number of fields where they can select operators. They can use any, all, or no fields, e.g.

Mileage: Operator, Value
Year: Operator, Value
Brand: (is/is not, for example), Car brands

like image 281
VSO Avatar asked Mar 12 '23 05:03

VSO


1 Answers

    Expression<Func<T, bool>> Build<T, TProperty>(Expression<Func<T, TProperty>> property, Operator op, TProperty value)
    {
        if (op == Operator.ANY)
        {
            return e => true;
        }

        var left = property.Body;   //you need to check if it's a valid property visit
        var right = Expression.Constant(value);
        BinaryExpression body;

        switch (op)
        {
            case Operator.EQUAL:
                body = Expression.Equal(left, right);
                break;
            case Operator.NOT_EQUAL:
                body = Expression.NotEqual(left, right);
                break;
            case Operator.LESS_THAN:
                //you'd better check if < operator is available for the type
                body = Expression.LessThan(left, right);
                break;
            case Operator.MORE_THAN:
                body = Expression.GreaterThan(left, right);
                break;
            default: throw new NotSupportedException();
        }

        return Expression.Lambda<Func<T, bool>>(body, property.Parameters[0]);
    }

Usage var bmw = Build<Car, string>(c => c.Brand, Operator.EQUAL, "BMW");

like image 108
Cheng Chen Avatar answered Mar 24 '23 06:03

Cheng Chen