NHibernate Criteria API 實現 EXISTS 子查詢

需求

查詢所有擁有訂單的客戶。HQL/SQL 寫法：

// HQL
from Customer c where exists (from Order o where o.Customer = c.Id)

// SQL
select c.* from Customer c where exists (select 1 from `Order` o where o.Customer = c.CustomerId)

如何用 `ICriteria` + `Restrictions` 實現？

解決方案：DetachedCriteria + Subqueries.PropertyExists

using NHibernate.Criterion;

// 主查詢
ICriteria criteria = session.CreateCriteria();

// 子查詢：關聯 Order，條件 Order.Customer = 外層 Customer.Id
DetachedCriteria orderSubquery = DetachedCriteria.For("o")
    .Add(Property.ForName("o.Customer").EqProperty("c.Id")); // 關鍵：關聯外層別名 "c"

// 套用 EXISTS
criteria.Add(Subqueries.Exists(orderSubquery));

// 執行
IList list = criteria.List();

關鍵點解析

1. `DetachedCriteria.For("o")`：建立離線子查詢，別名 `o`。
2. `Property.ForName("o.Customer").EqProperty("c.Id")`：**關聯外層查詢別名**。`c` 是主查詢隱含別名 (或用 `criteria.SetAlias("c")` 顯式指定)。這相當於 SQL `o.Customer = c.Id`。
3. `Subqueries.Exists(detachedCriteria)`**：產生 `EXISTS (SELECT ...)` 片段。
4. `Subqueries.NotExists(...)`**：對應 `NOT EXISTS`。

完整別名控制版本 (避免隱含別名衝突)

ICriteria criteria = session.CreateCriteria("c"); // 顯式別名 c
DetachedCriteria sub = DetachedCriteria.For("o")
    .Add(Property.ForName("o.Customer").EqProperty("c.Id"));
criteria.Add(Subqueries.Exists(sub));

替代方案：Join + Distinct (效能較好)

若資料量大，`EXISTS` 子查詢可能較慢。改用 `JOIN` + `DISTINCT_ROOT_ENTITY`：

ICriteria criteria = session.CreateCriteria("c")
    .CreateAlias("c.Orders", "o", JoinType.InnerJoin) // 需 Customer 映射有 Orders 集合
    .SetResultTransformer(CriteriaUtil.DistinctRootEntity); // 去重
// 等同：SELECT DISTINCT c.* FROM Customer c INNER JOIN Order o ON o.Customer = c.Id

參考資源

StackOverflow 討論：NHibernate CreateCriteria and EXISTS clause

結論

NHibernate Criteria 實現 EXISTS：**`DetachedCriteria` 定義子查詢 + `Property.EqProperty` 關聯外層別名 + `Subqueries.Exists()`**。記得顯式指定主查詢別名 (`CreateCriteria("c")`) 避免隱含別名問題。大資料量優先考慮 `JOIN` + `DistinctRootEntity`。