NHibernate Native SQL multiple joins
- by Chris
Hi all,
I"m having some problems with Nhibernate and native sql.
I've got an entity with alot of collections and I am doing an SQL Fulltext search on it. So when returning 100 or so entities, I dont want all collections be lazy loaded. For this I changed my SQL query:
SELECT Query.*
FROM (SELECT {spr.*},
{adr.*},
{adrt.*},
{cty.*},
{com.*},
{comt.*},
spft.[Rank] AS [Rak],
Row_number() OVER(ORDER BY spft.[Rank] DESC) AS rownum
FROM customer spr
INNER JOIN CONTAINSTABLE ( customerfulltext , computedfulltextindex , '" + parsedSearchTerm + @"' ) AS spft
ON spr.customerid = spft.[Key]
LEFT JOIN [Address] adr
ON adr.customerid = spr.customerid
INNER JOIN [AddressType] adrt
ON adrt.addresstypeid = adr.addresstypeid
INNER JOIN [City] cty
ON cty.cityid = adr.cityid
LEFT JOIN [Communication] com
ON com.customerid = spr.customerid
INNER JOIN [CommunicationType] comt
ON comt.communicationtypeid = com.communicationtypeid) as Query
ORDER BY Query.[Rank] DESC
This is how I setup the query:
var items = GetCurrentSession()
.CreateSQLQuery(query)
.AddEntity("spr", typeof(Customer))
.AddJoin("adr", "spr.addresses")
.AddJoin("adrt", "adr.Type")
.AddJoin("cty", "adr.City")
.AddJoin("com", "spr.communicationItems")
.AddJoin("comt", "com.Type")
.List<Customer>();
What happens now is, that the query returns customers twice (or more), I assume this is because of the joins since for each customer address, communicationItem (e.g. phone, email), a new sql row is returned. In this case I thought I could use the DistinctRootEntityResultTransformer.
var items = GetCurrentSession()
.CreateSQLQuery(query)
.AddEntity("spr", typeof(Customer))
.AddJoin("adr", "spr.addresses")
.AddJoin("adrt", "adr.Type")
.AddJoin("cty", "adr.City")
.AddJoin("com", "spr.communicationItems")
.AddJoin("comt", "com.Type")
.SetResultTransformer(new DistinctRootEntityResultTransformer())
.List<Customer>();
Doing so an exception is thrown. This is because I try to list customers .List<Customer>() but the transformer returns only entities of the last join added. E.g. in the case above, the entity with alias "comt" is returned when doing .List() instead of .List(). If I would switch last join with the join alias "cty", then the transformer returns a list of cities only...
Anyone knows how I can return a clean list of customers in this case?
