连接两个不相关的视图表与NHibernate和的ICriteria [英] Joining two unrelated view-tables with nhibernate and ICriteria

问题描述

我有一个基于两个视图两个实体。该映射是这样的：

Entiy答：

 <类名=搜索查看表=搜索查看动态更新=真正的可变=false的模式行动=无> 
< ID名称=ID类型=GUID列=ID/> 
<属性名=ID列=ID类型=GUID/> 
<属性名=过期列=过期类型=日期时间/> 
<属性名=VerificationNumber栏=VerificationNumberTYPE =的Int32/> 
<属性名=InvoiceNo栏=InvoiceNoTYPE =的Int32长=50/> 
<属性名=状态列=FakturaStatusTYPE =的Int32/> 
< /班> 
  

实体B：

 <类名=SearchInvoiceResourceLookUpView表=SearchInvoiceResourceLookUpView动态更新=真正的可变=false的模式行动=无> 
< ID名称=ID类型=GUID列=ID/> 
 
<属性名=InvoiceId栏=InvoiceId类型=GUID/> 
<属性名=RESOURCEID栏=RESOURCEID类型=GUID/> 
< /班> 
  

实体A 是基于一个更复杂的表结构的平面视图，为搜索优化表视图。现在我希望能够从中获取的所有行的实体A 其中ID是列InvoiceId中的实体B 在RESOURCEID的特定值< STRONG>实体B 按使用NHibernate和标准的API。两个表是看法和他们没有关系的声明。我曾尝试在C＃下面的代码，但它不工作：

  VAR标准= _session.CreateCriteria（typeof运算（搜索查看） ）; 
 criteria.CreateAlias​​（SearchInvoiceResourceLookUpView，SRF，JoinType.InnerJoin）
。新增（Restrictions.EqProperty（sfr.InvoiceId，ID））
。新增（限制.EQ（sfr.ResourceId，invoiceResId））; 
  

用于此目的的原始的SQL将是：

  SELECT * FROM搜索查看
 JOIN SearchInvoiceResourceLookUpView SRF上srf.InvoiceId = ID 
，其中srf.ResourceId ='[Inser资源ID这里]'
  

我要如何解决这个问题？

有没有其他更好的方式来做到这一点？

解决方案

在内部消除我们的实体之间明确的映射情况，我们可以使用仅 HQL。

• 的 14.2。 from子句的（小举和片段）的

可以出现多个类，导致笛卡尔乘积或十字加入。

 从公式，参数
从公式的形式，参数为参数
  

因此，在上述情况下，我们将不得不HQL是这样的：

 从搜索查看AS SV，SearchInvoiceResourceLookUpView AS SRF 
其中srf.InvoiceId = sv.Id 
和srf.ResourceId ='[这里Inser资源ID]' 
  

的也有一些选择应该使用，也许DTO与结果变压器...

I have two entities based on two views. The mappings looks like this:

Entiy A:

<class name="SearchView" table="SearchView" dynamic-update="true" mutable="false" schema-action="none">
    <id name="Id" type="Guid" column="Id" />
    <property name="Id" column="Id" type="Guid" />
    <property name="Expires" column="Expires" type="DateTime" />
    <property name="VerificationNumber" column="VerificationNumber" type="Int32" />
    <property name="InvoiceNo" column="InvoiceNo" type="Int32" length="50" />
    <property name="Status" column="FakturaStatus" type="Int32" />
</class>

Entity B:

<class name="SearchInvoiceResourceLookUpView" table="SearchInvoiceResourceLookUpView" dynamic-update="true" mutable="false" schema-action="none">
    <id name="Id" type="Guid" column="Id" />

    <property name="InvoiceId" column="InvoiceId" type="Guid" />
    <property name="ResourceId" column="ResourceId" type="Guid" />        
</class>

Entity A is based on a table view that is a flattened view of a more complex table-structure, for search optimization. Now i want to be able to get all the rows from Entity A where the Id is in column "InvoiceId" in Entity B for a specific value of "ResourceId" in Entity B by using NHibernate and the Criteria-API. The both tables are views and they have no declared relationship. I have tried the following code in C# but it doesn't work:

        var criteria = _session.CreateCriteria(typeof(SearchView));
            criteria.CreateAlias("SearchInvoiceResourceLookUpView", "srf",JoinType.InnerJoin)
                .Add(Restrictions.EqProperty("sfr.InvoiceId", "Id"))
                .Add(Restrictions.Eq("sfr.ResourceId", invoiceResId));

The raw SQL for this purpose would be:

SELECT * FROM SearchView
JOIN SearchInvoiceResourceLookUpView srf on srf.InvoiceId = Id 
WHERE srf.ResourceId = '[Inser resource id here]'

How do i solve this?

Is there another, better way to do this?

解决方案

In scenarios whithout explicit mapping between our entities, we can use only HQL.

• 14.2. The from clause (small cite and snippet)

Multiple classes may appear, resulting in a cartesian product or "cross" join.

from Formula, Parameter
from Formula as form, Parameter as param

So in the case above we would have HQL like this:

FROM SearchView AS sv, SearchInvoiceResourceLookUpView AS srf
WHERE srf.InvoiceId = sv.Id
AND srf.ResourceId = '[Inser resource id here]'

Also some SELECT should be used and maybe DTO with Result Transformer...

这篇关于连接两个不相关的视图表与NHibernate和的ICriteria的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！