LINQ: Dynamic Creation of Query Filters

Sooner or later, each developer has to create data tables with the possibility to sort by columns. I am not an exception. In our project, there are similar tables almost on every page. So, 90 % of the content is output on them. Of course, search and sorting by tables work without reloading a page.

Needless to say, that to extend the semantics of API controller methods to infinity would be absolutely impractical, so we needed a universal solution for all our tables. Reflection and expression trees helped to find it.

We decided that it is much more convenient to create a filter for retrieving data on a client machine and pass it as a JSON file to a server than to add many conditions to the query.

How it looks like

Assume we have a model of a user and cars they own:

public class User 
{
  public int Id { get; set; }
  public string Name { get; set; }
  public int Age { get; set; }
  public IEnumerable<Car> Cars { get; set; }
}

public class Car
{
  public int CarId { get; set; }
  public string Model { get; set; } 
  public int MaxSpeed { get; set; }
}

We are going to retrieve users whose name starts with A and a car speed is more than 300 km per hour or whose ID is higher than 0. We will sort them by name in the descending order and by ID in the ascending order. To do this, we create the following object:

{
    "Where": {
        "OperatorType": "Or",
        "Operands": [
            {
                "OperatorType": "And",
                "Operands": [
                  {
                    "Field": "Name",
                    "FilterType": "StartsWith",
                    "Value": "A"
                  },
                  {
                    "Field": "Cars.MaxSpeed",
                    "FilterType": "GreaterThan",
                    "Value": 300
                  }
                ]
            },
            {
                "Field": "Id",
                "FilterType": "GreaterThan",
                "Value": 0
            }
        ]
    },
    "OrderBy": [
        {
            "Field": "Name",
        },
        {
            "Field": "Flag",
            "Order": "Desc"
        }
    ],
}

As you can see, we get the object with an unlimited nesting of operands, which allows you to create large filters.

Now, we need to apply this filter to the data selection.

FilterContainer filter = ...; // object deserialization
IQueryable<User> query = dataAccess.MyUsers;

query = query.Request(filter);
// or
////query = query.Where(filter.Where).OrderBy(filter.OrderBy);

That’s all.

Many people know that ORM of the Entity Framework or Linq2SQL type use expression trees for applying structured queries to data sources. The query provider can go through the data structure for the expression tree and convert it into a query language.

Through reflection, a filter collector recursively builds a query tree from the corresponding entity components.

All the filtering methods and types are explored on GitHub.