Richard wrote:
> Just to clarify, I meant I expect (or am not at all surprised that):
>
> .Where(u => u.UserName == nullVar)
>
> ... translates to:
>
> session
> .CreateQuery("from User u where u.UserName = :name")
> .SetString("name", nullVar)
>
> ... which in turn generates the SQL:
>
> WHERE u.UserName = @p0
> /* with @p0 = NULL */
Really?
My colleagues (using Linq2SQL) checked that
....Where(u => u.UserName == null);
really returns all "u" where the UserName is null - i.e. transformation to "IS
NULL" works; then wrote code
... FindWithUserName(string userName) {
....Where(u => u.UserName == nullVar);
}
then tested it in-memory with NUnit with both string and null values; then used
it in the application - and searched for a few hours why they did not get back
the same result as in their tests. That you have to write
if (userName == null) {
....Where(u => u.UserName == null);
} else {
....Where(u => u.UserName == userName);
}
(not even a ?: inside the Where worked) was surprising. Also, ReSharper lets
you happily inline or "outline" the null ... as almost anyone (<= that's me, of
course) would expect.
Fortunately, NHibernate behaves "as expected by almost anyone ... ahem, by me"!
On a more general level: As "boundary cases", there seem to be two "schools of
thought" for Linq SQL providers (I might exaggerate a little):
* The "Let's-SQLers" believes Linq to be a sort of "macro language to control
SQL". For them, every operator is mainly (only?) interpreted as an SQL
operator. It seems they would even go so far to redefine C# operators to
"control" the resulting SQL. They miss < and > and the like keyword for strings
and redefine the behavior for .Any to always be an inner join. They even know
(or "know") which variables and constants are outer-parameterized and which are
not and therefore can control remotely the DB planning performance of a
statement via Linq!
* The "Linq purists" believe that the following statements should *not* return
a result:
obj.S = "abc "; // with trailing blank
...Where(obj => obj.S == "abc"); // without trailing blank
For them, checking every- and anything against Linq2Objects is a must; they
work test-driven in-memory and then complain when the database finds or does
not find what their unit tests imply. They even know that && could be
overloaded for a type so that it still returns bool - and then would be in
heaven if (or when?) the Linq SQL provider will *not* create an SQL AND, but a
call to some stored function implementing their && operator!
I confess I tend to the latter belief; but I must accept that the code example
as well as the overloading horror I described will not work on a real database:
Trailing whitespace *is* ignored in SQL; and bool && *is* the SQL AND operator;
period. Finding that "middle ground" is work ... and heated discussions.
Regards - all the best!
Harald
--
GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit
gratis Handy-Flat! http://portal.gmx.net/de/go/dsl
