NH-2379 - Add support of Left Joins to Linq Provider

[nhibernate-bot]

Alex Baker created an issue — 13th October 2010, 13:59:50:

The attached sample identifies a number of issues in the LINQ provider related to the use of the "Join" expression. Specifically when performing outer joins or pre filtering the result set to be join in the join.

for example

select *
from table 1 a
left join table 2 b
on a.id = b.id
and b.start_date < getdate()

---

Patrick Earl added a comment — 19th January 2011, 4:11:22:

Hi Alex. Could you improve the test cases so they check something other than the fact that the query basically parses? It would be good to know if they're returning expected data.

---

kkozmic added a comment — 8th January 2012, 1:12:46:

I created some tests for this underlying issue (not basd on Alex's tests).
https://github.com/kkozmic/nhibernate-core-testcase/tree/NH-2379

---

Alexander Zaytsev added a comment — 10th February 2012, 2:28:41:

I've implemented detection of almost left joins expressions in linq, but I've issues to convert it into HQL, in case that HQL only supports joins with linked entities by FK-PK pair.

Left join could be implemented with inner join and union, but unfortunatelly HQL does not support union statements.

And so we can support only these kind of left joins:

            var query = from parent in session.Query<Parent>()
                        from child in parent.Children.DefaultIfEmpty()
                        select new {parentId = parent.Id, childId = (int?) child.Id};

---

Alexander Zaytsev added a comment — 14th February 2012, 16:17:03:

I need anyones attention

---

Alexander Zaytsev added a comment — 1st May 2012, 15:36:53:

Krzysztof, your query in test "canget_parents_with_no_children_or_children_born_before2000" should be rewritten as following:

var query = from parent in session.Query()
where parent.Children.Any() == false |
|
parent.Children.Any(c => c.DateOfBirth < new DateTime(2000, 1, 1))
select parent;

Because as per documentation "NHibernate does not distinguish between a null collection reference and an empty collection." (NH reference point 6.1)

---

Alexander Zaytsev added a comment — 5th June 2012, 11:24:08:

Commited to master support for left join via navigation properties:

var query = from parent in session.Query()
from child in parent.Children.DefaultIfEmpty()
select new {parentId = parent.Id, childId = (int?) child.Id};

Commit: 35f3f20

---

Luka Cetina added a comment — 6th June 2012, 20:24:54:

Great work Alexander.
Will this be supported too:

var query = from p in session.Query()
join pa in session.Query() on p.Id equals pa.PersonId into tempAddresses
from addresses in tempAddresses.DefaultIfEmpty()
select new { p.FirstName, p.LastName, addresses.State });

Thanks

---

Alexander Zaytsev added a comment — 7th June 2012, 7:03:48:

Luka Cetina, no.

Because HQL does support left joins only through navigation properties. Left join not through navigation property in HQL could be emulated by cross join and union, but HQL does not support unions.

---

Ventsislav Mladenov added a comment — 11th February 2013, 15:14:47:

Hi, LeftJoinRewriter.cs is added 8 months ago but still does not exist in latest release of Nhibernate. What is the schedule for this feature ?

---

Daniel Laberge added a comment — 7th May 2013, 16:21:04:

I am also badly in need of DefaultIfEmpty()... I'm tempted to try and backport your commit (35f3f20) into the last released version and hope for the best.

---

Jonatas Hudler added a comment — 21st May 2013, 22:04:43:

Thinking of Daniel's comment just before, isn't there any way to add this funcionality as an "add-in" to the NHibernate Configurantion while we wait this feature to be integrated to the trunk?

I mean, create a custom QueryVisitor class overriding the base one with this funcionality and adding it as the "default Linq Interceptor" in the configs?

Is this even possible?

---

Jonatas Hudler added a comment — 21st May 2013, 22:08:19:

Or maybe adapt this feature as sort of 'LinqToHqlGeneratorsRegistry' Linq extension?

---

Alexander Zaytsev added a comment — 21st May 2013, 23:48:24:

Yes, I thought about having a custom LINQ provider. But it requires time to build and support.

---

Daniel Laberge added a comment — 22nd May 2013, 12:54:08:

I have a fork where I've merged support for DefaultIfEmpty() into 3.3.x as well as added support for GroupBy combined with DefaultIfEmpty (NH-3459).

If anyone is interested, I'll tidy up and publish my changes.

---

Jonatas Hudler added a comment — 22nd May 2013, 13:14:23:

That would be great. I would really appreciate that :)

---

Daniel Laberge added a comment — 22nd May 2013, 16:12:52:

Here you go: DanielLaberge@aa05f72

Please note that left outer joins with outer restrictions aren't supported on my backport (see DanielLaberge@aa05f72#L4R112)

I haven't had time to look into why that is and since I don't have such a use case I've overlooked it and ignored that previously passing test. Someone more experienced with the code base could probably easily find out why it isn't working...

This was a pain to port since mainly because refactoring was done previously in NestedSelectRewriter which made it hard to merge (fd08421)

Hopefully this covers your use case as well as mine! Cheers.

---

Daniel Laberge added a comment — 22nd May 2013, 16:15:38:

You have to remove the trailing ) in my second link for it to work properly. Can't edit my comment...

---

Jonatas Hudler added a comment — 22nd May 2013, 18:34:53:

Daniel,

Nice! Just tested it out and worked perfectly. Great job! :D

Just a thought: I know I am a null contributor of this project and each merge situation might have its own special care, but I still wonder why some of this done solutions are not prioritized to be integrated to the trunk.

Anyways, I'll start to work with your version while this fix is not done officially. And thank you very much for spending your time sharing.

---

Daniel Laberge added a comment — 22nd May 2013, 18:39:12:

You're welcome!

Honestly, I have no idea how the process is usually handled, or who decides what goes into which version; This is my first contribution that is not a bug report or feature request.

---

xumix added a comment — 17th June 2013, 8:01:25:

Hi there, can anyone implement/take responsibility for this already?

---

Daniel Laberge added a comment — 17th June 2013, 13:38:34:

I have a backport of this working in the 3.3x branch, but I don't have commit priviledges to the repo though...

---

xumix added a comment — 17th June 2013, 13:41:29:

Looks like it is finally committed 93a3050

---

Daniel Laberge added a comment — 17th June 2013, 13:47:35:

That's the master branch and that commit is just a fix for specific use case (left join with group by). This feature has been in master for over 8 months. Until it's back-ported to the 3.3.x branch it's not going to be seen in an actual release.

---

Bilal Fazlani added a comment — 23rd June 2014, 7:18:03:

Is anyone looking into this ? Left join with linq is a very basic need for developers using nhibernate.

---

Matthias Kramp added a comment — 23rd June 2014, 8:10:17:

I'm watching for this issue too. I'm afraid, that the further development of nHibernate since month runs very slow?

---

Benjamin Hemken added a comment — 17th March 2016, 19:17:18:

Hi, the same here. We need the ability to use left outer joins without navigation properties. Navigation properties are in our case over the top because we're using NHibernate just as a simple mapper (with StatelessSession).

[kirinboy]

So, any updates on this?

[fredericDelaporte]

The changes written about here are released since quite some time, but as explained, it supports only some cases. There is a fundamental limitation, since the LINQ provider does work with HQL and HQL does not support un-mapped joins. If you want this to change you need someone (could be you) to work on adding arbitrary join support in HQL.

Otherwise there is other APIs in NHibernate having more support of join: Criteria and QueryOver, especially since 5.1 (see #1619 for details).

Also related: #1601.

[fernandocode]

@fredericDelaporte, I could not find adequate documentation to perform left join for unmapped relation entities, without a mapped relationship list, such as:

Models:

public class Reference
{
    public virtual long Id { get; set; }
    public virtual string Name { get; set; }
    public virtual bool IsOwn { get; set; }
}

public class Relacao
{
    public virtual long Id { get; set; }
    public virtual string Item { get; set; }
    public virtual Reference Reference { get; set; }
}

Mappers with Fluent:

public class ReferenceMapper : ClassMap<Reference>
{
    public ReferenceMapper()
    {
        Table("REFERENCE");
        Id(x => x.Id, "ID").GeneratedBy.Assigned();
        Map(x => x.Name, "NAME");
        Map(x => x.IsOwn, "IS_OWN");
    }
}

public class RelacaoMapper : ClassMap<Relacao>
{
    public RelacaoMapper()
    {
        Table("RELACAO");
        Id(x => x.Id, "ID").GeneratedBy.Assigned();
        Map(x => x.Item, "ITEM");
        References(x => x.Reference, "REFERENCE_ID");
    }
}

My intention is to have a query that fetches all Reference that IsOwn or is related to Reference in the entity or in relation to Reference contains "r" in the Item property. In SQL I would like something similar to this:

select REFERENCE0_.ID as ID1_64_, REFERENCE0_.NAME as NAME2_64_, REFERENCE0_.IS_OWN as IS3_64_, RELACAO1_.ITEM
from REFERENCE REFERENCE0_
left outer join RELACAO RELACAO1_ on RELACAO1_.REFERENCE_ID = REFERENCE0_.ID
where (RELACAO1_.ITEM like('%' || @ P0 || '%') or REFERENCE0_.IS_OWN = @ P1)

The query for this using linq would be this (with left outer join, which is not supported ):

from reference in session.Query<Reference>()
join _r in session.Query<Relacao>() on reference.Id equals _r.Reference.Id into r
from relacao in r.DefaultIfEmpty()
where
    relacao.Item.Contains("r")
    ||
    reference.IsOwn == true
select new { reference.Id, reference.Name, reference.IsOwn, relacao.Item }
).ToList();

How could I do this with Criteria or QueryOver? Could you give me some documentation?

It also seems to me that this use case of mine is a little different from what it has here, as it does not have the mapped relationship as a list, and may perhaps be considered in a possible future implementation.

Added a question in SO: https://stackoverflow.com/q/57795029/2290538