> I've been pondering this thread over on
> nh-usershttp://groups.google.com/group/nhusers/browse_thread/thread/1ff37a36e...
>
> To save you reading it all, the guy is try to do this:
>
> Session.Query<Project>().OrderBy(p.Type.Label).ToList()
>
> but due to the reference to p.Type there is a join being inserted, which
> means that there is some un-intentional filtering going on (he doesn't
> receive any results where p.Type is null).
>
> This is analogous to hitting a child reference within a select, for example
>
> Session.Query<Project>().Select(p.Type.Label).ToList()
>
> In this case, the provider (correctly, imo) generates outer joins to ensure
> that filtering is not taking place. In my view, filtering of the results
> should only happen if there is an explicit join or where clause.
You should do the following:
- FKside LEFT JOIN PKside, when at least 1 of the FK fields is
nullable
- FKside INNER JOIN PKside when no Fk field is nullable.
One could argue to always do an FKSide LEFT JOIN PKside, even if the
fk side has no nullable fk fields, though it might be an RDBMS can
optimize the INNER JOIN better than the LEFT JOIN. Linq to SQL (and
also my linq provider) uses this left join / inner join system.
What I did was in any expression I detect on my own in the expression
tree, a property collects the relationships navigated. So the lambda
p=>p.Type.Label, results in a Field Expression which contains in its
AdditionalRelationships property the relationship navigated by the
navigator 'Type'. The OrderBy (which returns a query) then appends the
additional relationships gathered to its body's relationships with the
proper left/inner statement. Similar in where and other extension
methods which result in a query.
There are a couple of miserable situations you've to deal with. I'll
list two examples below.
1) duplicate relationships with different join characteristics.
var q = from c in metaData.Customer // A
from o in c.Orders // B
....
here, the multiple from clauses form a cross-join between customer and
order. However, the line B contains the _same_ relationship with
either an INNER join (if o.CustomerId is not nullable) or a LEFT join
towards the FK side (order) if o.CustomerID is nullable). You have to
pick the second one over the first one, so actually ignore the
navigator originating relationship and use its join characteristics to
adjust the cross-join
2) where / other derived table creating method inside navigation
clause
var q = from c in metaData.Customer
from o in c.Orders.Where(o=>o.OrderDetails.Count() > 10)
...
Here, the join is different, as the 'Where' clause creates a derived
table / subquery on Orders, however it's correlated with the
customer.
The trick is to create a visitor which removes these where clauses
(and order by etc.) from join sides. This gives a lot of headaches in
the alias/scope department but it also makes it easier to deal with
complex joins.
> So I'm proposing that I process OrderBy clauses in the same way as I handle
> Select, and introduce outer joins as appropriate - this is technically a
> breaking change, hence the post here to see if you all agree.
There's a problem with relationships navigated in the projection: it's
sometimes cumbersome to determine whether a scalar query is meant or a
member navigation. (like messy queries with select(x=>new
{x.Foo.Bar.First().SomeNavigator.FieldName, ....}). This example for
example requires that from 'x' everything is a scalar, so
Foo.Bar.First() all have to be in the scalar query, which is embedded
in the projection. However you find that out when you run into
'First()', likely completely elsewhere than where you evaluate the
memberexpressions for Foo and Bar. So it's not always that simple ;)
Another thing which will pop up in the above example is that if you
assign an alias to Foo and Bar, and the developer placed a second time
the whole x.Foo.Bar line in the projection (this happens, trust
me ;)), you need to assign different aliases. The problem is however
that you don't always want to do that:
.Select(x=>new { x.Customer.CompanyName, x.Customer.CustomerId, ...}),
here you want to have 1 join with Customer and assign it 1 alias.
FB
