使用 nhibernate 和 ICriteria 连接两个不相关的视图表 [英] Joining two unrelated view-tables with nhibernate and ICriteria
问题描述
我有两个基于两个视图的实体.映射如下所示:
I have two entities based on two views. The mappings looks like this:
实体 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>
实体 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>
实体 A 基于一个表视图,它是一个更复杂的表结构的扁平视图,用于搜索优化.现在我希望能够从 Entity A 中获取所有行,其中 Id 位于 Entity B 的InvoiceId"列中,用于 Entity B 通过使用 NHibernate 和 Criteria-API.这两个表都是视图,它们没有声明的关系.我在 C# 中尝试了以下代码,但它不起作用:
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));
用于此目的的原始 SQL 将是:
SELECT * FROM SearchView
JOIN SearchInvoiceResourceLookUpView srf on srf.InvoiceId = Id
WHERE srf.ResourceId = '[Inser resource id here]'
我该如何解决这个问题?
还有其他更好的方法吗?
推荐答案
在实体之间没有显式映射的场景中,我们可以仅使用 HQL.
In scenarios whithout explicit mapping between our entities, we can use only HQL.
- 14.2.from 子句 (小引用和片段)
可能会出现多个类,导致笛卡尔积或交叉"连接.
Multiple classes may appear, resulting in a cartesian product or "cross" join.
from Formula, Parameter
from Formula as form, Parameter as param
所以在上面的例子中,我们会有这样的 HQL:
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]'
还应该使用一些 SELECT 以及可能带有结果转换器的 DTO...
这篇关于使用 nhibernate 和 ICriteria 连接两个不相关的视图表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!