NHibernate Using SetProjection To Retrieve Individual Collection Items

I am trying to use Projections to Create a DTO from a Lead class. My DTO has, for example, a Home phone number, a mobile number and an email address, but the Lead Class has a collection of 开发者_开发问答contact details. So I'm trying to find a way to retrieve each contact detail so that I can set the properties on the dto. I have tried using sub queries and projections but to no avail.

The SQL I'm trying to generate is something like this:

SELECT 
     A.LeadId, 
     B.ContactId,
     B.Value,
     C.ContactId, 
     C.Value, 
     D.ContactId
FROM Lead A
LEFT JOIN ContactDetail B ON A.LeadId=B.LeadId AND B.ContactType='Home Number'
LEFT JOIN ContactDetail C ON A.LeadId=C.LeadId AND C.ContactType='Mobile Number'
LEFT JOIN ContactDetail D ON A.LeadId=D.LeadId AND D.ContactType='Email Address'

So in short I'm trying to join to the same table 3 times based on different criteria, and I know that in NHibernate I can't use CreateAlias to join to the same table more than once and I'm anxious to know if this is possible using either the Criteria API or NHIbernate Linq. Thanks in advance for any help.

I would load the "regular" lead instance from nHibernate with its contact detail collection. Then I would use AutoMapper, to map it to the DTO class.

In my opinion, this is a much cleaner approach since you do not create special data access methods just for "simple" DTO mappings. And it's easier for refactoring since everything is expressed via "c sharp code".

Link to AutoMapper