无法使我的非键引用使用与 Fluent NHibernate 的左联接

Question

TripSent and ReturnCode

我想在这些表中做一个左连接，比如

"left join TripSent on TripSent.ReturnCode = ReturnCode.Id"

这是我的 TripSent 映射类

public TripSentMap()
{
    ImportType<RemittanceDetailTransHeader>();
    Schema(Dao.DataAccess.Schema);
    Table("EI_TRIP_SENT");
    LazyLoad();
    Id(x => x.Id)
      .Column("ID")
      .CustomType("Int64")
      .Access.Property()
      .CustomSqlType("NUMBER")
      .Not.Nullable()
      .Precision(12)
      .GeneratedBy.Sequence("EI_TRIP_SENT_ID_SEQ");
    //EDIT : Removed redundant Mapping Info
    References(x => x.ReturnCode)
      .Class<ReturnCode>()
      .Access.Property()
      .Cascade.None()
      .LazyLoad()
      .Columns("EI_RETURN_CODE_ID"); 
}

这是我的总部

String hql = @"SELECT ts.PartialTripNumber, ts.Sequence, ts.TagIssuer, ts.TagNumber, ts.DateTime, ts.PlazaNumber, ts.LaneNumber, ts.RealCategory, ts.DetectedCategory,
    ts.TabulatedCategory, ts.Amount, ts.PassMode, ts.LicensePlate, ts.ImageReason, ts.ReturnCode, ts.TripSentStatus
    FROM TripSent ts
    WHERE ts.Remittance.Id = :remId";
var transactionDetailsHql = _session.CreateQuery(hql)
                .SetParameter("remId", remId)
                .List();

这将生成如下所示的查询

select tripsent0_.PARTIAL_TRIP_NUMBER    as col_0_0_,
       tripsent0_.SEQUENCE               as col_1_0_,
       tripsent0_.EI_TAG_ISSUER_ID       as col_2_0_,
       tripsent0_.TAG_NUMBER             as col_3_0_,
       tripsent0_.DATE_TIME              as col_4_0_,
       tripsent0_.PLAZA_NUMBER           as col_5_0_,
       tripsent0_.LANE_NUMBER            as col_6_0_,
       tripsent0_.REAL_CATEGORY          as col_7_0_,
       tripsent0_.DETECTED_CATEGORY      as col_8_0_,
       tripsent0_.TABULATED_CATEGORY     as col_9_0_,
       tripsent0_.AMOUNT                 as col_10_0_,
       tripsent0_.EI_PASS_MODE_ID        as col_11_0_,
       tripsent0_.LICENSE_PLATE          as col_12_0_,
       tripsent0_.EI_IMAGE_REASON_ID     as col_13_0_,
       tripsent0_.EI_RETURN_CODE_ID      as col_14_0_,
       tripsent0_.EI_TRIP_SENT_STATUS_ID as col_15_0_,
       tagissuer1_.ID                    as ID36_0_,
       passmode2_.ID                     as ID14_1_,
       imagereaso3_.ID                   as ID12_2_,
       returncode4_.ID                   as ID28_3_,
       tripsentst5_.ID                   as ID48_4_,
       tagissuer1_.DESCRIPTION           as DESCRIPT2_36_0_,
       tagissuer1_.EI_TAG_CHECK_TYPE_ID  as EI3_36_0_,
       tagissuer1_.EI_TAG_OSA_ID         as EI4_36_0_,
       passmode2_.DESCRIPTION            as DESCRIPT2_14_1_,
       imagereaso3_.DESCRIPTION          as DESCRIPT2_12_2_,
       returncode4_.DESCRIPTION          as DESCRIPT2_28_3_,
       tripsentst5_.DESCRIPTION          as DESCRIPT2_48_4_
from   CENTRO.EI_TRIP_SENT tripsent0_
       inner join CENTRO.EI_TAG_ISSUER tagissuer1_
         on tripsent0_.EI_TAG_ISSUER_ID = tagissuer1_.ID
       inner join CENTRO.EI_PASS_MODE passmode2_
         on tripsent0_.EI_PASS_MODE_ID = passmode2_.ID
       inner join CENTRO.EI_IMAGE_REASON imagereaso3_
         on tripsent0_.EI_IMAGE_REASON_ID = imagereaso3_.ID
       inner join CENTRO.EI_RETURN_CODE returncode4_
         on tripsent0_.EI_RETURN_CODE_ID = returncode4_.ID
       inner join CENTRO.EI_TRIP_SENT_STATUS tripsentst5_
         on tripsent0_.EI_TRIP_SENT_STATUS_ID = tripsentst5_.ID
where  tripsent0_.EI_REMITTANCE_ID = 12 /* :p0 */

但我想要一个左外部连接而不是

inner join CENTRO.EI_RETURN_CODE returncode4_ on tripsent0_.EI_RETURN_CODE_ID = returncode4_.ID

我找不到如何做到这一点我也尝试过映射

References(x => x.ReturnCode)
      .Class<ReturnCode>()
      .Access.Property()
      .Cascade.None()
      .NotFound.Ignore().Nullabe()
      .Not.LazyLoad()
      .Columns("EI_RETURN_CODE_ID"); 

未成功 D：

Answer 1

应从映射中删除冗余信息，使其更易于阅读。

此外，像NHibernate这样的对象/关系映射器旨在从关系数据库返回对象。如果您尝试指定类似 LEFT JOIN 的内容，则似乎不是在尝试拉取对象，而是在拉取一些自定义视图。

您仍然可以执行此操作，但您需要一个数据传输对象 （DTO），该对象基本上是一个表示查询中每一行将返回的内容的对象。您仍然可以利用 NHibernate 的数据库连接并使用CreateSqlQuery运行原始 SQL，然后根据需要将结果转换为对象。

Answer 2

在这种情况下

，我需要编写一个纯SQL查询。但这是一个孤立的。