It was a legacy enterprise project and I was asked to improve its “advanced” filtering capabilities.

Before they had something like this:

But wanted something like this:

Let’s skip the UI part and focus on the data access layer. Like 99% of similar projects this one used a sql database (MS SQL but it is does not matter) and this project belonged to the part of the similar projects that does not struggle with Entity Framework but puts all the logic in stored procedures. The procedure which performed the “advanced” search looked like that:

CREATE PROCEDURE dbo.SomeContextUserFind
    (@ContextId int, @Filter nvarchar(max)) AS
BEGINDECLARE @sql nvarchar(max) = 
    N'SELECT U.UserId, U.FirstName, U.LastName
    FROM [User] U
    INNER JOIN [SomeContext] [C]
      ON ....
    WHERE [C].ContextId = @p1 AND ' + @Filter;EXEC sp_executesql 

    @sql,
    N'@p1 int',
    @p1=@ContextId
END

and the code that generated the filter string looked something like that:

string BuildFilter(IEnumerable<FilterItem> items)
{
    var builder = new StringBuilder();
    foreach (var item in items)
    {
        builder.Append(item.Field);
        bool isLike = false;
        switch (item.Operation)
        {
            case Operation.Equals:
                builder.Append(" = ");
                break;
            case Operation.Like:
                isLike = true;
                builder.Append(" LIKE ");
                break;
            //...
        }
        builder.Append('\'');
        if (isLike)
            builder.Append('%');
        builder.Append(Escape(item.Value));
        if (isLike)
            builder.Append('%');
        builder.Append('\'');
    }
    return builder.ToString();
}

Of course it is not the greatest code you have ever seen, but unfortunately legacy projects (or even not yet legacy) often are full of similar things. Anyway, it is what it is and it should be somehow improved.

The first thought that came to my mind is just adding more fields into the “FilterItem” and make the building logic more complex, but I quickly realized that it a road to nowhere — it would be extremely difficult to maintain such code, and I would never achieve the desired functionality.

At that point, I remembered the “abstract syntax tree”, which is obviously the best choice in this case, and now I am going to explain what it is and how it helps.

#programming #sql #databases #linq #developer