Further optimize and abstract out filter query gen

OpenShock · hhvrc · Feb 11, 2025 · Feb 10, 2025 · Feb 10, 2025 · Feb 10, 2025
commit 1fbbe4ed574dcb392ed35e34c50e6ea6546bc300
diff --git a/API/Controller/Admin/GetUsers.cs b/API/Controller/Admin/GetUsers.cs
@@ -8,6 +8,7 @@
 using OpenShock.Common.Utils;
 using Z.EntityFramework.Plus;
 using OpenShock.Common.OpenShockDb;
+using OpenShock.Common.Query;
 
 namespace OpenShock.API.Controller.Admin;
 
@@ -47,7 +48,11 @@ public async Task<IActionResult> GetUsers(
                 query = query.OrderBy(u => u.CreatedAt);
             }
         }
-        catch (ExpressionBuilder.ExpressionException e)
+        catch (QueryStringTokenizerException e)
+        {
+            return Problem(ExpressionError.QueryStringInvalidError(e.Message));
+        }
+        catch (DBExpressionBuilderException e)
         {
             return Problem(ExpressionError.ExpressionExceptionError(e.Message));
         }

diff --git a/Common/Errors/ExpressionError.cs b/Common/Errors/ExpressionError.cs
@@ -5,5 +5,6 @@ namespace OpenShock.Common.Errors;
 
 public static class ExpressionError
 {
+    public static OpenShockProblem QueryStringInvalidError(string details) => new OpenShockProblem("ExpressionError", "Query string is invalid", HttpStatusCode.BadRequest, details);
     public static OpenShockProblem ExpressionExceptionError(string details) => new OpenShockProblem("ExpressionError", "An error occured while processing the expression", HttpStatusCode.BadRequest, details);
 }
diff --git a/Common/Extensions/IQueryableExtensions.cs b/Common/Extensions/IQueryableExtensions.cs
@@ -1,20 +1,16 @@
 using System.Linq.Expressions;
 using OpenShock.Common.Utils;
+using OpenShock.Common.Query;
 
 namespace OpenShock.Common.Extensions;
 
 public static class IQueryableExtensions
 {
     public static IQueryable<T> ApplyFilter<T>(this IQueryable<T> query, string filterQuery) where T : class
     {
-        var filter = ExpressionBuilder.GetFilterExpression<T>(filterQuery);
-
-        if (filter != null)
-        {
-            query = query.Where(filter);
-        }
+        if (string.IsNullOrWhiteSpace(filterQuery)) return query;
 
-        return query;
+        return query.Where(DBExpressionBuilder.GetFilterExpression<T>(filterQuery));
     }
 
     public static IOrderedQueryable<T> ApplyOrderBy<T>(this IQueryable<T> query, string orderbyQuery) where T : class
@@ -28,9 +24,7 @@ public static IOrderedQueryable<T> ApplyOrderBy<T>(this IQueryable<T> query, str
 
         var entityType = typeof(T);
 
-        var memberInfo = ExpressionBuilder.GetPropertyOrField(entityType, propOrFieldName);
-        if (memberInfo == null)
-            throw new ExpressionBuilder.ExpressionException($"'{propOrFieldName}' is not a valid property");
+        var (memberInfo, memberType) = DBExpressionBuilderUtils.GetPropertyOrField(entityType, propOrFieldName);
 
         var parameterExpr = Expression.Parameter(entityType, "x");
         var memberExpr = Expression.MakeMemberAccess(parameterExpr, memberInfo);
@@ -42,10 +36,6 @@ public static IOrderedQueryable<T> ApplyOrderBy<T>(this IQueryable<T> query, str
             "desc" => "OrderByDescending",
             _ => throw new ArgumentException(),
         };
-
-        var memberType = ExpressionBuilder.GetPropertyOrFieldType(memberInfo);
-        if (memberType == null)
-            throw new ExpressionBuilder.ExpressionException("Unknown error occured");
 
         // Get the appropriate Queryable method (OrderBy or OrderByDescending)
         var method = typeof(Queryable).GetMethods()

diff --git a/Common/Query/DBExpressionBuilder.cs b/Common/Query/DBExpressionBuilder.cs
@@ -0,0 +1,112 @@
+using System.Linq.Expressions;
+using System.Text.RegularExpressions;
+
+namespace OpenShock.Common.Query;
+
+public sealed class DBExpressionBuilderException : Exception
+{
+    public DBExpressionBuilderException(string message) : base(message) { }
+}
+
+public static partial class DBExpressionBuilder
+{
+    [GeneratedRegex(@"^[A-Za-z][A-Za-z0-9]*$")]
+    private static partial Regex ValidMemberNameRegex();
+
+    private static Expression CreateMemberCompareExpression<T>(Type entityType, ParameterExpression parameterExpr, string propOrFieldName, string operation, string value) where T : class
+    {
+        var (memberInfo, memberType) = DBExpressionBuilderUtils.GetPropertyOrField(entityType, propOrFieldName);
+
+        var memberExpr = Expression.MakeMemberAccess(parameterExpr, memberInfo);
+
+        Expression? resultExpr = operation switch
+        {
+            "like" => DBExpressionBuilderUtils.BuildEfFunctionsCollatedILikeExpression(memberType, memberExpr, value),
+            "==" or "eq" => DBExpressionBuilderUtils.BuildEqualExpression(memberType, memberExpr, value),
+            "!=" or "neq" => DBExpressionBuilderUtils.BuildNotEqualExpression(memberType, memberExpr, value),
+            "<" or "lt" => DBExpressionBuilderUtils.BuildLessThanExpression(memberType, memberExpr, value),
+            ">" or "gt" => DBExpressionBuilderUtils.BuildGreaterThanExpression(memberType, memberExpr, value),
+            "<=" or "lte" => DBExpressionBuilderUtils.BuildLessThanOrEqualExpression(memberType, memberExpr, value),
+            ">=" or "gte" => DBExpressionBuilderUtils.BuildGreaterThanOrEqualExpression(memberType, memberExpr, value),
+            _ => throw new DBExpressionBuilderException($"'{operation}' is not a supported operation type.")
+        };
+
+        return resultExpr ?? throw new DBExpressionBuilderException($"Operation {operation} is not supported for {memberType}");
+    }
+
+
+    private sealed record ParsedFilter(string MemberName, string Operation, string Value);
+    private enum ExpectedToken
+    {
+        Member,
+        Operation,
+        Value,
+        AndOrEnd
+    }
+    private static IEnumerable<ParsedFilter> ParseFilters(string query)
+    {
+        var member = string.Empty;
+        var operation = string.Empty;
+        var expectedToken = ExpectedToken.Member;
+        foreach (var word in QueryStringTokenizer.ParseQueryTokens(query))
+        {
+            switch (expectedToken)
+            {
+                case ExpectedToken.Member:
+                    member = word;
+                    expectedToken = ExpectedToken.Operation;
+                    break;
+                case ExpectedToken.Operation:
+                    operation = word;
+                    expectedToken = ExpectedToken.Value;
+                    break;
+                case ExpectedToken.Value:
+                    if (!ValidMemberNameRegex().IsMatch(member))
+                        throw new DBExpressionBuilderException("Invalid filter string!");
+
+                    if (string.IsNullOrEmpty(operation))
+                        throw new DBExpressionBuilderException("Invalid filter string!");
+
+                    yield return new ParsedFilter(member, operation, word);
+
+                    member = string.Empty;
+                    operation = string.Empty;
+                    expectedToken = ExpectedToken.AndOrEnd;
+                    break;
+                case ExpectedToken.AndOrEnd:
+                    if (word != "and") throw new DBExpressionBuilderException("Only and is supported atm!");
+                    expectedToken = ExpectedToken.Member;
+                    break;
+                default:
+                    throw new DBExpressionBuilderException("Unexpected state!");
+            }
+        }
+
+        if (expectedToken != ExpectedToken.AndOrEnd)
+            throw new DBExpressionBuilderException("Unexpected end of query");
+    }
+
+    public static Expression<Func<T, bool>> GetFilterExpression<T>(string filterQuery) where T : class
+    {
+        Expression? completeExpr = null;
+
+        var entityType = typeof(T);
+        var parameterExpr = Expression.Parameter(entityType, "x");
+
+        foreach (var filter in ParseFilters(filterQuery))
+        {
+            var memberExpr = CreateMemberCompareExpression<T>(entityType, parameterExpr, filter.MemberName, filter.Operation, filter.Value);
+
+            if (completeExpr == null)
+            {
+                completeExpr = memberExpr;
+            }
+            else
+            {
+                completeExpr = Expression.And(completeExpr, memberExpr);
+            }
+        }
+
+        return Expression.Lambda<Func<T, bool>>(completeExpr ?? Expression.Constant(true), parameterExpr);
+    }
+}
diff --git a/Common/Query/DBExpressionBuilderUtils.cs b/Common/Query/DBExpressionBuilderUtils.cs
@@ -0,0 +1,103 @@
+using Microsoft.EntityFrameworkCore;
+using System.Linq.Expressions;
+using System.Reflection;
+using System.Runtime.Serialization;
+
+namespace OpenShock.Common.Query;
+
+public static class DBExpressionBuilderUtils
+{
+    private static readonly MethodInfo EfFunctionsCollateMethodInfo = typeof(RelationalDbFunctionsExtensions).GetMethod("Collate")?.MakeGenericMethod(typeof(string)) ?? throw new MissingMethodException("EF.Functions", "Collate(string,string)");
+    private static readonly MethodInfo EfFunctionsILikeMethodInfo = typeof(NpgsqlDbFunctionsExtensions).GetMethod("ILike", [typeof(DbFunctions), typeof(string), typeof(string)]) ?? throw new MissingMethodException("EF.Functions", "ILike(string,string)");
+    private static readonly MethodInfo StringEqualsMethodInfo = typeof(string).GetMethod("Equals", [typeof(string)]) ?? throw new MissingMethodException("string", "Equals(string,StringComparison)");
+    private static readonly MethodInfo StringStartsWithMethodInfo = typeof(string).GetMethod("StartsWith", [typeof(string)]) ?? throw new MissingMethodException("string", "StartsWith(string)");
+    private static readonly MethodInfo StringEndsWithMethodInfo = typeof(string).GetMethod("EndsWith", [typeof(string)]) ?? throw new MissingMethodException("string", "EndsWith(string)");
+    private static readonly MethodInfo StringContainsMethodInfo = typeof(string).GetMethod("Contains", [typeof(string)]) ?? throw new MissingMethodException("string","Contains(string)");
+
+    /// <summary>
+    /// To not let whoever's requesting to explore hidden data structures, we return same exception for all errors here
+    /// </summary>
+    /// <param name="type"></param>
+    /// <param name="propOrFieldName"></param>
+    /// <returns></returns>
+    /// <exception cref="MissingMemberException"></exception>
+    public static (MemberInfo, Type) GetPropertyOrField(Type type, string propOrFieldName)
+    {
+        var memberInfo = type.GetMember(propOrFieldName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty | BindingFlags.GetField | BindingFlags.IgnoreCase).SingleOrDefault();
+        if (memberInfo == null)
+            throw new DBExpressionBuilderException($"'{propOrFieldName}' is not a valid property of type {type.Name}");
+
+        var isIgnored = memberInfo.GetCustomAttributes(typeof(IgnoreDataMemberAttribute), true).Any();
+        if (isIgnored)
+            throw new DBExpressionBuilderException($"'{propOrFieldName}' is not a valid property of type {type.Name}");
+
+        var memberType = memberInfo switch
+        {
+            PropertyInfo prop => prop.PropertyType,
+            FieldInfo field => field.FieldType,
+            _ => throw new DBExpressionBuilderException($"'{propOrFieldName}' is not a valid property of type {type.Name}")
+        };
+
+        return (memberInfo, memberType);
+    }
+
+    private static ConstantExpression GetConstant(Type type, string value)
+    {
+        /* Currently this causes a really weird bug which persists across subsequent requests
+        if (type.IsEnum)
+        {
+            var enumValue = Enum.Parse(type, value, ignoreCase: true);
+            return Expression.Constant(enumValue, type);
+        }
+        */
+
+        return Expression.Constant(value, type);
+    }
+
+    public static MethodCallExpression? BuildEfFunctionsCollatedILikeExpression(Type memberType, Expression memberExpr, string value)
+    {
+        if (memberType != typeof(string)) return null;
+
+        var valueConstant = Expression.Constant(value, typeof(string));
+        var defaultStrConstant = Expression.Constant("default", typeof(string));
+        var efFunctionsConstant = Expression.Constant(EF.Functions, typeof(DbFunctions));
+
+        var collated = Expression.Call(null, EfFunctionsCollateMethodInfo, efFunctionsConstant, memberExpr, defaultStrConstant);
+
+        return Expression.Call(null, EfFunctionsILikeMethodInfo, efFunctionsConstant, collated, valueConstant);
+    }
+
+    public static BinaryExpression BuildEqualExpression(Type memberType, Expression memberExpr, string value)
+    {
+        return Expression.Equal(memberExpr, GetConstant(memberType, value));
+    }
+
+    public static BinaryExpression BuildNotEqualExpression(Type memberType, Expression memberExpr, string value)
+    {
+        return Expression.NotEqual(memberExpr, GetConstant(memberType, value));
+    }
+
+    public static BinaryExpression? BuildLessThanExpression(Type memberType, Expression memberExpr, string value)
+    {
+        if (memberType is { IsPrimitive: false, IsEnum: false }) return null;
+        return Expression.LessThan(memberExpr, GetConstant(memberType, value));
+    }
+
+    public static BinaryExpression? BuildGreaterThanExpression(Type memberType, Expression memberExpr, string value)
+    {
+        if (memberType is { IsPrimitive: false, IsEnum: false }) return null;
+        return Expression.GreaterThan(memberExpr, GetConstant(memberType, value));
+    }
+
+    public static BinaryExpression? BuildLessThanOrEqualExpression(Type memberType, Expression memberExpr, string value)
+    {
+        if (memberType is { IsPrimitive: false, IsEnum: false }) return null;
+        return Expression.LessThan(memberExpr, GetConstant(memberType, value));
+    }
+
+    public static BinaryExpression? BuildGreaterThanOrEqualExpression(Type memberType, Expression memberExpr, string value)
+    {
+        if (memberType is { IsPrimitive: false, IsEnum: false }) return null;
+        return Expression.GreaterThan(memberExpr, GetConstant(memberType, value));
+    }
+}