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<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.
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 .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<T> 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<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();
/* 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<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));
}
}
//=====================================================
/* 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<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)
{
//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
[13] https://github.com/zzzprojects/System.Linq.Dynamic.Corezzzprojects/ System.Linq.Dynamic.Core
[14] https://dynamic-linq.net/A FREE & Open Source LINQ Dynamic Query Library
[20] https://github.com/IronBox/antisqli-coreAnti-SQL Injection (AntiSQLi) Library
[21] https://www.invicti.com/blog/web-security/sql-injection-cheat-sheet/SQL injection cheat sheet
[22] https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.htmlSQL Injection Prevention Cheat Sheet
-xl-(1)-xl-xl.jpg)
