.NET 8 - System.Linq.Dynamic.Core – using SQL LIKE

Mark Pelf
1d
254
0
4

Article

109_System.Linq.Dynamic.Core_LIKE.zip

Abstract: In my .NET8/C#/ASP.NET8/EF8 project, I am using System.Linq.Dynamic.Core for back-end processing. I had a problem; it would not support SQL LIKE. Here is how I resolved the issue. Code samples are enclosed._**

1. System.Linq.Dynamic.Core library

I am using EF8 in my NET8/C#/ASP.NET8 project. There is another library that is useful for back-end processing in my application, which is System.Linq.Dynamic.Core library \[12\]-\[14\]. There is a nice tutorial available at \[14\].

2. How to do SQL LIKE – code samples from the Internet would not work?

I wanted to implement dynamic execution of LIKE. Simply, my users want to use search strings like “\*U\*” or “A?C”. There are code samples on the internet, as well as on sites \[12\]-\[14\], but they would not work. I was constantly getting EXCEPTIONS. Here is code from the Internet samples that would compile but NOT work in .NET8/C#/ASP.NET8/EF8 project.

// Code sample from the Internet
// Dynamic execution of SQL LIKE in library System.Linq.Dynamic.Core
// Compiles but does NOT work in .NET 8 / C# / ASP.NET 8 / EF8 project.
// Throws Exception:
// No applicable method 'Like' exists in type 'DynamicFunctions' (at index 18)

using System.Linq.Dynamic.Core;

IQueryable<T> iQueryableOfAnonymous;

var config = new ParsingConfig();

// We plan to build dynamic LINQ expression in this string
string dynamicLinqSearch = string.Empty;

dynamicLinqSearch += $" DynamicFunctions.Like({column.Field}, \"{patternLike}\") ";

// Using System.Linq.Dynamic.Core
iQueryableOfAnonymous = iQueryableOfAnonymous.Where(config, dynamicLinqSearch);

3. Investigating System.Linq.Dynamic.Core library

I needed the functionality of SQL LIKE or otherwise to change the architecture of the application. So, I downloaded the library source code to have a look from \[13\]. I didn’t plan to contribute to the library because I do not have time to fully understand it, just to patch it for my application. I wanted a “fast and dirty” fix and to see why it gives that Exception. Here are my observations after looking into the lib System.Linq.Dynamic.Core source code, but I do not claim I understand it fully.

Investigating System.Linq.Dynamic.Core

Expression Parser

Dynamic functions

Interface Linq- Link vs Linq

So, it looks like the authors of the library abandoned support for the Like method sometime in the past**. The Internet samples I was using are no longer valid.

4. My own patch for SQL LIKE in .NET 8

So, after looking into the library source code, I decided that I wanted to make my own patch to support SQL LIKE. Here is the solution I assembled for .NET8, and that works on my system.

// Written by Mark Pelf 2025
// Dynamic execution of SQL LIKE in library System.Linq.Dynamic.Core
// "Works on my system"
// Works in .NET 8 / C# / ASP.NET 8 / EF8 project
// In SQL Server Profiler, I get nice SQL code:
// ...([t].[KUNDEN_NR] LIKE N'%U%' OR [t].[POST_NAME] LIKE N'%U%' OR ..............

//=============================================================
// Code fragments
using System.Linq.Dynamic.Core;
using System.Linq.Dynamic.Core.CustomTypeProviders;

IQueryable<T> iQueryableOfAnonymous;

var config = new ParsingConfig
{
    CustomTypeProvider = DynamicLinqCustomTypeProvider.Instance
};

// We plan to build dynamic LINQ expression in this string
string dynamicLinqSearch = string.Empty;

dynamicLinqSearch += $" EF_TBS_Context.Like({column.Field}, \"{patternLike}\") ";

// Using System.Linq.Dynamic.Core
iQueryableOfAnonymous = iQueryableOfAnonymous.Where(config, dynamicLinqSearch);

//=============================================================
public partial class EF_TBS_Context : DbContext
{
    public EF_TBS_Context(DbContextOptions<EF_TBS_Context> options)
        : base(options)
    {
    }
}

//===================================================================
public partial class EF_TBS_Context
{
    // This was added to support the "EF_TBS_Context.Like" function in System.Linq.Dynamic.Core
    // This is a bit of a hack to add database LIKE functionality to System.Linq.Dynamic.Core
    public static bool Like(string matchExpression, string pattern) => throw new Exception();

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
    {
        modelBuilder
            .HasDbFunction(typeof(EF_TBS_Context).GetMethod(nameof(Like)) ??
                throw new InvalidOperationException())
            .HasTranslation(args =>
            {
                if (args.Count != 2)
                    throw new ArgumentException("Like function requires exactly 2 arguments.");

                var matchExpression = (SqlExpression)args[0];
                var pattern = (SqlExpression)args[1];

                if (matchExpression == null || pattern == null)
                    throw new ArgumentNullException("Arguments for the LIKE function cannot be null.");

                if (pattern is not SqlConstantExpression && pattern is not SqlParameterExpression)
                    throw new ArgumentException("The pattern must be a constant or parameterized expression.");

                if (pattern is SqlConstantExpression constantPattern)
                {
                    string? escapedPattern = EscapeFunction2(constantPattern.Value?.ToString() ?? string.Empty);
                    pattern = new SqlConstantExpression((object)(escapedPattern), constantPattern.TypeMapping);
                }

                return new LikeExpression(matchExpression, pattern, null, null);
            });
    }

    private static string EscapeFunction(string pattern)
    {
        string shortenedString = StringWithMaxLength2(pattern.Trim(), 30);
        shortenedString = shortenedString.Replace("[", "")
                                         .Replace("]", "")
                                         .Replace("-", "")
                                         .Replace("/", "")
                                         .Replace("\\", "")
                                         .Replace(";", "");
        return shortenedString;
    }

    private static string EscapeFunction2(string pattern)
    {
        string shortenedString = StringWithMaxLength2(pattern.Trim(), 30);
        Func<char, bool> filter = ch => char.IsLetterOrDigit(ch) || ch == '_' || ch == '%';
        string result = new string(shortenedString.Where(filter).ToArray());
        return result;
    }

    private static string? StringWithMaxLength(string? value, int maxLength)
    {
        return value?.Substring(0, Math.Min(value.Length, maxLength));
    }

    private static string StringWithMaxLength2(string value, int maxLength)
    {
        return value.Substring(0, Math.Min(value.Length, maxLength));
    }
}

//=============================================================
// Support for EF_TBS_Context.Like in System.Linq.Dynamic.Core
// Note spelling issue in the System.Linq.Dynamic.Core.CustomTypeProviders library:
// IDynamicLinkCustomTypeProvider (K) vs IDynamicLinqCustomTypeProvider (Q)
// In .NET 8, use K; in .NET 9, use Q
// In .NET 8, the K interface is marked obsolete, but required here

public class DynamicLinqCustomTypeProvider : AbstractDynamicLinqCustomTypeProvider,
    System.Linq.Dynamic.Core.CustomTypeProviders.IDynamicLinkCustomTypeProvider
{
    private DynamicLinqCustomTypeProvider() : base(new List<Type>())
    {
    }

    public HashSet<Type>? _customTypes = null;

    public HashSet<Type> GetCustomTypes()
    {
        if (_customTypes == null)
        {
            _customTypes = GetCustomTypes_worker();
        }
        return _customTypes;
    }

    private HashSet<Type> GetCustomTypes_worker()
    {
        var loadedAssemblies = MyAssemblies.ToList();
        var loadedPaths = loadedAssemblies.Where(a => !a.IsDynamic).Select(a => a.Location).ToArray();
        var referencedPaths = Directory.GetFiles(AppDomain.CurrentDomain.BaseDirectory, "*.dll");
        var toLoad = referencedPaths.Where(r => !loadedPaths.Contains(r, StringComparer.InvariantCultureIgnoreCase)).ToList();

        toLoad.ForEach(path => loadedAssemblies.Add(AppDomain.CurrentDomain.Load(AssemblyName.GetAssemblyName(path))));

        return new HashSet<Type>(FindTypesMarkedWithDynamicLinqTypeAttribute(loadedAssemblies))
        {
            typeof(EF_TBS_Context)
        };
    }

    private Dictionary<Type, List<MethodInfo>>? _extensionMethods = null;

    public Dictionary<Type, List<MethodInfo>> GetExtensionMethods()
    {
        if (_extensionMethods == null)
        {
            _extensionMethods = GetExtensionMethods_worker();
        }
        return _extensionMethods;
    }

    private Dictionary<Type, List<MethodInfo>> GetExtensionMethods_worker()
    {
        var types = GetCustomTypes();
        List<Tuple<Type, MethodInfo>> list = new List<Tuple<Type, MethodInfo>>();

        foreach (var type in types)
        {
            var extensionMethods = type.GetMethods(BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic)
                                       .Where(x => x.IsDefined(typeof(ExtensionAttribute), false)).ToList();
            extensionMethods.ForEach(x => list.Add(new Tuple<Type, MethodInfo>(x.GetParameters()[0].ParameterType, x)));
        }

        return list.GroupBy(x => x.Item1, tuple => tuple.Item2).ToDictionary(key => key.Key, methods => methods.ToList());
    }

    private Assembly[]? _assemblies = null;

    private Assembly[] MyAssemblies
    {
        get
        {
            if (_assemblies == null)
            {
                _assemblies = AppDomain.CurrentDomain.GetAssemblies();
            }
            return _assemblies;
        }
        set => _assemblies = value;
    }

    public Type? ResolveType(string typeName)
    {
        return ResolveType(MyAssemblies, typeName);
    }

    public Type? ResolveTypeBySimpleName(string typeName)
    {
        return ResolveTypeBySimpleName(MyAssemblies, typeName);
    }

    // Singleton pattern
    private static System.Linq.Dynamic.Core.CustomTypeProviders.IDynamicLinkCustomTypeProvider? _customTypeProvider = null;

    public static System.Linq.Dynamic.Core.CustomTypeProviders.IDynamicLinkCustomTypeProvider Instance
    {
        get
        {
            if (_customTypeProvider == null)
            {
                _customTypeProvider = Create();
            }
            return _customTypeProvider;
        }
    }

    private static System.Linq.Dynamic.Core.CustomTypeProviders.IDynamicLinkCustomTypeProvider Create()
    {
        return new DynamicLinqCustomTypeProvider();
    }
}
//=============================================================

So, I got dynamic SQL LIKE working in my project, and I verified that by looking into generated SQL via the SQL Server Profiler.

5. Conclusion

5.1. Future versions of the System.Linq.Dynamic.Core

Will the code above work with future versions of the System.Linq.Dynamic.Core library? I am not sure, maybe they will add support for SQL LIKE to library itself. For now, it works. Maybe it is a bit of a hack, but it works.

If anyone can think of a better solution, please let me know.

5.2. Is it safe regarding SQL Injection?

Is this code introducing a security problem regarding SQL Injection problem? This patch is going quite low in Expressions resolution, as far as I can see, do not have time now to study it properly. If users enter instead of the search string “\*U\*” something like “; DROP TABLE… ;” or similar crap, will that be stopped on some level? I do not have time to look deeply at that right now.

I added some SQL Escape functionality, hope that is enough. I filter out everything that is not EU language characters or digit, so that should stop any injected script. I see some open source “Anti-SQL Injection (AntiSQLi) Library”, but have no time for all that now. There is some literature at \[21\] and \[22\], but who has the time to read all that?

Maybe someone who better understands EF/Expression resolution (translation into SQL) can answer or suggest improvements.