I've these organizations during my database

enter image description here

For any given Job, I wish to essentially obtain the leads to the next form

<Translation>,<ExternalUnit.Text>,<ExternalTranslation.Text>

in which the joining condition is the fact that Translation.Unit.Text == ExternalUnit.Text

This is exactly what I've to date, working fine:

var props = session.QueryOver<Translation>(() => translation)
            .Select(c => translation.Id, c => externalUnit.Text, c => externalTranslation.Text)
            .JoinAlias(() => translation.TranslationUnit, () => unit)                
            .JoinAlias(() => unit.Job, () => job)
            .Where(() => unit.Job == job)
            .JoinAlias(() => job.ExternalUnits, () => externalUnit)
            .JoinAlias(() => externalUnit.ExternalTranslations, () => externalTranslation)
            .Where(() => externalUnit.Text == unit.Text)
            .List<object[]>();

var translations = session.QueryOver<Translation>(() => translation)                
            .JoinAlias(() => translation.TranslationUnit, () => unit)                
            .JoinAlias(() => unit.Job, () => job)
            .Where(() => unit.Job == job)
            .JoinAlias(() => job.ExternalUnits, () => externalUnit)
            .JoinAlias(() => externalUnit.ExternalTranslations, () => externalTranslation)
            .Where(() => externalUnit.Text == unit.Text)
            .List<Translation>()
            .ToList();

I Quickly loop through translations, mentioning to props. However, I do not such as this approach since i have unnecessarily perform two (almost identical) queries towards the database rather than only one.

However I can't obtain the preferred projection working. I believed about something similar to this:

var data = session.QueryOver<Translation>(() => translationAlias)                
            .JoinAlias(() => translation.TranslationUnit, () => unit)                
            .JoinAlias(() => unit.Job, () => job)
            .Where(() => unit.Job == job)
            .JoinAlias(() => job.ExternalUnits, () => externalUnit)
            .JoinAlias(() => externalUnit.ExternalTranslations, () => externalTranslation)
            .Where(() => externalUnit.Text == unit.Text)
            .Select(() => translation, () => externalUnit.Text, () => externalTranslation.Text)
            .List()

but, clearly, NHibernate isn't keen on the Select(() => translation...) bit (it doesn't let me project the entire entity).

Ideally I must choose into anonymous types, like

var data = session.QueryOver<Translation>()
             ...
             .Select(() => new { A = translation, B = externalTranslation })

however i guess NHibernate isn't there to date...

Thanks greatly for just about any suggestion.

I have first got it! NHibernate's LINQ provider saved me. What's great about this is it can choose into anonymous types, making joining a lot simpler. Just just in case someone is curious, here you go for my particular situation:

var q = 
(from c in
  (from b in            
    (from translation in session.Query<Translation>()
     join unit in units on translation.Unit equals unit
     where unit.Job == job
     select new { Translation = translation, Original = unit.Text })         
   join extUnit in externalUnits on job equals extUnit.Job
   where extUnit.Text == b.Original
   select new { Translation = b.Translation, ExternalUnit = extUnit })
 join extTranslation in extTranslations on c.ExternalUnit equals extTranslation.Unit
 select new { Translation = c.Translation, Suggestion = extTranslation })
 .ToList();

The SQL it creates is extremely reasonable, and so i am pretty happy :)