Linq Query ToLower

[Bryan Murphy]

We're using Postgres and NHibernate 1.2, but are planning a migration
to 2.0.x or 2.1.x soon. In one of my experiments, I'm trying to get a
query to use the following index:

CREATE INDEX users__index__username ON users(LOWER(username));

We haven't had any problems using these indexes with HQL, but we're
experimenting with Linq 2 SQL. I can't figure out how to get
NHibernate to call the database LOWER() function when writing a Linq
query. Am I missing something?

The query looks like this:

from u in session.Linq<User>()
where u.EmailAddress.ToLower() == this.EmailAddress.ToLower()
select u;

And the exception I'm getting (v2.1.0alpha2) is:

Unhandled Exception: System.ArgumentOutOfRangeException: Index was out
of range. Must be non-negative and less than the size of the
collection.
Parameter name: index
at System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument
argument, ExceptionResource resource)
at System.ThrowHelper.ThrowArgumentOutOfRangeException()
at System.SZArrayHelper.get_Item[T](Int32 index)
at System.Collections.ObjectModel.ReadOnlyCollection`1.get_Item(Int32 index)
at NHibernate.Linq.Visitors.EntityExpressionVisitor.VisitMethodCall(MethodCallExpression
expr)
at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp)
at NHibernate.Linq.Visitors.NHibernateExpressionVisitor.Visit(Expression exp)
at NHibernate.Linq.Visitors.EntityExpressionVisitor.FindEntity(Expression
expr, Boolean findFirst)
at NHibernate.Linq.Visitors.EntityExpressionVisitor.RootEntity(Expression
expr)
at NHibernate.Linq.Visitors.BinaryCriterionVisitor.VisitMethodCall(MethodCallExpression
expr)
at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp)
at NHibernate.Linq.Visitors.NHibernateExpressionVisitor.Visit(Expression exp)
at NHibernate.Linq.Visitors.BinaryCriterionVisitor.GetBinaryCriteria(ICriteria
rootCriteria, ISession session, BinaryExpression expr,
ComparePropToValue comp
arePropToValue, ComparePropToProp comparePropToProp,
CompareValueToCriteria compareValueToCriteria, ComparePropToCriteria
comparePropToCriteria)
at NHibernate.Linq.Visitors.WhereArgumentsVisitor.VisitBinaryCriterionExpression(BinaryExpression
expr)
at NHibernate.Linq.Visitors.WhereArgumentsVisitor.VisitBinary(BinaryExpression
expr)
at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp)
at NHibernate.Linq.Visitors.NHibernateExpressionVisitor.Visit(Expression exp)
at NHibernate.Linq.Visitors.ExpressionVisitor.VisitLambda(LambdaExpression
lambda)
at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp)
at NHibernate.Linq.Visitors.NHibernateExpressionVisitor.Visit(Expression exp)
at NHibernate.Linq.Visitors.WhereArgumentsVisitor.VisitUnary(UnaryExpression
expr)
at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp)
at NHibernate.Linq.Visitors.NHibernateExpressionVisitor.Visit(Expression exp)
at NHibernate.Linq.Visitors.WhereArgumentsVisitor.GetCriterion(ICriteria
rootCriteria, ISession session, Expression expression)
at NHibernate.Linq.Visitors.RootVisitor.HandleWhereCall(MethodCallExpression
call)
at NHibernate.Linq.Visitors.RootVisitor.VisitMethodCall(MethodCallExpression
expr)
at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp)
at NHibernate.Linq.Visitors.NHibernateExpressionVisitor.Visit(Expression exp)
at NHibernate.Linq.Visitors.NHibernateQueryTranslator.Translate(Expression
expression, QueryOptions queryOptions)
at NHibernate.Linq.NHibernateQueryProvider.Execute(Expression expression)
at NHibernate.Linq.Query`1.System.Collections.IEnumerable.GetEnumerator()

Thanks,
Bryan

[Hendry Luk]

I also bumped into this problem (non-case-sensitive String.Contains() is a really common requirement), and still looking for a solution for this.. which apparently still produces the same exception in the recent release of NH-Linq. Any idea?
Thanks

[Tuna Toksoz]

var query = from e in db.Employees

where e.FirstName.Contains("a")

                                            select e;

this works, regarding to string.contains.

Tuna Toksöz
Eternal sunshine of the open source mind.

http://devlicio.us/blogs/tuna_toksoz
http://tunatoksoz.com
http://twitter.com/tehlike

[Tuna Toksoz]

can you retry your query like this:

string emailAddress=his.EmailAddress.ToLower();

from u in session.Linq<User>()

where u.EmailAddress.ToLower() == emailAddress
select u;

Tuna Toksöz
Eternal sunshine of the open source mind.

http://devlicio.us/blogs/tuna_toksoz
http://tunatoksoz.com
http://twitter.com/tehlike

[Hendry Luk]

I thought that would generate title LIKE'%a%'? (instead of LOWER(title) LIKE '%a%')

[Hendry Luk]

Sorry, no joy.. Still exact same exception..

Cheers

[Hendry Luk]

Quite weirdly, this doesnt work too:
u.EmailAddress.Equals(emailAddress, StringComparison.CurrentCultureIgnoreCase))

.. even though StringComparison.CurrentCultureIgnoreCase works perfectly with both StartsWith() and EndsWith() .

And unfortunately, String.Contains() doesn't have overload that accepts StringComparison parameter.

Cheers

[Hendry Luk]

Just ignore my previous email.. StringComparison.CurrentCultureIgnoreCase doesnt work either. It was just because my database was configured to be case-insensitive for LIKE. Sorry for the noise.

[William Claiming]

This throws the same Exception as well:

var txAddresses = addresses.Where(address => string.Compare
(address.State, "Tx", true) == 0);

I don't control the Database in my situation.

Does anyone know how to do case insensitive string comparison with
NHibernate.Linq?