c# - nhibernate joinqueryover不相关的表(相同的外键)

基本上我想做的是在外键上联接两个表。我有这个查询：

        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

SQL im试图重新创建：

  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'

最佳答案

如果我确实了解您的情况，则无法通过QueryOver API来实现中间人（外键参考）的联接。 NHibernate需要完全了解路径，因此，如果没有从TranslationContentReference到ContentReference到ContentLibrarySearch的显式映射，那么我们将无法创建正确的JoinAliases。

因此，第一个选择是扩展中间人对象

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

然后我们可以导航（创建路径）


从TranslationContentReference到ContentReference
从ContentReference到ContentLibrarySearch


第二个选择是创建ISQLQuery，这是较少的NHibernate和更多的SQL。

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();

关于c# - nhibernate joinqueryover不相关的表(相同的外键)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13804204/