3

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 this is easy, you just have to write inner join fetch instead of left join fetch, but I couldn't find that for Linq.

cremor
  • 6,669
  • 1
  • 29
  • 72

3 Answers3

2

UPDATE 2:

https://www.nuget.org/packages/NHibernate.Linq.InnerJoinFetch

Enjoy!!!

UPDATE:

I finally managed to do this and sent the followign pull request to the NH team

https://github.com/nhibernate/nhibernate-core/pull/210

In Fact, it is possible but you need to hack NHibernate, the line to change is this one

tree.AddFromClause(tree.TreeBuilder.LeftFetchJoin(join, tree.TreeBuilder.Alias(alias)));

into 

tree.AddFromClause(tree.TreeBuilder.FetchJoin(join, tree.TreeBuilder.Alias(alias)));

at the ProcessFetch.cs file

It seems the behavior is hardcoded for Linq and I think its because its using the extension methods in order to send what to use for the DefaultQueryProvider and re-linq processing, I still need to figure out how to specify the kind of join you want to do on eager fetching using the linq api but I'm working on it, hopefully I'll send a pull request to the NH team so they can put this into the codebase, for now in my project I'll use my custom NH version

Paleta
  • 970
  • 2
  • 13
  • 27
  • Nice extension, but we are using the nhibernate 4 and when I add it to the project, the nuget set the nhibernate to 3.3. Is there any way to provide a new version for the current version of nhibernate? Or how could I add it on my project? – Felipe Oriani Mar 15 '17 at 18:28
2

Since this isn't possible yet, I have created a Jira issue for it: NH-2790

cremor
  • 6,669
  • 1
  • 29
  • 72
0

Not sure if you can control the JOIN type in LINQ but you can still have strongly typed queries controlling the JOIN type using the QueryOver API.

var ordersWithCustomers = Session.QueryOver<Order>()
  .JoinQueryOver(o => o.Customer,JoinType.InnerJoin) //You can specify join type
  .List();

Also try adding not-null="true" in your mapping, although I just tried this and it didn't make a difference to the join type in the query.

Wierdly this works for the inner join (using the old ANSI SQL joining syntax) but doesn't fetch the Customer.

var ordersWithCustomers = 
   from o in Session.Query<Order>()
   join c in Session.Query<Customer>() on o.Customer.Id equals c.Id
   select o;
Mark Perry
  • 1,705
  • 10
  • 12
  • Thanks for the QueryOver suggestion, at least that would be more typesafe than HQL. I've also tried `not-null="true"` and as you said, that doesn't change anything. – cremor Jul 08 '11 at 10:25
  • Just updated with the LINQ expression syntax example although even that doesn't solve the problem, might give you another idea. – Mark Perry Jul 08 '11 at 10:27
  • I've tried that combined with `Fetch()`. Seems to work, but produces very ugly and inefficient SQL (something like `Order left join OrderJoinTable left join OrderJoinTable2, Customer left join OrderJoinTable left join OrderJoinTable2 where Order.ID = Customer.ID` where OrderJoinTables are coming from my elements in the entity). – cremor Jul 08 '11 at 10:41
  • I have a feeling the answer here is that you cannot force an `inner join` using the LINQ API in NHibernate. Whether that is bug or not is debatable I guess. – Mark Perry Jul 08 '11 at 10:58