c# - Fluent NHibernate Join 映射到 Joined 表

Question

我有这样的实体:

class Doc 
{
public virtual int Id {get;set;}
public virtual int Code {get;set;}
}

其中 Id 是身份。
而代码放在另外一个表中。获取 Code 我使用下一个 SQL 语句:

Select Distinct A.CODE from DOCLIST D Left Join DOCSLINKS  DL On DL.TODOC_ID=D.DOC_ID
Left Join ARTICLES A On DL.ART_ID=A.ART_ID  Where D.DOC_ID=*ourid*

ourid 是我们实体的 Id

Doc类的映射是:

public class DocMap : ClassMap<Doc>
    {
        public DocMap()
        {
            Table("DOCLIST");
            Id(x =>x.Id).Column("DOC_ID").GeneratedBy.Custom<NHibernate.Id.TriggerIdentityGenerator>(); 

            //HOW TO MAP CODE?
        }
     }

我尝试使用公式:

Map(x => x.Code).Formula("(Select Distinct A.CODE from DOCLIST D Left Join DOCSLINKS  DL On DL.TODOC_ID=D.DOC_ID
Left Join ARTICLES A On DL.ART_ID=A.ART_ID  Where D.DOC_ID=Id)").Not.Update();

但我处理了一个异常 ORA-00904 : "DOC0_"."ID": invalid identifier with message:

could not load an entity: [Doc#1562][SQL: SELECT doc0_.DOC_ID as DOC1_3_0_, (Select Distinct A.OKP_CODE from DOCLIST D  Left join  DOCSLINKS DL ON DL.TODOC_ID=D.DOC_ID Left join ARTICLES A ON DL.ART_ID=A.ART_ID  Where D.DOC_ID=doc0_.Id) as formula0_0_ FROM DOCLIST doc0_ WHERE doc0_.DOC_ID=?]

谁能帮我映射代码？

Answer 1

我想我们应该更改选择。它不应该使用当前表 DOCLIST (它已经用于映射根类 Doc .. Table("DOCLIST"))

// current select
(SELECT Distinct A.CODE 
 FROM DOCLIST D 
 Left Join DOCSLINKS DL On DL.TODOC_ID=D.DOC_ID
 Left Join ARTICLES  A  On DL.ART_ID=A.ART_ID  
 Where D.DOC_ID=Id)

这应该可以完成工作(或进行一些调整)

(SELECT Distinct A.CODE 
 FROM DOCSLINKS DL
 Left Join ARTICLES  A  On DL.ART_ID=A.ART_ID  
 Where DL.TODOC_ID = DOC_ID) // DOC_ID should be column on current table

我们已经可以访问表 DOCLIST(当前表)。我们只需要从其他表中选择，同时在 wwhere 子句中使用我们的 ID 来过滤 DOCSLINKS:

Where DL.TODOC_ID = DOC_ID // DOC_ID will be used from current table