IsNull or Coalesce functionality in LINQ?

Question

I have the following LINQ query:

return (from r in Repository.Query<Measurement>()
        where
            r.Postal.ToLowerInvariant() ==
                (string.IsNullOrEmpty(postalCode)
                    ? r.Postal : postalCode).ToLowerInvariant()
            &&
            r.Trait.ToLowerInvariant() ==
                (string.IsNullOrEmpty(trait)
                    ? r.Trait : trait).ToLowerInvariant()
        select r).ToList();

My goal was to simulate the functionality of IsNull or Coalesce, but LINQ doesn't like it. I get the following exception:

The unary operator Not is not defined for the type 'System.String'.
at System.Linq.Expressions.Expression.GetUserDefinedUnaryOperatorOrThrow(ExpressionType unaryType, String name, Expression operand)
   at System.Linq.Expressions.Expression.Not(Expression expression, MethodInfo method)
   at System.Linq.Expressions.Expression.Not(Expression expression)
   at NHibernate.Linq.Visitors.BinaryBooleanReducer.ProcessBinaryExpression(Expression exprToCompare, Expression exprToReturn, ExpressionType nodeType, Expression original)
   at NHibernate.Linq.Visitors.BinaryBooleanReducer.VisitBinary(BinaryExpression expr)
   at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp)
   at NHibernate.Linq.Visitors.ExpressionVisitor.VisitBinary(BinaryExpression b)
   at NHibernate.Linq.Visitors.BinaryBooleanReducer.VisitBinary(BinaryExpression expr)
   at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp)
   at NHibernate.Linq.Visitors.ExpressionVisitor.VisitBinary(BinaryExpression b)
   at NHibernate.Linq.Visitors.BinaryBooleanReducer.VisitBinary(BinaryExpression expr)
   at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp)
   at NHibernate.Linq.Visitors.ExpressionVisitor.VisitLambda(LambdaExpression lambda)
   at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp)
   at NHibernate.Linq.Visitors.ExpressionVisitor.VisitUnary(UnaryExpression u)
   at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp)
   at NHibernate.Linq.Visitors.ExpressionVisitor.VisitList(ReadOnlyCollection`1 original)
   at NHibernate.Linq.Visitors.ExpressionVisitor.VisitMethodCall(MethodCallExpression m)
   at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp)
   at NHibernate.Linq.NHibernateQueryProvider.TranslateExpression(Expression expression)
   at NHibernate.Linq.NHibernateQueryProvider.Execute(Expression expression)
   at NHibernate.Linq.Query`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at HarvestMap.Website.DataService.GetData(String postalCode, String trait, String measureType, Boolean ffOnly, String apiKey) in D:\Data\Projects\Active\Clients\HarvestMap\src\Website\DataService.svc.cs:line 90
   at HarvestMap.Website.DataService.GetDataText3(String postalCode, String trait, String measureType, Boolean ffOnly) in D:\Data\Projects\Active\Clients\HarvestMap\src\Website\DataService.svc.cs:line 162
   at SyncInvokeGetDataText3(Object , Object[] , Object[] )
   at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage41(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage1(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

That's it. No more clues as to what I'm doing wrong. Help?

Edit: This query worked when I was querying a list of objects in memory. As soon as I moved to NHibernate to execute against a db, I started getting the exception.

Answer 1

Try refactoring the IsNullOrEmpty condition like this:

return (from r in Repository.Query<Measurement>()
    where
        (string.IsNullOrEmpty(postalCode)
            || r.Postal.ToLowerInvariant() == postalCode.ToLowerInvariant()
        )
        &&
        (string.IsNullOrEmpty(trait)
            || r.Trait.ToLowerInvariant() == trait.ToLowerInvariant()
        )
    select r).ToList();

That may cause LINQ to evaluate the IsNullOrEmpty before sending off the query. If not, you could precalculate them manually and put a couple boolean variables in their place.

Answer 2

Have you tried forcing the deferred execution by calling Repository.Query().ToList() before the where clauses? I noticed it looks like NHibernate is attempting to convert the string.IsNullOrEmpty() call into SQL syntax (and failing).

return (from r in Repository.Query<Measurement>().ToList()
        where
            r.Postal.ToLowerInvariant() ==
                (string.IsNullOrEmpty(postalCode)
                    ? r.Postal : postalCode).ToLowerInvariant()
            &&
            r.Trait.ToLowerInvariant() ==
                (string.IsNullOrEmpty(trait)
                    ? r.Trait : trait).ToLowerInvariant()
        select r).ToList();