I am using Criteria and I want to explicitly tell hibernate to join on specific columns rather than the primary and foreign key columns. My code is
CreateCriteria<Application>("Application")
.CreateCriteria("Application.Address", "Address", JoinType.FullJoin)
The query that runs for this is
Select * from Application app Full Join Address add ON app.AdressId = add.Id
I want the query to be as
Select * from Application app Full Join Address add ON app.PersonId = add.PersonId
I changed my criteria to
CreateCriteria<Application>("Application")
.CreateCriteria("Application.Address", "Address", JoinType.FullJoin, Restrictions.eqProperty("Application.Person.Id", "Address.Person.Id"))
The sql query that runs is
Select * from Application app Full Join Address add ON app.PersonId = add.PersonId and app.AdressId = add.Id
Is there a way I can explicitly mention in the criteria on the joins I wish to have ?
NHibernate always does joins basing on foreign keys.
I guess that you have configured relationship between Application and Address entities via Application.AddressId foreign key.
That's why you have app.AdressId = add.Id in join.
If I'm right you cannot do the join on some other property. You can find a slower workaround with subqueries or write a sql query manually.
See other answers: