Why does Linq to Nhibernate produce an outer join

Question

I have an issue with Linq to Nhibernate producing queries with outer joins.

For example:

return Session.Linq<ClientContact>().Where(c => c.Client.Id = 13).ToList();

Produces a query similar to:

SELECT... FROM mw_crafru.clientcontact this_, mw_crafru.client client1_, mw_crafru.relationshiptype relationsh4_ WHERE this_.clientid = client1_.clientid(+) AND this_.relationshiptypeid = relationsh4_.relationshiptypeid AND client1_.clientid = :p0;:p0 = 13

Notice the outer join this.clientid = client1.clientid(+). Boo!

To resolve the issue I went back to using Session.CreateCriteria.

return Session.CreateCriteria(typeof (ClientContact)).Add(Restrictions.Eq("Client.Id", clientId)).List<ClientContact>();

Produces the following query:

SELECT... FROM mw_crafru.clientcontact this_, mw_crafru.client client1_, mw_crafru.relationshiptype relationsh4_ WHERE this_.clientid = client1_.clientid AND this_.relationshiptypeid = relationsh4_.relationshiptypeid AND client1_.clientid = :p0;:p0 = 13

Notice no outer join this.clientid = client1.clientid. Yay!

Anyone know why this is happening?

Answer 1

With SQL Server at least, a many-to-one mapping with not-null="true", or Not.Nullable() using Fluent NH, causes Get operations to use an inner join instead of a left join. Try adding that to your mapping for Client in ClientContact.