A couple of days ago Matteo Migliore asked me a question about why String.Equals(string, StringComparison) was not supported in Linq to NHibernate while it was in Entity Framework.

My first answer was that the Linq to NHibernate implementation might have some missing and, if it was not supported, we can extend the provider to add support for missing functions if we know how to translate them in SQL (I even wrote a couple of blog posts in the past about how to extend the provider to add functions to it: NHibernate 3 - Extending the Linq Provider to fix some System.NotSupportedException and NHibernate - Customize the Linq provider to call your user defined SQL functions).

With those things in mind we have all the tools to cover the gaps and implement what we need; basically we want to ‘force’ a case insensitive string comparison at database level using something like this:

var res = Session.Query().Where(t => t.T1.Equals("test", StringComparison.InvariantCultureIgnoreCase)).FirstOrDefault();

I’m not a true SQL expert, but when it comes to string comparison a lot of database stuff kicks in (like collations and such) and every database can have a different default behavior to compare strings (by default it should be case insensitive, but I’m not really sure of this...so DBA experts can say their own here); aside of that, I think that the default behavior ORMs have is to rely on what the database does.

Matteo confirmed this last statement looking at the SQL expression that Entity Framework 4 generated for his test domain when calling Equals passing in an xxxIgnoreCase StringComparison, in both case (with and without the parameter) EF4 generated this SQL statement:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName]
FROM [dbo].[Customers] AS [Extent1]
WHERE N'temp' = [Extent1].[FirstName]

As you can see this is totally dependent on the database and it can ignore our will to perform a case insensitive equals (due to the database collation for example)...so it’s not a ‘perfect’ translation of what the user wanted to do and it may introduce subtle bugs in your code.

Linq to NHibernate raises us a ‘warning’ saying that this kind of operation is not supported (intentionally or not...we’ll never know...), so the first step is to extend the provider to support this kinds of equals:

public class EqualsStringGenerator : BaseHqlGeneratorForMethod
{
	public EqualsStringGenerator()
	{
		SupportedMethods = new[]
		                   	{
		                   		ReflectionHelper.GetMethodDefinition(x => x.Equals(null, StringComparison.CurrentCulture))
		                   	};
	}

	public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, ReadOnlyCollection arguments, HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
	{
		return treeBuilder.Equality(
			visitor.Visit(targetObject).AsExpression(),
			visitor.Visit(arguments[0]).AsExpression());
	}
}

public class ExtendedLinqtoHqlGeneratorsRegistry : DefaultLinqToHqlGeneratorsRegistry
{
	public ExtendedLinqtoHqlGeneratorsRegistry()
	{
		this.Merge(new EqualsStringGenerator());
	}
}

You can read my previous articles for the details, however the important thing here is that we are adding a new supported method (the overloaded version of String.Equals) and how we translate it to SQL in the BuildHql() function: we are basically ignoring the second parameter - the StringComparison - and we are constructing a normal Equality between two operands.

This extension makes Linq to NHibernate operate exactly as EF4 does and will generate a similar kind of SQL query (it completely relies on the database for the case sensitivity or not of the operation).

But we can do the things a little bit better and add support for forcing a case insensitive equals even if case sensitive collations are used for your database, making Linq-To-NHibenrate a little bit smarter. The trick is simple: add a SQL LOWER() call to both the sides of the equality operator:

public class EqualsStringGenerator : BaseHqlGeneratorForMethod
{
	public EqualsStringGenerator()
	{
		SupportedMethods = new[]
		                   	{
		                   		ReflectionHelper.GetMethodDefinition(x => x.Equals(null, StringComparison.CurrentCulture))
		                   	};
	}

	public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, ReadOnlyCollection arguments, HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
	{
		StringComparison comparison = (StringComparison)(arguments[1].As().Value);
		if (comparison == StringComparison.CurrentCultureIgnoreCase ||
		    comparison == StringComparison.InvariantCultureIgnoreCase ||
		    comparison == StringComparison.OrdinalIgnoreCase)
		{
			return treeBuilder.Equality(
				treeBuilder.MethodCall("lower", new[] { visitor.Visit(targetObject).AsExpression() }),
				treeBuilder.MethodCall("lower", new[] { visitor.Visit(arguments[0]).AsExpression() }));
		}
		return treeBuilder.Equality(
			visitor.Visit(targetObject).AsExpression(),
			visitor.Visit(arguments[0]).AsExpression());
	}
}

Using the treeBuilder.MethodCall(“lower”, ...) we are adding the desired call, the good thing is that this will be database independent (as long as your database support this function, otherwise you can extend the NHibernate dialect for your database and add support for it); this is what NHibernate now generates for my test domain:

NHibernate: select TOP (@p0)  table1x0_.ID as ID7_, table1x0_.T1 as T2_7_, table1x0_.T2 as T3_7_, table1x0_.GuidTest as GuidTest7_, table1x0_.Tb2 as Tb5_7_, table1x0_.Tb3 as Tb6_7_ from TABLE1 table1x0_ where lower(table1x0_.T1)=lower(@p1);@p0 = 1 [Type: Int32 (0)], @p1 = 'test' [Type: String (4000)]

This is far from being an optimized solution and we are not translating correctly all the behaviors of this overloaded version of the string comparison, nonetheless is a good starting point for it.

How to force then a case sensitive string comparison ? One thing that comes into my mind is to extend the provider once again and to cast both the operands to a binary type, in this way the comparison will be over array of bytes and it will be ‘sensitive’ by default...but I’ll leave this to you Open-mouthed smile.

Related Content