Cosmin LefermanStory time
I recently had a requirement to display data from different SQL tables in a grid like fashion. This grid needed to have common functionality like filtering, sorting, grouping, column visibility. As it usually goes with enterprise applications, we can’t just use client-side filtering. These tables would quickly be populated with hundreds of thousands of entries thanks to our trusty import feature and are expected to hold millions of rows. Of course, we had to implement server-side actions for each aforementioned functionality.
So, we had 8 tables to start with and more to come eventually. Each table with a number of columns ranging from 20 to a 170 (WHAT?! WHO NEEDS THAT MANY COLUMNS?!) Well, it turns out no one. No one looks at all 170 columns at once, but different users can see a diverse set of those columns.
Anyway, we had entity framework to do the heavy lifting for us. But how would you handle filtering for all 170 columns? Surely, I could write some LINQ queries myself for some of those columns and then coerce Copilot into generating everything else for me. But who’ll maintain this junk? What happens if there’s an issue with a field? DO I HAVE TO WRITE THIS FOR EACH TABLE AND THEY HAVE ONLY 5 FIELDS IN COMMON?! Surely there must be a better way!
Well, of course there is. Just create a SQL Statement builder and call it a day. However, we are .NET developers, we like type safety, not string manipulation.
I had this safety net of raw SQL queries to fall back on, and so I started to look for a better, functional way… and I found it! C# Expression Trees.
What are Expression Trees?
From Microsoft Docs:
Expression trees represent code in a tree-like data structure, where each node is an expression, for example, a method call or a binary operation such as x < y.
You can read more about it here.
Building Expression Trees in C
C# equivalent of the above expression tree would be:
using System.Linq.Expressions;
//x
var paramX = Expression.Parameter(typeof(int), "x");
//y
var paramY = Expression.Parameter(typeof(int), "y");
// x < y
var xLessThanY = Expression.LessThan(paramX, paramY);
//(x,y) => x < y
var isXLessThanY = Expression.Lambda<Func<int, int, bool>>(xLessThanY, paramX, paramY).Compile();
Console.WriteLine(isXLessThanY(2, 3));
Wow, that’s cool! But not particularly useful, is it? Why go through all the trouble when you can just return x < y. Well, let’s complicate the example a bit. Projects are usually more complex than that.
Advanced Scenarios
What if we could apply this LessThan logic dynamically to any property of type int, to any object. Consider the following:
using System.Linq.Expressions;
var list = new List<Student> {
new("Monika", 18),
new("Tim", 20),
new("Andrea", 23)
};
IQueryable<Student> query = list.AsQueryable();
var students = query
.LessThan("age", 21)
.ToList();
Console.WriteLine(students.Count); //2
Unlike the Where function, our handily crafted LessThan method accepts a string as a field name and a value of type int which means we can apply this operation on any property dynamically! Why would we need that since we know the type Student? We can just do query.Where(t => t.Age < 21) and get the paycheck. And you’d be right. But imagine you don’t know on what object you apply this operation on. Imagine Student is a generic type T so all your properties are unknown. See the implementation details of LessThan below:
using System.Linq.Expressions;
public static class IQueryableExtensions
{
public static IQueryable<T> LessThan<T>(this IQueryable<T> query, string fieldName, int value)
{
var paramX = Expression.Parameter(typeof(T), "x");
//x.[fieldName]
var selector = Expression.PropertyOrField(paramX, fieldName);
// value
var valueConstant = Expression.Constant(value);
//x.[fieldName] < value
var lessThan = Expression.LessThan(selector, valueConstant);
// x => x.[fieldName] < value
var lambdaExpr = Expression.Lambda(lessThan, paramX);
//query.Where(x => x.[fieldName] < value)
var whereExpression = Expression.Call(typeof(Queryable), "Where", new Type[] { query.ElementType }, query.Expression, lambdaExpr);
return query.Provider.CreateQuery<T>(whereExpression);
}
}
There you have it. Now you can apply this operation on any object! LessThan is not that useful in a real case scenario but not to worry, we can build upon it and take it to the next level.
Armed with this newly found knowledge, I was ready to tackle my initial problem. As you may remember, I had to apply filtering/sorting/grouping/column visibility operations to ~8 SQL tables each table having a number of columns anywhere between 20 and 170 and I didn’t want to concatenate strings to build a dynamic SQL statement. So I created a tiny library available as a NuGet package to easily add server-side filtering/sorting/grouping operations to any entity.
Let’s say we have a list of students. On this list we want to be able to apply filters on every property, order by every property, select a set of properties from the student and paginate it. Below you can see an example of how you could use the library to achieve this:
using ServerSide.GridUtilities.Extensions;
public IReadOnlyList<Student> GetStudents(GridRequest gridRequest)
{
return dbContext.Students
.FilterBy(gridRequest.Filtering)
.OrderBy(gridRequest.Sorting)
.Select(gridRequest.Columns)
.Skip(gridRequest.Pagination.StartRow)
.Take(gridRequest.Pagination.EndRow - gridRequest.Pagination.StartRow)
.ToList();
}
That’s it! You just need to supply the Grid Request. Consider the following JSON which deserializes into GridRequest:
{
"columns": ["Name", "EnrollmentDate", "Age"],
"pagination": {
"startRow": 0,
"endRow": 50
},
"sorting": [
{
"colName": "Age",
"sort": "desc"
}
],
"filtering": [
{
"conditions": [
{
"filterMethod": "contains",
"values": ["Student 1"]
}
],
"fieldName": "Name",
"filterType": "text"
}
],
"grouping": {
"rowGroupCols": [],
"groupKeys": []
}
}
This JSON instructs our expression tree to:
- Select the 3 columns: Name, EnrollmentDate, Age
- Get 50 students in a page
- Sort descending by Age
- Get only students which have “Student 1” in their name
Selecting a subset of columns and applying sort is easy. The filter clause gets more complicated. At the time of writing the library supports filtering by:
- Text (Contains, NotContains, Equals, NotEqual, StartsWith, EndsWith, Blank, NotBlank, In, NotIn)
- Number (Equals, NotEqual, GreaterThan, LessThan, GreaterThanOrEqual, LessThanOrEqual, InRange, Blank, NotBlank)
- Date (Equals, NotEqual, GreaterThan, LessThan, Blank, NotBlank)
You can add all the fields of an object to the filter model and each field can have up to two conditions. Between different fields we always put an AND operation. Between the 2 conditions on the same field, we can either put an AND or an OR operation.
With the JSON below we instruct our expression to return all students whose names contain “Student 1” but not “Student 12” and which have a grade greater than or equal to 8.
{
"filtering": [
{
"operator": "and",
"conditions": [
{
"filterMethod": "contains",
"values": ["Student 1"]
},
{
"filterMethod": "notContains",
"values": ["Student 12"]
}
],
"fieldName": "Name",
"filterType": "text"
},
{
"conditions": [
{
"filterMethod": "greaterThanOrEqual",
"values": ["8"]
}
],
"fieldName": "Grade",
"filterType": "number"
}
],
}
All those operations are extensions methods on the IQueryable interface which is great news! If you’ve ever used Entity Framework to get data from a SQL database you know EF also uses IQueryable so you can easily apply those operations just by importing the extensions namespace.
Not only that, The Mongo Driver for C# also makes use of IQueryable. You’ve guessed it. These operations are also supported when applied on a mongo database!
Conclusion
In this article, we looked at what expression trees are and how we can use their flexibility to build a fully dynamic solution for usual server side operations like filtering, sorting, grouping and column selection. They can be a bit hard to read in the beginning and you might have some trouble wrapping your head around them, but when you want to abstract away generic logic, expression trees can be very useful.
We’ve only looked at one use case of the expression trees, but there are many more. Entity Framework uses expression trees to translate the C# LINQ queries into SQL statements. Moq, a powerful mocking library for C# also uses them under the hood.
