This Dev Hacked EF Core Just to Make SQL LIKE Work in .NET 8 — And It Actually Does

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 // compile but NOT work in .NET8/C#/ASP.NET8/EF8 project. // throws Exception: // No applicable method 'Like' exists in type 'DynamicFunctions' (at index 18) //....code fragments using System.Linq.Dynamic.Core; IQueryable 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); // Code sample from the Internet // dynamic execution of SQL LIKE in library System.Linq.Dynamic.Core // compile but NOT work in .NET8/C#/ASP.NET8/EF8 project. // throws Exception: // No applicable method 'Like' exists in type 'DynamicFunctions' (at index 18) //....code fragments using System.Linq.Dynamic.Core; IQueryable 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. 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. it looks like the authors of the library abandoned support for the Like method sometime in the past 4 My own patch for SQL LIKE in .NET8 So, after looking into the library source code, I decided that I want 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 .NET8/C#/ASP.NET8/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 iQueryableOfAnonymous var config = new ParsingConfig(); config.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 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(); /* This was added to support the "EF_TBS_Context.Like" function in System.Linq.Dynamic.Core */ 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."); // The first argument is the string to match var matchExpression = (SqlExpression)args[0]; // The second argument is the pattern var pattern = (SqlExpression)args[1]; // Validate input types, so to avoid SQL injection 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."); } // Escape special characters in the pattern if it's a constant if (pattern is SqlConstantExpression constantPattern) { string? escapedPattern = EscapeFunction2(constantPattern.Value?.ToString() ?? string.Empty); pattern = new SqlConstantExpression((object)(escapedPattern), constantPattern.TypeMapping); } // Create a SQL function expression for the LIKE operation return new LikeExpression(matchExpression, pattern, null, null); } ); } private static string EscapeFunction(string pattern) { //hope this is enough to prevent SQL injection string shortenedString = StringWithMaxLength2(pattern.Trim(), 30); shortenedString= shortenedString.Replace("[", "") .Replace("]", "") .Replace("-", "") .Replace("/", "") .Replace("\\", "") .Replace(";", ""); return shortenedString; } private static string EscapeFunction2(string pattern) { //hope this is enough to prevent SQL injection //this should preserve alphanmeric of any EU language //and _ and % I need for search pattern in my application //and filter out all other characters string shortenedString = StringWithMaxLength2(pattern.Trim(), 30); Func 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)); } } //===================================================== /* 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 NOTE: SPELLING mistake in the System.Linq.Dynamic.Core.CustomTypeProviders library. Note usage of K vs Q in interfaces: System.Linq.Dynamic.Core.CustomTypeProviders.IDynamicLinkCustomTypeProvider System.Linq.Dynamic.Core.CustomTypeProviders.IDynamicLinqCustomTypeProvider They created a mess, you will need to use K in .NET8 and Q in .NET9 In .NET8 it gives a warnings: 'IDynamicLinkCustomTypeProvider' is obsolete: 'Please use the IDynamicLinqCustomTypeProvider interface instead.' But I need it in .NET8 project for now. */ public class DynamicLinqCustomTypeProvider : AbstractDynamicLinqCustomTypeProvider, System.Linq.Dynamic.Core.CustomTypeProviders.IDynamicLinkCustomTypeProvider { private DynamicLinqCustomTypeProvider() : base(new List ()) { } public HashSet ? _customTypes = null; public HashSet GetCustomTypes() { if (_customTypes == null) { _customTypes = GetCustomTypes_worker(); } return _customTypes; } private HashSet 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 (FindTypesMarkedWithDynamicLinqTypeAttribute(loadedAssemblies)) { typeof(EF_TBS_Context) }; } private Dictionary >? _extensionMethods = null; public Dictionary > GetExtensionMethods() { if (_extensionMethods == null) { _extensionMethods = GetExtensionMethods_worker(); } return _extensionMethods; } private Dictionary > GetExtensionMethods_worker() { var types = GetCustomTypes(); List > list = new List >(); 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 (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) { //Assembly[]? assemblies = AppDomain.CurrentDomain.GetAssemblies(); return ResolveType(MyAssemblies, typeName); } public Type? ResolveTypeBySimpleName(string typeName) { //Assembly[]? assemblies = AppDomain.CurrentDomain.GetAssemblies(); 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) { var _customTypeProvider2 = Create(); _customTypeProvider = _customTypeProvider2; } return _customTypeProvider; } } private static System.Linq.Dynamic.Core.CustomTypeProviders.IDynamicLinkCustomTypeProvider Create() { return new DynamicLinqCustomTypeProvider(); } } //====================================================== // Written by Mark Pelf 2025 // dynamic execution of SQL LIKE in library System.Linq.Dynamic.Core // "works on my system" // Works in .NET8/C#/ASP.NET8/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 iQueryableOfAnonymous var config = new ParsingConfig(); config.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 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(); /* This was added to support the "EF_TBS_Context.Like" function in System.Linq.Dynamic.Core */ 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."); // The first argument is the string to match var matchExpression = (SqlExpression)args[0]; // The second argument is the pattern var pattern = (SqlExpression)args[1]; // Validate input types, so to avoid SQL injection 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."); } // Escape special characters in the pattern if it's a constant if (pattern is SqlConstantExpression constantPattern) { string? escapedPattern = EscapeFunction2(constantPattern.Value?.ToString() ?? string.Empty); pattern = new SqlConstantExpression((object)(escapedPattern), constantPattern.TypeMapping); } // Create a SQL function expression for the LIKE operation return new LikeExpression(matchExpression, pattern, null, null); } ); } private static string EscapeFunction(string pattern) { //hope this is enough to prevent SQL injection string shortenedString = StringWithMaxLength2(pattern.Trim(), 30); shortenedString= shortenedString.Replace("[", "") .Replace("]", "") .Replace("-", "") .Replace("/", "") .Replace("\\", "") .Replace(";", ""); return shortenedString; } private static string EscapeFunction2(string pattern) { //hope this is enough to prevent SQL injection //this should preserve alphanmeric of any EU language //and _ and % I need for search pattern in my application //and filter out all other characters string shortenedString = StringWithMaxLength2(pattern.Trim(), 30); Func 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)); } } //===================================================== /* 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 NOTE: SPELLING mistake in the System.Linq.Dynamic.Core.CustomTypeProviders library. Note usage of K vs Q in interfaces: System.Linq.Dynamic.Core.CustomTypeProviders.IDynamicLinkCustomTypeProvider System.Linq.Dynamic.Core.CustomTypeProviders.IDynamicLinqCustomTypeProvider They created a mess, you will need to use K in .NET8 and Q in .NET9 In .NET8 it gives a warnings: 'IDynamicLinkCustomTypeProvider' is obsolete: 'Please use the IDynamicLinqCustomTypeProvider interface instead.' But I need it in .NET8 project for now. */ public class DynamicLinqCustomTypeProvider : AbstractDynamicLinqCustomTypeProvider, System.Linq.Dynamic.Core.CustomTypeProviders.IDynamicLinkCustomTypeProvider { private DynamicLinqCustomTypeProvider() : base(new List ()) { } public HashSet ? _customTypes = null; public HashSet GetCustomTypes() { if (_customTypes == null) { _customTypes = GetCustomTypes_worker(); } return _customTypes; } private HashSet 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 (FindTypesMarkedWithDynamicLinqTypeAttribute(loadedAssemblies)) { typeof(EF_TBS_Context) }; } private Dictionary >? _extensionMethods = null; public Dictionary > GetExtensionMethods() { if (_extensionMethods == null) { _extensionMethods = GetExtensionMethods_worker(); } return _extensionMethods; } private Dictionary > GetExtensionMethods_worker() { var types = GetCustomTypes(); List > list = new List >(); 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 (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) { //Assembly[]? assemblies = AppDomain.CurrentDomain.GetAssemblies(); return ResolveType(MyAssemblies, typeName); } public Type? ResolveTypeBySimpleName(string typeName) { //Assembly[]? assemblies = AppDomain.CurrentDomain.GetAssemblies(); 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) { var _customTypeProvider2 = Create(); _customTypeProvider = _customTypeProvider2; } 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 tool SQL Server Profiler. 5 Conclusion 5.1 Future versions of System.Linq.Dynamic.Core Will the code above work with future versions of 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 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 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 enters instead of 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 character 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. 6 References [12] https://www.nuget.org/packages/System.Linq.Dynamic.CoreSystem.Linq.Dynamic.Core https://www.nuget.org/packages/System.Linq.Dynamic.Core [13] https://github.com/zzzprojects/System.Linq.Dynamic.Corezzzprojects/ System.Linq.Dynamic.Core https://github.com/zzzprojects/System.Linq.Dynamic.Core [14] https://dynamic-linq.net/A FREE & Open Source LINQ Dynamic Query Library https://dynamic-linq.net/ [20] https://github.com/IronBox/antisqli-coreAnti-SQL Injection (AntiSQLi) Library https://github.com/IronBox/antisqli-core [21] https://www.invicti.com/blog/web-security/sql-injection-cheat-sheet/SQL injection cheat sheet https://www.invicti.com/blog/web-security/sql-injection-cheat-sheet/ [22] https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.htmlSQL Injection Prevention Cheat Sheet https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html