Strange problem with LINQ to NHibernate and string comparison

Question

I'm using LINQ to NHibernate and encountered a strange problem while comparing strings. Following code works fine but when I un-comment: //MyCompareFunc(dl.DamageNumber, damageNumberSearch) && and comment: dl.DamageNumber.Contains(damageNumberSearch) && then it breaks down and seems that MyCompareFunc() always return true while dl.DamageNumber.Contains(damageNumberSearch) sometimes return true and sometimes returns false.

In other words when I use string.Contains() in LINQ query directly it works, but when I move it to a method, it does not work.

    internal List<DamageList> SearchDamageList(
    DateTime? sendDateFromSearch, DateTime? sendDateToSearch, string damageNumberSearch,
    string insuranceContractSearch)
    {
        var q = from dl in session.Linq<DamageList>()
                where
                CommonHelper.IsDateBetween(dl.SendDate, sendDateFromSearch, sendDateToSearch) &&
                //MyCompareFunc(dl.DamageNumber, damageNumberSearch) &&
                dl.DamageNumber.Contains(damageNumberSearch) &&
                insuranceContractSearch == null ? true : CommonHelper.IsSame(dl.InsuranceContract, insuranceContractSearch)
                select dl;

        return q.ToList<DamageList>();
    }

    private bool MyCompareFunc(string damageNumber, string damageNumberSearch)
    {
        return damageNumber.Contains(damageNumberSearch);
    }

Answer 1

I have to admit I'm not an expert in NHibernate, but while using a different ORM we have frequently run into the same kind of problem. The thing is that the LINQ engine, while translating the query, is capable of recognizing simple string functions from .NET library like Contains and translating them into the SQL equivalent. This SQL equivalent does the comparison case-insensitive (it depends on the settings of the database, but that's usually the default).

On the other hand, it's not possible for him to parse the source code of your custom function and therefore it can't translate it into SQL and has to just execute it in memory after preloading the result of the previous query from the database. This means it is executed as a .NET code, where the comparison is done by default case-sensitive.

That could be the reason for your mismatch of results ;)

Answer 2

Linq works with expressions, not with compliled functions. It will be fine if you use expression> instead of the "compiled" method.