NHibernate Native SQL multiple joins

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
    开发者_JAVA技巧               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<Customer>(). 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?

try this

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")  
.AddEntity("spr", typeof(Customer))                        
.SetResultTransformer(new DistinctRootEntityResultTransformer())             
.List<Customer>(); 

GetCurrentSession.CreateQuery("select distinct spr from Customer spr inner join spr.Addresses
adr inner join adr.Type adrt inner join adr.City cty inner join apr.CommunicationItems com 
inner join com.Type comt").List<Customer>();

Hope this helps. Or you can write the desired SQL query under

GetCurrentSession.CreateSqlQuery(sqlquery).List<Customer>();