Conditional shortcuts in LinqToSql query

Question

Here's a little LinqToSql GOTCHA:

// Returns the number of counties in a state, 
// or all counties in the USA if the state is null
public static int CountCounties(State s) {
  var q = 
    from cy in County.GetTable() // my method to get the ITable
    where (s == null || s.Code == cy.StateCode) // shortcut OR operator, right...?
    select cy;
  return q.Count();
}

Guess what - if you pass a null State object to this method, you get a null reference exception! It seems that LinqToSql doesn't use the || shortcut operator as a shortcut!

Answer credit goes to whoever proposes the best explanation & workaround for this.

Answer 1

If it's linq to sql then remeber that Linq is just parsing your query into SQL.

It is therefore sending both of your where clauses to the database, hence the exception. I dont find this surprising really, though it is arguably wrong.

You will just have to do an independant check.

if (!string.isNullOrEmpty(state.statecode)
     q = q.where( s => s.code == state.statecode

Answer 2

This is not related to LINQ in general. In this case, the LINQ-to-SQL provider tries to parse the your lambda expression and make it a TSQL query. It cannot make too many assumptions based on your expression since it's trying to delegate most of the work to the database.

Long story short, the provider simply cannot translate it to SQL.