> I do have concerns about strictly emulating the semantics of C# in the SQL
> query, as it seems that NHibernate is doing in at least that one case. To
> be fair, I haven't tested the latest code... I'm making the assumption the
> issue is applicable and that the same strategy has been applied elsewhere,
> as implied in a comment on the issue.
>
> To emulate C# you need something like (a is null or b is null) or (a is
not
> null and b is not null and a = b) in SQL Server. Naturally a and b could
> even be subqueries, making this a very long expression.
that's not going to work in all cases, so you can save yourself the
trouble. (cases like when null values are created due to left-joins,
aggregates without a value, expressions on fields which are null because of
a left join or aggregate which doesn't have a value etc. etc.)
> There are apparently performance issues with even the simple case
mentioned
> in the issue. Since in SQL, "x = y" is generally a different operation
from
> "x IS NOT DISTINCT FROM y", I believe it's expensive to hide this
> difference. I'd propose that to get "x = y"
> semantics, which are performant and usually work well, you'd use the ==
> operator in C#. (I can't comment on VB right now, not enough experience.
I
> am curious what the differences are though.) To get the "x IS NOT
DISTINCT
> FROM y" semantics, you'd use the
> object.Equals(x,y) method.
>
> Hope I didn't miss your point entirely Frans. I just thought the issue
was
> more related to the OO / SQL mismatch. Even if the equality operators in
VB
> are different, I'd assume that using different VB operators to generate
> different SQL would be reasonable.
Microsoft decided that a linq query on a DB should behave like a
query in memory (linq to objects). This isn't doable, and they know this
actually. You can get very far but as I said, you have to add such a
tremendous amount of SQL code to cover all the tracks in your query (as
obtaining a value can be a long list of projections which eventually results
in a null value, so you have to test everywhere, or always apply the slow
test logic, which they don't do), so it's not really a thing one should
invest a lot of time on. Instead, it's good that the developer knows that
the query is ran on the DB and thus that DB logic around NULL values apply:
- this makes the developer realize that in-memory constructs inside the
query will have a SQL equivalent or they'll fail
- the DB isn't a resource you can abstract away so you have to realize how
things behave in your DB: e.g. NULL values, are these sorted before or after
the real values? How much time is my app spending inside the DB? etc.
So a linq query on the DB behaves like a SQL query, the developer has to
live with that. It's silly to assume things should be the same because one
can also use the same query to query an in-memory data structure, but that's
not true: not all Linq elements have a 1:1 mapping onto SQL elements, and
thus things will be different.
> Ultimately it's a bit of a hack to work around the NULL semantic mismatch,
> but it's a hack that's been utilized by Microsoft and is known in the Linq
> world.
no it's not that known actually. Ask a developer if VB.NET treats
NULL values differently from C# and they won't know that, left alone how
Linq does it. Also, a lot of developers have problems with recognizing
in-memory constructs in a linq query and wonder why a query won't run on a
db.
> As another data point, how does your provider handle it Frans?
it treats null values like a DB would, so I don't append extra null
checks whatsoever: comparing to null results in a <expression/field> IS
[NOT] NULL construct.
If NHibernate does that today (which I assume it does), I'd leave it
as-is, it's not worth it.
FB
> I'll have to play with the code later and see exactly what it's doing to
be
> sure, just wanted to bring up this issue before it caused "breaking
change"
> trouble in the future.
>
> Patrick Earl
>
> On Wed, Oct 27, 2010 at 12:51 AM, Frans Bouma <[email protected]> wrote:
> > Doesn't NHibernate treat null comparisons as the SQL equivalent?
> >
> > Linq to Sql makes an attempt to mimic the differences between
> > VB.NET's null comparisons and C#'s which differ, however this isn't
> > always working because the outcome of a subquery for example is
> > unknown (or an aggregate returns null etc.)
> >
> > FB
