QueryOver multiple tables with NHibernate

Question

I'm trying to left join multiple tables and project some columns that result from this join onto a new entity, and then taking a few records from this result from my database. I've taken a look on a few similar questions here on SF, but I'm not managing to assemble all of those parts into a piece of code that works.

Here is the query I'm trying to generate with NHibernate:

select * from 
( select LOC_Key.KeyName, LOC_Translation.TranslationString, LOC_Translation.Comments
  from LOC_Key
  left join LOC_Translation
  on LOC_Key.ID = LOC_Translation.KeyID and LOC_Translation.LanguageID = 6
  order by LOC_Key.KeyName 
) as keyTable 
limit 0,100

I have three entities here, Key, Translation and Language. A Key is a unique string identifier for different translations of a same word in different languages. I want to show the first n keys in alphabetical order for a language, but I want all keys listed, not only the ones that are translated (that's why I'm using a left join).

I took a look at QueryOver<>, Select() method and List<object[]>() method but I can't even manage to have a code that compiles in the first place.

I could use C# linq after getting all records from the tables Key and Translation, having something like this:

IEnumerable<string> k = RepositoryKey.GetLimits( offset, size ).Select( x => x.KeyName );

IEnumerable<TranslationDescriptor> t = RepositoryTranslation.GetAllWhere( x => x.LanguageID.LanguageCode == language && k.Contains ( x.KeyID.KeyName ) ).ToList().ConvertAll( new Converter<Translation, TranslationDescriptor>( ( x ) => { return new TranslationDescriptor { LanguageCode = x.LanguageID.LanguageCode, KeyName = x.KeyID.KeyName, Comments = x.Comments, TranslationString = x.TranslationString }; } ) );

var q = from key in k
        join trl in t on key equals trl.KeyName into temp
        from tr in temp.DefaultIfEmpty()
        select new TranslationDescriptor { KeyName = key, LanguageCode = language, Comments = ( tr == null ) ? string.Empty : tr.Comments, TranslationString = ( tr == null ) ? string.Empty : tr.TranslationString };

However, that's very slow. By the way, my implementation for GetLimits and GetAllWhere is:

public IEnumerable<T> GetAllWhere(Func<T, bool> func)
{
    var products = Session.Query<T>().Where(func);
    return products;
}

public IEnumerable<T> GetLimits(int offset, int size)
{
    return Session.CreateCriteria(typeof(T)).SetFirstResult(offset).SetMaxResults(size).List<T>();
}

Thank you for your help!

Bruno

Answer 1

I'm guessing a little bit at your entities and mappings, but the following might help you get ideas. It joins Key to Translation with a left outer join, then projects the results to a new DTO object.

[Test]
public void LeftOuterProjection()
{
    using (var s = OpenSession())
    using (var t = s.BeginTransaction())
    {
        // Set up aliases to use in the queryover.
        KeyDTO dtoAlias = null;
        Key keyAlias = null;
        Translation translationAlias = null;

        var results = s.QueryOver<Key>(() => keyAlias)
            .JoinAlias(k => k.Translations, () => translationAlias, JoinType.LeftOuterJoin)
            .Where(() => translationAlias.LanguageId == 6)
            .OrderBy(() => keyAlias.KeyName).Asc
            .Select(Projections.Property(() => keyAlias.KeyName).WithAlias(() => dtoAlias.KeyName),
                    Projections.Property(() => translationAlias.TranslationString).WithAlias(() => dtoAlias.TranslationString),
                    Projections.Property(() => translationAlias.Comments).WithAlias(() => dtoAlias.Comments))
            .TransformUsing(Transformers.AliasToBean<KeyDTO>())
            .List<KeyDTO>();
    }
}

public class KeyDTO
{
    public string KeyName { get; set; }
    public string TranslationString { get; set; }
    public string Comments { get; set; }
}

public class Key
{
    public int Id { get; set; }
    public string KeyName { get; set; }
    public IList<Translation> Translations { get; set; }
}

public class Translation
{
    public Key Key { get; set; }
    public int LanguageId { get; set; }
    public string TranslationString { get; set; }
    public string Comments { get; set; }
}