Inner join fetch in Linq query possible?

[cremor]

Is it possible to modify a query (or the mapping) so that the call to
the .Fetch() extension method of NHibernate results in a inner join in
the generated SQL query?

In HQL or QueryOver this is easy, but I couldn't find that for Linq.

A workaround seems to be writing the join myself in the Linq query,
but then all <join> elements in the entity are joined twice (very ugly
and inefficient SQL).

[Ricardo Peres]

The INNER JOIN or the LEFT OUTER JOIN is selected depending on which
the endpoint of the relation can be null (not-null="true" or not-
null="false" in the hbm.xml mappings).

[cremor]

If it should be that way it isn't working. It's still a left outer
join when I set the many-to-one to not-null (using 3.2.0.CR1).

[CSharper]

The question for me is why do you want NHibernate to perform an INNER
JOIN. If you want a correct result in the general case without the not
null constraint, an outer join is required. And if you have a not null
constraint and a foreign key, a good query planner should detect the
situation and optimize the query the same way as if it would have been
an outer join in the first place.

[cremor]

I need an inner join for the query itself, the fetch is just an
additional requirement to prevent a select n + 1 problem.

Concrete use-case:
A table that saves the last 100 opened customers for each user. This
table has a reference to the customer table. The customer table has
limited access (by Oracle FGAC) which prevents access to customer rows
which the user isn't allowed to see. It could happen that a user is
moved to another security group which then suddenly results in the
user having last-opened-entries for customers he hasn't access to
anymore. Therefore I have to query the last opened table with an inner
join to the customers table so that only rows are returned which the
user has access to. The fetch is needed because I have to show the
customer names in a list in the UI.

This is supported by HQL and Criteria perfectly, just seems like it
isn't yet implemented for Linq.

[Ricardo Peres]

cremor:

Have you tried this?

var q = (from a1 in session.Query<Entity>()
from a2 in session.Query<Entity>()
where a1.Id == a2.Id
select a1.Id + a2.Id).ToList();

[cremor]

You mean something like this (fetch is needed to prevent select n + 1
problem)?

var entries = (from o in session.Query<LastOpened<Customer>>().Fetch(x
=> x.Entity)
from c in session.Query<Customer>()
where o.Entity.Id == c.Id
select o).ToArray();

This results in the following (wrong and extremly ugly) SQL:

LastOpened o left outer join
Customers c1 left outer join
JoinTable1 j1 left outer join
JoinTable2 j2 inner join
Customers c2 left outer join
JoinTable1 j3 left outer join
JoinTable2 j4,
Customers c3 left outer join
JoinTable1 j5 left outer join
JoinTable2 j6
where c2.Id = c3.Id

The JoinTables are coming from my <join> elements in the Customer
entity.

[Ricardo Peres]

Sorry, at some point, I thought you wanted the entity to reference
itself... my fault!
Can't help...

[Darren Kopp]

cant you do

var query = from x in session.Query<X>()

                 join y in session.Query<Y>() on x.Id = y.OtherId

doesn't that work in the new linq provider? That should give you an inner join.

[cremor]

Yes, but I have to combine it with .Fetch(), otherwise the association
isn't created. And if I combine it, it generates the wrong SQL that
I've already posted.