Why is Trim() failing in this expression?

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 some开发者_开发知识库thing along those lines). Can anyone suggest how I can fix this?

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));

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.

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));