DefaultIfEmpty outer join support for NH3
pvginkel
wondering when support for DefaultIfEmpty for outer joins is going to
be integrated.
I see that NHibernate/Linq/GroupJoin/
NonAggregatingGroupJoinRewriter.cs did not receive any updates for the
past nine months and it specifically does not support DefaultIfEmpty
(NotSupportedException). Also, the DLinqJoin7 test case in /
NHibernate.Test/Linq/LinqQuerySamples.cs which tests this construct is
set to [Ignore] and I cannot find a JIRA issue on this.
Frans Bouma
he remarked that group-join isn't supported yet, and he doesn't know whether
it will be in RTM.
DefaultIfEmpty and groupjoin are a massive undertaking, although it seems
they are easy at first. It's a major pain in the you-know-where to support
these, especially in the more advanced scenarios. The main gripe is that a
groupjoin's seen as 1 side of another join and acts like it and
defaultifempty fulfills the role of the other side of the join and refers to
the other side of the groupjoin. So this is _2_ joins which should be _1_.
It's also a nightmare, alias-wise, as defaultifempty is seen as a set, but
as it refers to a part of a groupjoin elsewhere in the tree, you have to
COPY the side of the groupjoin and re-alias the thing, also re-aliasing the
parts which refer to THAT. Not sure if the re-linq people have solved this
in re-linq.
I.o.w.: the person who will pick this up and implement it, has to love S&M
and has to have a chronical sleep disorder as you wont be having a lot of
free time to do anything else ;)
FB
Fabio Maulo
I don't know if Steve's still reading this list,
Fabio Maulo
Fabio Maulo
Fabio Maulo
Fabio Maulo
On Thu, Aug 19, 2010 at 7:20 AM, Frans Bouma <fr...@sd.nl> wrote:
I don't know if Steve's still reading this list,
Sir Strong is on holidays scratching his b... in some nice beach under the sun
--
Fabio Maulo
Frans Bouma
>
> I don't know if Steve's still reading this list,
>
> What do you mean ?
Well, I asked in some other linq thread a couple of questions to
Steve, gave info about how to do things, but never got a reply (was in June
or so), also all other linq related material is not answered by him, so I
wondered if he was still reading this list. I don't know how else to
interpret this remark, to be honest.
But that aside, sorry I tried to be helpful, mr. Maulo.
FB
Fabio Maulo
Fabio Maulo
Frans Bouma
I tried to be helpful in EVERY email here and on nhusers, and I
didn't want to imply *anything* in the 1st sentence I wrote. WTF is your
problem?
> What we have notice is that there is a kind of "personal questions" when
we
> are talking about our linq-provider and we don't have a "personalized
> customer service" (at least not for free).
I have no idea what you mean/refer to.
> Steve is part of NH's team and the Linq provider is part of NHibernate
core.
> As any other issue, even the issues related to linq-provider are solved by
> the team.
>
> Perhaps, with his silence, Steve and the team altogether are trying to say
> the same.
Sorry but I have no idea what you mean, but anyway, it's been
enough. Every f*cking time 'Linq' is mentioned and I reply in some form or
another, it ends up with you talking me down in some form or the other. I
just try to be helpful, but I don't need this kind of bickering. If helping
out time after time ends up with some form of bickering, I won't spend my
time on it anymore. Like I've said to Steve before, he can email me any time
with questions about Linq and how to do things, but I won't reply to linq
crap here again, as apparently you don't want my help/information about how
to deal with linq expressions and what to expect. In case you don't know,
mr. Maulo, that info is no-where to be found on the net nor in MS'
documentation. Only the small group on this planet who have dealt with these
miserable issues before know what to expect and where a solution 'might' be
found (as the only team who managed to get it all right is the Linq to Sql
team (now abandoned) and they didn't publish any of their info).
FB
Fabio Maulo
Fabio Maulo
Frans Bouma
other"
> I don't know what you are talking about.
To help you, think REALLY HARD about the following:
Why did you reply with 3 emails to my post, only replying on my first
sentence, as if I would imply anything with it instead of exactly what I
said. Why did you think that of me?
For the record, the only one who replied with any real info to the
topicstarter was... who? I really don't know why you had to do this, I
really don't.
FB
Fabio Maulo
Fabio Maulo
pvginkel
subject, I was wondering whether you agree this is a crucial feature
of the Linq provider. Maybe I'm missing the obvious, but I do not see
a different way to get outer joins to work via Linq. I would offer to
pick this up would it not be that the initial reply kind of scared me
away :).
In short, can we expect to get outer joins via Linq working in the GA?
Fabio Maulo
Fabio Maulo
Steve Strong
Frans Bouma
> I suspect a naive approach won't be too hard, and will probably nail a
large
> %age of use-cases. A full implementation will be, as suggested by Frans,
a
> serious undertaking and I suspect isn't worth the effort. However, as an
> unpaid volunteer, I'm not going to commit to a date for even the naive
> implementation :) I will try my best though!
It might be simpler with re-linq's front end. I was trying it out on
saturday, and I saw this:
query:
[Test]
public void RelinqTest1()
{
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
LinqMetaData metaData = new LinqMetaData(adapter);
var q = from reason in metaData.Reason
join time in
(
from timeHeader in
metaData.NonPresentTimeHeader
where timeHeader.Id == 6
join time in
metaData.NonPresentTime on timeHeader.Id equals time.HeaderId into timeJoin
from joinedTime in
timeJoin.DefaultIfEmpty()
select joinedTime
) on reason.Id equals time.ReasonId
into reasonJoin
from joinedReason in
reasonJoin.DefaultIfEmpty()
select new
{
ReasonID = reason.Id,
Reason = reason.Reason,
HeaderID = joinedReason.HeaderId ??
-1,
TimeID = joinedReason.Id,
Notes = joinedReason.Notes,
DateStart = joinedReason.DateStart,
DateEnd = joinedReason.DateEnd
};
var parser = new QueryParser(new
ExpressionTreeParser(MethodCallExpressionNodeTypeRegistry.CreateDefault()));
var query = parser.GetParsedQuery(q.Expression);
Console.WriteLine(query.ToString());
}
}
Front-end produces:
from ReasonEntity reason in
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[AdventureWorks.Dal.Ada
pter.EntityClasses.ReasonEntity])
join NonPresentTimeEntity time in
{
from NonPresentTimeHeaderEntity timeHeader in
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[AdventureWorks.Dal.Ada
pter.EntityClasses.NonPresentTimeHeaderEntity])
where ([timeHeader].Id = 6)
join NonPresentTimeEntity time in
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[AdventureWorks.Dal.Ada
pter.EntityClasses.NonPresentTimeEntity])
on Convert([timeHeader].Id) equals [time].HeaderId into
IEnumerable`1 timeJoin
from NonPresentTimeEntity joinedTime in
{
[timeJoin] => DefaultIfEmpty()
}
select [joinedTime]
}
on Convert([reason].Id) equals [time].ReasonId into IEnumerable`1
reasonJoin from NonPresentTimeEntity joinedReason in {
[reasonJoin] => DefaultIfEmpty()
}
select new <>f__AnonymousType101`7
(
ReasonID = [reason].Id,
Reason = [reason].Reason,
HeaderID = ([joinedReason].HeaderId ?? -1),
TimeID = [joinedReason].Id,
Notes = [joinedReason].Notes,
DateStart = [joinedReason].DateStart,
DateEnd = [joinedReason].DateEnd
)
Now, as you can see in this snippet:
from NonPresentTimeEntity joinedTime in
{
[timeJoin] => DefaultIfEmpty()
}
the defaultifempty is simply referring to the groupjoin object. What might
be a solution is to add a 'jointype' to the groupjoin class of re-linq and
switch it to 'left join' when you handle the p=>DefaultIfEmpty() construct
of a re-linq tree.
At least that's what I thought of when looking at it. BUt I might overlook
things, haven't looked too deeply into re-linq.
FB
Wenig, Stefan
> where timeHeader.Id == 6
> join time in metaData.NonPresentTime on timeHeader.Id equals time.HeaderId into timeJoin
> from joinedTime in timeJoin.DefaultIfEmpty()
> select joinedTime
> What might be a solution is to add a 'jointype' to the groupjoin class of re-linq
> and switch it to 'left join' when you handle the p=>DefaultIfEmpty()
> construct of a re-linq tree.
Hi Frans,
we could transform simple left-join-via-DefaultIfEmpty to left join clauses in re-linq's QueryModel, but that would be a very simplistic solution that could not support any but the most straightforward LINQ clauses. (i.e. only those where someone implements left join as they found out via Google, but for instance not any scenario where the query further references the intermediate join clause (timeJoin in your sample).
We think that DefaultIfEmpty is best solved in each back-end individually, but of course nothing stops anyone from extending the front-end to create that kind of QueryModel via an optional transformation step. Just be aware that this only takes you so far, it would probably result in the LINQ provider rejecting every use of DefaultIfEmtpy in joins that cannot be reduced to that pattern.
Cheers,
Stefan
Frans Bouma
> > == 6 join time in metaData.NonPresentTime on timeHeader.Id equals
> > time.HeaderId into timeJoin from joinedTime in
> > timeJoin.DefaultIfEmpty() select joinedTime
>
> > What might be a solution is to add a 'jointype' to the groupjoin class
> > of re-linq and switch it to 'left join' when you handle the
> > p=>DefaultIfEmpty() construct of a re-linq tree.
>
> Hi Frans,
>
> we could transform simple left-join-via-DefaultIfEmpty to left join
clauses
> in re-linq's QueryModel, but that would be a very simplistic solution that
> could not support any but the most straightforward LINQ clauses. (i.e.
only
> those where someone implements left join as they found out via Google, but
> for instance not any scenario where the query further references the
> intermediate join clause (timeJoin in your sample).
A simple, stupid, but illustrative example: ('ctxt' is
session.Linq... )
var q = from c in ctxt.Order
join o in ctxt.Order on c.CustomerId equals o.Customer.CustomerId
Into co // A
from x in co.DefaultIfEmpty() //B
select c;
Lines A and B form a SelectMany, due to the from in B. At the left side,
you'll have the group join in A and on the right side you have the right
side of the group join in A.
This can be transformed into keeping A, and using B to adjust the group
join. The main advantage is that the group join contains the selectors and
in C#'s case also the projection.
So if re-linq could transform this into (pseudo code!)
var q = from c in ctxt.Order
(left) join o in ctxt.Order on c.CustomerId equals
o.Customer.CustomerId Into co select c;
it would be a big win. Big problem is the code which references the
DefaultIfEmpty result as that has to be changed to references to the
right-side of the join.
It would help a lot, because it would make a join handler in a linq provider
much easier, in fact, an existing join handler (which can handle the normal,
'join') would likely already work.
> We think that DefaultIfEmpty is best solved in each back-end individually,
> but of course nothing stops anyone from extending the front-end to create
> that kind of QueryModel via an optional transformation step. Just be aware
> that this only takes you so far, it would probably result in the LINQ
> provider rejecting every use of DefaultIfEmtpy in joins that cannot be
> reduced to that pattern.
You mean:
var q = from c in ctxt.Order
from o in c.Orders.DefaultIfEmpty()
select c;
?
FB
Fabian Schmied
We try to keep the re-linq front-end as general as possible. The fact
that DefaultIfEmpty causes a GroupJoin to turn into a left-outer join
in your example is true, but it's not as simple generally speaking.
Consider the following slightly modified version of your query:
var q = from c in ctxt.Order
join o in ctxt.Order on c.CustomerId equals
o.Customer.CustomerId
select c;
Here, B uses the group join in a left-outer join fashion, but C uses
it in an ordinary cross join fashion. The simplification you suggested
(keeping A and flagging it as a left join) won't work here. And my
opinion is that if we can't apply it generally, we shouldn't apply it
at all. (As a predefined transformation in the re-linq front-end, that
is. Any LINQ provider can of course choose to build this
simplification if it helps.)
In addition, as Stefan said, DefaultIfEmpty can be applied to any
arbitrary sequence in a query, not only group joins. A LINQ provider
striving to support DefaultIfEmpty should try to find a solution that
works in all cases, no matter where the DefaultIfEmpty operator is
applied.
I've written a blog post about how I'd handle the DefaultIfEmpty query
operator in a SQL-translating LINQ provider here:
https://www.re-motion.org/blogs/mix/archive/2010/09/04/handling-the-defaultifempty-result-operator-in-a-re-linq-based-provider.aspx
. This describes more or less how we deal with DefaultIfEmpty in re-
linq's SQL backend, and I think you'll agree that with an approach
such as the one explained in the blog post, DefaultIfEmpty isn't any
longer difficult to implement.
Cheers,
Fabian
>
> - Show quoted text -
Frans Bouma
> DefaultIfEmpty causes a GroupJoin to turn into a left-outer join in your
> example is true, but it's not as simple generally speaking.
> Consider the following slightly modified version of your query:
>
> var q = from c in ctxt.Order
> join o in ctxt.Order on c.CustomerId equals o.Customer.CustomerId
> into co // A
> from x in co.DefaultIfEmpty() //B
> from y in co //C
> select c;
>
> Here, B uses the group join in a left-outer join fashion, but C uses it in
> an ordinary cross join fashion.
Is that really the case? 'y' is an order, same as 'x', but 'co' is
the same set as when the DefaultIfEmpty() call is made.
I do understand why you're saying it, from the perspective of
sequences, it makes sense, however from the perspective of sets and SQL, I
don't really know how to formulate the query above, because one has to join
co again with... co ? (which can't be done).
At the moment I follow the same steps, but it's a cumbersome path
IMHO, where if you look at 'intent', it can be made simpler, IMHO. But I
understand why adding this to the front end might make situations like the
one above harder to deal with (although I'm interested in what you'd think
the query above does, how it looks in SQL :))
> The simplification you suggested (keeping A
> and flagging it as a left join) won't work here. And my opinion is that if
> we can't apply it generally, we shouldn't apply it at all. (As a
predefined
> transformation in the re-linq front-end, that is. Any LINQ provider can of
> course choose to build this simplification if it helps.)
IMHO the sole purpose of 'DefaultIfEmpty' is to use a sequence which
can contain nulls due to a left join, and in fact, it's the only mechanism
to force a left join into the final query (navigator traversal might result
in left joins due to nullable FK fields, but that's not enforceable) using a
'join' operator or a navigator traversal which would otherwise result in an
inner join.
I.o.w.: if you see a DefaultIfEmpty, the sequence it is called on is
a result of a left join, and you can treat that sequence as such. Dealing
with it 'on the fly' otherwise is a very cumbersome task as it's 'too late':
either group joins are already handled in some trees or other clauses refer
to the defaultifempty sequence and do that by its own alias (and not the
alias of the side the defaultifempty refers to)
> In addition, as Stefan said, DefaultIfEmpty can be applied to any
arbitrary
> sequence in a query, not only group joins. A LINQ provider striving to
> support DefaultIfEmpty should try to find a solution that works in all
> cases, no matter where the DefaultIfEmpty operator is applied.
yes, that's true. The second query I gave is indeed an example of
that. Though is it really a matter of 'a lot of cases' btw? Aren't there
just 2: 1) a group join result used with DefaultIfEMpty -> make groupjoin a
left join and 2) the random sequence on which DefaultIfEmpty() is called,
which also would result in a left join? (x=> c.Orders.DefaultIfEmpty())
The part I'm struggling with is the query you gave above, which to
me is unspecifyable in SQL, or at least I don't really see the 'intent' of
what such a construct might be. Could you elaborate on that a bit please?
> I've written a blog post about how I'd handle the DefaultIfEmpty query
> operator in a SQL-translating LINQ provider here:
> https://www.re-motion.org/blogs/mix/archive/2010/09/04/handling-the-
> defaultifempty-result-operator-in-a-re-linq-based-provider.aspx
> . This describes more or less how we deal with DefaultIfEmpty in re-
linq's
> SQL backend, and I think you'll agree that with an approach such as the
one
> explained in the blog post, DefaultIfEmpty isn't any longer difficult to
> implement.
.. except that you have a tremendous amount of work ahead of you:
optimize the query you're producing :). This is rather difficult to do
though, Linq to SQL (the best linq provider out there still) uses a lot of
visitors for this, which can take significant amount of time during query
production.
I do like the approach though, it makes things easier on the
expression tree front, at the expense of optimizing it later (something the
EF linq provider also does) with the assumption that optimizing queries is
easier. It's at least more straight forward! ;)
What I learned from the demoscene so many years ago though, was that
if you can do things up front, it will save you a lot of time later on.
I.o.w.: if you run into a defaultifempty, you can also think: why is it
there? My point is then: what if the answer to that question is: "to make a
join a left join"? This info is then usable to rework the tree a bit to
reflect that info so the expression tree handling is easier and you don't
need optimization afterwards. The question of course is: is it indeed only
there for that purpose or not? (in the context of database targeting
queries, as not all IQueryable operators make sense in every location of
such a query when you see them in the context of db targeting queries).
I have no clear answer to that. THe query you gave above as an
example clearly proves me wrong, so we can look into other ways perhaps to
deal with this, but on the other hand, the query construct also might not
make much sense in the context of a db query, which may mitigate the
argument. IF we can find evidence DefaultIfEmpty is there for making joins
left joins, things will get much easier to implement IMHO (including group
join).
Disclaimer: I haven't looked at these constructs for quite a while,
(read: more 1.5 years) so I might overlook an important aspect.
FB
Fabian Schmied
I think we're hijacking thist thread for discussions about re-linq;
therefore, I'm moving my answer to the re-motion users list:
http://groups.google.com/group/re-motion-users/t/3062c49267adfa5f .
Cheers,
Fabian