Friday, August 3, 2007

Null semantics in LINQ to SQL

The fun with Orcas Beta2 continues! Other than some incompatibility problems with ComponentArt's AJAX controls (which we're expecting a true fix for any day now), all the big showstoppers have been worked around thus far.

Hit a new snag today- not a showstopper, but a change from B1 behavior for sure. Awhile back, Dinesh was talking about the null impedance mismatch between SQL and C# and how the LINQ to SQL team was proposing handling it. Basically, C# null semantics ruled, so LINQ to SQL was consistent with all the other LINQ types (in other words, null == null). That proposal stuck until Beta2, where it appears they've flip-flopped back to SQL null semantics (null != null, or anything else for that matter). I've started an MSDN forum discussion on it as well.

I can definitely understand both sides of this argument. That said, the change makes life a little harder. The following worked in Beta1 (when the "filter" arg was null, rows where "Name" is null came back):

public IList GetFoos(string filter)
{
var query = from j in jdc.ATable where j.Name == filter select j;

return query.ToList();
}


This query doesn't work in Beta2- since SQL null doesn't equal anything (including null), I get no rows back. The following hack restores the correct behavior in Beta2:

var query = from j in jdc.ATables where filter == null ? j.Name == null : j.Name == filter select j;

This works, but is a little verbose on the C# side (especially when I have lots of potentially null filters on a query), and generates SQL that is also verbose. Instead of generating an IS NULL statement when filter is null, it generates an inline CASE statement in the query. Works, but probably not ideal.

What I'd really like to do is wrap the comparison in a little expression generator that would result in an IS NULL getting generated on the SQL side. Something like:

var query = from j in jdc.ATable where CheckNullCompare(j.Name == filter) select j;

I might try to get to this over the weekend- I'm still not terribly clear on how I'd mix this, since it is sort of a mix of runtime values and expressions. More to come...

2 comments:

Anonymous said...

Hi Matt,

I have a problem with nullable values and LINQ. Take a look at the code below:

var query = from person in db.Persons where person.Name == null
select person;

Here, Person is a class and Name is a string (which can be, of course, null).

This query generates an error:
The member 'Project1.Person.Name' has no supported translation to SQL.

Any ideas?

Thanks.

Matt Davis said...

Usually this happens when your Name field is set up as a property. The SQL generator has to be able to translate everything you do in that prop-get to SQL, and it's not terribly smart in that department, so it usually won't try. If you're trying to build partial class behavior on the Name property, you should find another place to hang it.