NHibernate的joinqueryover不相关的表（与外键） [英] nhibernate joinqueryover unrelated tables (same foreign key)

问题描述

基本上我所试图做的是加入外键两个表。我有这个疑问：

  VAR的结果= 
 _session.QueryOver（（）=> contentReferenceAlias）
 .Inner.JoinAlias（（）=> contentReferenceAlias.ContentReference，（）=> contentLibrarySearchAlias.ContentReference）
。凡（（）=> contentReferenceAlias.ToLanguage.Id == languageId&放大器;&放大器; contentReferenceAlias。 ContentReference.Id == contentLibrarySearchAlias.ContentReference.Id）
 .SelectList（名单=>清单
。选择（（）=> contentReferenceAlias.ContentReference）
。选择（（）=> ; contentLibrarySearchAlias.ContentReference）
。选择（（）=> contentReferenceAlias.ContentReference.Id）.WithAlias（（）=> resultAlias.ContentReferenceId）
。选择（（）=> contentReferenceAlias.ContentReference .ID）.WithAlias（（）=> resultAlias.ContentReferenceId）
。选择（（）=> contentReferenceAlias.OrderedFrom）.WithAlia 
  

这个SQL IM试图重建：

  SELECT A.OrderedFrom，C.LastOrdered，A.ContentReferenceId，B.Title FROM TranslationContentReference一个
 INNER JOIN TranslationOrder C ON（A.TranslationOrderId = C.Id）
 INNER JOIN ContentLibrarySearch b开（A.ContentReferenceId = b.ContentReferenceId）
，其中A.ToLanguageId ='XXXX-XXXX -XXXX-XXXX-XXXX
  

解决方案

如果我明白您的方案正确，加入了人在这方面的中间人（外键参考）不能通过QueryOver API来实现。 NHibernate的需要知道pathes一路下跌，所以，如果有从 TranslationContentReference 到 ContentReference 没有显式映射 ContentLibrarySearch ，那么我们就不能创造正确的JoinAliases。

所以，第一种选择是延长人在这方面的中间人对象

 公共类ContentReference 
 {
 ... 
公共虚拟的IList< TranslationContentReference> TranslationContentReference {搞定;设置;} 
公共虚拟的IList< ContentLibrarySearch> ContentLibrarySearch {搞定;设置;} 
} 
  

然后我们就可以导航（创建pathes）

• 从TranslationContentReference到ContentReference


• 从ContentReference到ContentLibrarySearch

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

  ISQLQuery查询= session.CreateSQLQuery（
选择A.OrderedFrom，C.LastOrdered，A.ContentReferenceId，B.Title 
起价TranslationContentReference一个
 INNER JOIN TranslationOrder C ON（A.TranslationOrderId = C.Id）
 INNER JOIN ContentLibrarySearch b开（A.ContentReferenceId = b.ContentReferenceId）
其中A.ToLanguageId =语言）; 
 query.SetString（语言，XXXX-XXXX-XXXX-XXXX-XXXX'）; 
 VAR的结果= query.SetResultTransformer（新AliasToBeanResultTransformer（typeof运算（MyDTO）））
的.List（）; 
  

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'

解决方案

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

这篇关于NHibernate的joinqueryover不相关的表（与外键）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！