CODEWITHSUNDEEP
c#nhibernate
ruffen
ruffen

Reputation: 1721

nhibernate joinqueryover unrelated tables (same foreign key)

Basically what i am trying to do is join two tables on foreign keys. I have this query: 

        var result =
            _session.QueryOver(() => contentReferenceAlias)
                    .Inner.JoinAlias(() => contentReferenceAlias.ContentReference, () => contentLibrarySearchAlias.ContentReference)
                    .Where(() => contentReferenceAlias.ToLanguage.Id == languageId && contentReferenceAlias.ContentReference.Id == contentLibrarySearchAlias.ContentReference.Id)
                    .SelectList(list => list
                                            .Select(() => contentReferenceAlias.ContentReference)
                                            .Select(() => contentLibrarySearchAlias.ContentReference)
                                            .Select(() => contentReferenceAlias.ContentReference.Id).WithAlias(() => resultAlias.ContentReferenceId)
                                            .Select(() => contentReferenceAlias.ContentReference.Id).WithAlias(() => resultAlias.ContentReferenceId)
                                            .Select(() => contentReferenceAlias.OrderedFrom).WithAlia

The SQL im trying to recreate: 

  SELECT A.OrderedFrom, C.LastOrdered, A.ContentReferenceId, B.Title FROM TranslationContentReference A
  INNER JOIN TranslationOrder C ON (A.TranslationOrderId = C.Id)
  INNER JOIN ContentLibrarySearch B ON (A.ContentReferenceId = b.ContentReferenceId)
  WHERE A.ToLanguageId = 'xxxx-xxxx-xxxx-xxxx-xxxx'

Upvotes: 3

Views: 2037

Answers (1)

Radim Köhler
Radim Köhler

Reputation: 123861

If I do understand your scenario correctly, join over man-in-the-middle (foreign key reference) cannot be achieved via QueryOver API. NHibernate needs to know pathes all the way down, So if there is no explicit mapping from TranslationContentReference through ContentReference to ContentLibrarySearch, then we cannot create correct JoinAliases.

So, first option is to extend the man-in-the-middle object

public class ContentReference
{
    ...
    public virtual IList<TranslationContentReference> TranslationContentReference { get; set;}
    public virtual IList<ContentLibrarySearch> ContentLibrarySearch { get; set;}
}

Then we can navigate (create pathes)

  • from TranslationContentReference to ContentReference
  • from ContentReference to ContentLibrarySearch

The second option, which is less NHibernate and more SQL, is to create ISQLQuery 

ISQLQuery query = session.CreateSQLQuery(
  "SELECT A.OrderedFrom, C.LastOrdered, A.ContentReferenceId, B.Title 
    FROM TranslationContentReference A
     INNER JOIN TranslationOrder C ON (A.TranslationOrderId = C.Id)
     INNER JOIN ContentLibrarySearch B ON (A.ContentReferenceId = b.ContentReferenceId)
    WHERE A.ToLanguageId = :language");
query.SetString("language", "xxxx-xxxx-xxxx-xxxx-xxxx");
var result = query.SetResultTransformer(new AliasToBeanResultTransformer(typeof(MyDTO)))
.List();

Upvotes: 1

Related Questions