Mark mailed to ask;

Mike, I enjoyed your short webcast entitled Linq to SQL: Nulls.

I do have one question.

Here is a snippet of your code from your webcast

var query = from e in ctx.Employees

                        where e.Region == null

                        select e;

If I wanted to change this to use a nullable parameter to compare against region, how would I go about doing that

I tried the following

int? intRegion = null;

var query = from e in ctx.Employees

                        where e.Region == intRegion

                        select e;

this does not seem to work as expected.  I would think that linq would be smart enough to recognize that intRegion is a nullable type whose value is, in fact, null and convert that to sql (where Region IS NULL).  Linq does not seem to operate that way in this case.

I was able to write what I consider to be a hack to accomplish the correct SQL

where (e.Region == null && intRegion == null) || (e.Region == intRegion && intRegion != null)

This seems to be a lot of work to handle a nullable parameter.

Can you clarify how to go about using nullable parameters in Linq to SQL?

I'll admit that it is a bit of a weird one and I'm not 100% sure that I have the answer right but I've a sneaking suspicion that I asked about this a while back and that it's to do with preserving the way that a database treats NULLs because, in the database (at least with ANSI_NULLS ON (the default)), nothing ever equals NULL.

So, if we write a bit of code such as;

int? reportsTo  = null;

      using (NorthwindDataContext ctx = new NorthwindDataContext())
      {
        var query = from e in ctx.Employees
                    where e.ReportsTo == reportsTo
                    select e;

        Console.WriteLine("Number of rows returned {0}", query.Count());
      }

Then you get a result of 0 and, if you ran the Profiler against this, you'd see that the SQL that's being generated is doing;

exec sp_executesql N'SELECT COUNT(*) AS [value]
FROM [dbo].[Employees] AS [t0]
WHERE [t0].[ReportsTo] = @p0',N'@p0 int',@p0=NULL

so it's looking for equality with the value that we pass in rather than translating it into an "IS NULL". Now, I guess that the person who wrote the LINQ to SQL bits could have tried to check if the variable that we're using above "reportsTo" is Nullable or not and could have tried to replace what they've done with "IS NULL" if they'd wanted to but I guess they're concerned about something like this happening;

using (NorthwindDataContext ctx = new NorthwindDataContext())
      {
        var query = from e in ctx.Employees
                    where e.FirstName == "Andrew" && e.LastName == "Fuller"
                    select e.ReportsTo;

        var result = query.First();

        var secondQuery = from e in ctx.Employees
                    where e.ReportsTo == result
                    select e;

        foreach (var v in secondQuery)
        {
          Console.WriteLine("{0} {1}", v.FirstName, v.LastName);
        }
      }

So, here, we pull out the value of ReportsTo for "Andrew Fuller" and his ReportsTo value is NULL. We stick this into the result variable and we then use it to query the DB for any employees who have that ReportsTo value (i.e. NULL). So...the question is whether the DB should return the row here or not?

If we did this in SQL...

declare @result integer

select @result = reportsTo from employees where firstname='Andrew' and lastName='Fuller'

select firstname, lastname from employees where reportsTo=@result

Then we wouldn't get a result-set with ANSI_NULLS set to ON and nor do we get a result-set with LINQ to SQL.

Having said all that, I do find it a bit different that the explicit use of the keyword null appears to be special-cased so that;

using (NorthwindDataContext ctx = new NorthwindDataContext())
      {
        var query = from e in ctx.Employees
                    where e.ReportsTo == null
                    select e;

        foreach (var v in query)
        {
          Console.WriteLine("{0} {1}", v.FirstName, v.LastName);
        }
      }

will actually print out a result-set because of what seems like special-casing for "== null". Assuming that what I've written is right (which is always a big assumption) then I think I'd have preferred not to have this binding to the "== null" syntax and perhaps had a IsNull function or something like that to make it a little more explicit as to what we're doing.