When is null is not a null? This is not a philosophical SQL question :-)

Answer: When it is still inside of Linq Expression


Linq operation is one of the rare occasion where C# semantics is not of WYSIWYG one.

var employees =
from x in db.Employees
where x.Manager == null
orderby x.LastName
select new { x.FirstName, x.LastName, x.Manager, ManagerName = x.Manager.LastName };
 
GridView1.DataSource = employees;
GridView1.DataBind();

Given the code above, even if x.Manager is null (or just look like one) the code x.Manager.LastName is not an absurd one. x.Manager == null has no bearing on C# nullability. As far as an ORM is concerned, x.Manager == null needed be translated to its equivalent relational syntax, i.e. that code needed be translated to table2.EmployeeId IS NULL. An ORM is able to do that, as the ORM's mappings contains info on what columns connect one table to another table. The x.Manager.LastName in Linq's select clause is also being translated by Linq Expression provider(e.g. ORM), that's why there won't be any null runtime error on x.Manager.LastName when the LastName is being accessed.


Continuing the code above, when using AsEnumerable this code's null..

var z =
from y in employees.AsEnumerable()
where y.Manager == null
select new { y.FirstName, y.LastName, y.Manager, ManagerName = y.Manager.LastName };

GridView1.DataSource = z;
GridView1.DataBind();


..is now a C#'s null. This code will have a null runtime exception on y.Manager.LastName then.



Perhaps to make code analysis a lot simpler, any code that is still in scope of Linq's Expression should be thought of not as a C#'s construct, and as such, are still subject for translation to whatever SQL syntax it needed be translated, C#'s language rules doesn't come into play when an Expression is still in scope. Linq Expression provider could even translate y.Manager == null to any kind of code; much like C++/C# operator overloading, a plus operator could mean minus, a minus could mean plus. And as far as Linq Expression provider is concerned, the code select new { y.FirstName, y.LastName, y.Manager, ManagerName = y.Manager.LastName } could be translated up to the Linq provider's liking and such can be translated to any kind of code, y.Lastname could be automatically translated to an expression that automatically lowercases, e.g. SELECT Lastname = LCASE(LastName), to even a random one :-)


C# rules could only take effect when things are materialized to objects already, e.g. when using AsEnumerable, ToArray, ToList.


This is the ideal Linq code for properties(e.g. FullName from concatenation in C#) in classes that are not present in database:

var employees =
    (from x in db.Employees
    where x.Manager == null
    orderby x.LastName
    select x) // code here and up are being translated to back-end equivalent, i.e. SQL
 
 
    // code here and below, be it Linq-style or Lambda-style, now follows the normal C# rules,
    // note the null coalesce (??) operator
    .AsEnumerable()
    .Select(y => new
        {
        y.FullName, // Lastname and Firstname is concatenated on C# getter
        ManagerName = (y.Manager ?? new NorthwindModel.Employee()).FullName
    });