Why is Trim() failing in this expression?

Question

I have the following method:

        var catIds = DetachedCriteria.For<Category>()
            .Add<Category>(c => c.TypeCode == "IMA")
            .SetProjection(LambdaProjection.Property<Category>(s => s.Id));

This is returning nothing because in the database the field is nchar(10). I want to Trim() the TypeCode value, as follows:

        var catIds = DetachedCriteria.For<Category>()
            .Add<Category>(c => c.TypeCode.Trim() == "IMA")
            .SetProjection(LambdaProjection.Property<Category>(s => s.Id));

but it returns the NHibernate error:

Unrecognised method call in epression c.TypeCode.Trim()

One of the guys here in the office thinks it's because HHibernate doesn't know how to convert .Trim() to SQL (or something along those lines). Can anyone suggest how I can fix this?

Answer 1

Your office guys are correct -- the linq provider doesn't know how to translate C# string.Trim() to whatever sql variant it is.

As for the fix, linq can make you do the damndest things -- like padding your data to match CHAR(10) rather than TRIM() to a 'normal' string.

Answer 2

Try right-padding the value you're comparing with to the required length, for example:

string cmpValue = "IMA".PadRight(10);

var catIds = DetachedCriteria.For<Category>()
        .Add<Category>(c => c.TypeCode == cmpValue)
        .SetProjection(LambdaProjection.Property<Category>(s => s.Id));

Answer 3

I hope the following code may solve your problem:

Func<string, string> trimmer = (x) => { 
                   return !string.IsNullOrEmpty(x) ? x.Trim() : string.Empty; };

and then use it like:

var catIds = DetachedCriteria.For<Category>()
            .Add<Category>(c => trimmer(c.TypeCode) == "IMA")
            .SetProjection(LambdaProjection.Property<Category>(s => s.Id));