LINQ: Dynamic Query Filtering

Surely, sooner or later each developer had to create data tables with the ability to sort by columns, etc. I am not an exception. In our project, there are similar tables on almost every page; we can say that 90% of the content is displayed through them. Searching and sorting by tables naturally works without reloading the page.

Of course, extending the semantics of the controllers' API methods to an infinite sheet would be absolutely stupid and impractical, so we needed a universal solution for all our tables. Reflection and expression trees helped find it.

We decided that creating a filter for fetching data on the client and transferring it as a JSON object to the server is much more convenient than hanging a bunch of conditions in the query factory.

How it all looks

Suppose we have models of the user and cars that he owns:

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; } } 

Let's get users whose name begins with A and the car can reach speeds of more than 300 km / h or with Id greater than zero, and then sort them by name in descending order, then by Id in ascending order. To do this, 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" } ], } 

You can see that the object is obtained, with unlimited nesting of the operands, which allows you to build fairly large filters.

Now it remains to apply this filter to the data sample. How to transfer to the server and deserialize json object I will not paint, not small ones.

 FilterContainer filter = ...; //   IQueryable<User> query = dataAccess.MyUsers; query = query.Request(filter); //  ////query = query.Where(filter.Where).OrderBy(filter.OrderBy); 

That's actually the whole sample.

Many people know, and who do not know, so I will tell you that the Entity Framework or Linq2SQL ORMs use expression trees to represent 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, the filter collector recursively builds a query tree from the corresponding members of the entity.

All methods and types of filtering are discussed in the githaba project.

PS In principle, I did not count on the title of the pioneer in this task, something like this has already been done in one form or another. In any case, it was a great experience.