Make LINQ expression case insensitive

Question

I have bellow LINQ Expression and I need to make Contains as case insensitive, tried different approach but none of them is working

ParameterExpression paramExpr = Expression.Parameter(typeof(EmployeeEntity));
var propertyName = Expression.Property(paramExpr, "EmpName");
//for type convertion start
var propertyType = ((PropertyInfo)propertyName.Member).PropertyType;
var converter = TypeDescriptor.GetConverter(propertyType); 
if (!converter.CanConvertFrom(typeof(string))) 
    throw new NotSupportedException();
var propertyValue = converter.ConvertFromInvariantString("john"); 
var constant = Expression.Constant(propertyValue);
var valueExpression = Expression.Convert(constant, propertyType);
//for type convertion ends

MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
var someValue = Expression.Constant(propertyValue, typeof(string));
var finalExpression = Expression.Call(propertyName, method, someValue);

In my Table the EmpName is 'John' but my above query will return zero rows, so how to make above query case insensitive.

what database are you using?But anyway you can generate `ToLower()` calls on your properties. — Guru Stron, Apr 09 '21 at 11:37
Maybe this helps: https://stackoverflow.com/questions/444798/case-insensitive-containsstring — MakePeaceGreatAgain, Apr 09 '21 at 11:40
Which version of EF do you use? For EF core, see: https://stackoverflow.com/questions/43277868/entity-framework-core-contains-is-case-sensitive-or-case-insensitive — Christoph Lütjen, Apr 09 '21 at 12:26
@GuruStron:I am using MS SQL, I tried with ToLower but the exact syntax I am not getting, Can u please provide the exact syntax with my above example — user3501613, Apr 09 '21 at 13:38

score 2 · Accepted Answer · answered Apr 09 '21 at 12:32

2

Will this do it? It should get the Contains method with the StringComparison parameter and pass in the value to ignore case.

MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string), typeof(StringComparison) });
var someValue = Expression.Constant(propertyValue, typeof(string));
var comparisonValue = Expression.Constant(StringComparison.OrdinalIgnoreCase, typeof(StringComparison));
var finalExpression = Expression.Call(propertyName, method, someValue, comparisonValue);

answered Apr 09 '21 at 12:32

Steve Harris

5,014
1
10
25

I'm pretty sure this will not be translated by EF. – Guru Stron Apr 09 '21 at 13:48
@Steve Harris: thanks for the help. this is working – user3501613 Apr 09 '21 at 13:51
You're probably right there @GuruStron. There is not a flag in SQL to make a case insensitive comparison and this would either result in a query where the column and value will be converted to the same case, or that the results will be retrieved from the database before the comparison is applied. But it will get the required result. – Steve Harris Apr 09 '21 at 13:53
@SteveHarris it seems that I was wrong, cause it worked for the OP =) – Guru Stron Apr 09 '21 at 13:54

score 0 · Answer 2 · answered Apr 09 '21 at 11:57

If you have access to your database and if it supports different collations (e.g. MS SQL), then the easiest way to solve the problem is to change the collation of the field to case insensitive alternative:

ALTER TABLE [MyTable]
  ALTER COLUMN [MyColumn] NVARCHAR(...) COLLATE Latin1_General_CI_AS

Otherwise, as mentioned in the comments, you can generate .ToLower() calls for properties and values.

Make LINQ expression case insensitive

2 Answers2