Lambda Expression Weirdness in Linq to SQL Where condition

Question

Hi, I am working on an ASP.Net MVC application which uses the repository pattern with linq to sql as my data source. In my repository I expose the following method:

public IEnumerable<T> Find(Expression<Func<T, bool>> where)
{
    return _context.GetTable<T>().Where(where);
}

I am able to call this by saying:

repository<User>.Find(u => true);

But if I try doing (when search is null):

repository<User>.Find(u => !string.IsNullOrEmpty(search) ? u.UserName.Contains(search) : true);

I get the error:

Value cannot be null. Parameter name: text

I thought the lambda expression would execute the same thing since the value of search is null but this is clearly not the case.

I'd appreciate it if someone could help. Thanks.

Answer 1

Contains expects a string. You should pass string.Emtpy.

u.UserName.Contains(search)

If you try this, it'll compile but you'll get a runtime error:

string str = "Leniel";

if (str.Contains(null))
{
    var num = 1;
}

Error:

Value cannot be null.
Parameter name: value

Answer 2

Because this is an expression, normal conditional operator logic (not evaluating the false expression) don't automatically apply. Since the expression is actually being translated into a SQL query expression, LINQ to SQL is evaluating the parameters of the condition, and in this case it's null. While LIKE null is valid SQL syntax, evidently LINQ to SQL doesn't appreciate being passed null. You'll have to construct two different queries, one for a null term, one for a non-null term.

Either that, or pass something like string.Empty to Contains. The only impact here will be the inability to use an index on the column, since the server will be doing a repeated conditional evaluation on every row, forcing a table scan. I'd advise using two queries without the conditional.

Answer 3

To add to the other answers, if you have to follow this ideal, you could simply do this instead:

repository<User>.Find(u => string.IsNullOrEmpty(search) || 
                           u.UserName.Contains(search));

Before blindly implementing this, if you were to do so, please read Adam's answer to learn of consequences.