LINQ query checks for null

Question

I have a userList, some users don't have a name (null). If I run the first LINQ query, I got an error saying "object reference not set to an instance of an object" error.

var temp = (from a in userList
            where ((a.name == "john") && (a.name != null))
            select a).ToList();

However, if I switch the order by putting the checking for null in front, then it works without throwing any error:

var temp = (from a in userList
            where ((a.name != null) && (a.name == "john"))
            select a).ToList();

Why is that? If that's pure C# code (not LINQ), I think both would be the same. I don't have SQL profiler, I am just curious what will be the difference when they are being translated on SQL level.

Answer 1

In C# the && operator is short-circuiting so if the first condition returns false, the second condition is not executed at all. From MSDN:

The conditional-AND operator (&&) performs a logical-AND of its bool operands, but only evaluates its second operand if necessary.

The || operator behaves in a similar way, except that it doesn't evaluate its second argument if the first returns true.

---

I don't think this is the full story though. The rest of my post covers the following points:

• You can log the SQL statements using DataContext.Log.
• Your query shouldn't generate an error no matter which way round you write it.
• There are differences in behaviour between LINQ to objects and LINQ to SQL.
• Your filtering might be executing locally instead of in the database.

You can easily view the generated SQL in Visual Studio without needing a SQL profiler. You can hover your mouse over a LINQ to SQL query object and it will display the SQL. Or you can use the DataContext.Log to log the SQL statements, for example like this:

TextWriter textWriter = new StringWriter();
using (var dc = new UserDataContext())
{
    dc.Log = textWriter;
    var userList = dc.Users;
    var temp = (from a in userList
                where (a.Name.ToString() == "john") && (a.Name != null)
                select a).ToList();
}
string log = textWriter.ToString();

You can also log to a file or even to Console.Out:

dc.Log = Console.Out;

Doing this you can see that the query looks something like this, although you will likely have more columns in the select list:

SELECT [t0].[Name]
FROM [dbo].[User] AS [t0]
WHERE ([t0].[Name] = @p0) AND ([t0].[Name] IS NOT NULL)

Another point is that your query should not generate an error. Even if a.name is null, a == "john" should still work - it will just return false.

Lastly, there is a difference between how C# normally works and how LINQ to SQL works. You shouldn't get a null exception from the database. To demonstrate this I will make a small modification to your query - adding a ToString after a.Name:

var temp = (from a in userList
            where (a.Name.ToString() == "john") && (a.Name != null)
            select a).ToList();

Now this fails for Linq to Objects with a NullReferenceException, but it works with LINQ to SQL without throwing an exception. So I suspect that you have loaded all items from the database into memory and are filtering locally. In other words maybe you have something like this:

var userList = dc.Users.ToList();

instead of the following which would allow the database to do the filtering:

var userList = dc.Users;

So I suspect there is more to this question than meets the eye. Perhaps you can provide more details.

Answer 2

Regarding the question how does this translate the SQL - I think that SQL has the same short-circuiting semantics, so the SQL translator simply preserves the order of conditions in the generated query.

For example the following two LINQ clauses:

where p.CategoryID != null && p.CategoryID.Value > 1
where p.CategoryID.Value > 1 && p.CategoryID != null

Translet to the following two SQL clauses:

WHERE ([t0].[CategoryID] IS NOT NULL) AND (([t0].[CategoryID]) > @p0)
WHERE (([t0].[CategoryID]) > @p0) AND ([t0].[CategoryID] IS NOT NULL)