将集合查询从 SQL 转换为 nhibernate

Question

我有一个数据库架构，其中有一个 Product、Category、CategoryFeature 和 ProductCategoryFeatureValue。

该模型是使用 Fluent NHibernate 映射的，但基本上如下。

Product
-------
ID
Title

Category
--------
ID
Title

CategoryFeature
---------------
ID
CategoryID
Title

ProductCategoryFeatureValue
---------------
ID
ProductID
CategoryFeatureID
_______________________

Category [one] <-> [many] CategoryFeature
Product [many] <-> [many] ProductCategoryFeatureValue

基本上，产品可用的功能都列在 ProductCategoryFeatureValue 表中，该表是多对多集合的“中间链接”。

我需要创建一个查询，在其中可以找到具有用户选择的所有功能的所有产品。

例如，搜索 ID 为 643229 和 643229 的两个要素。 667811 在 SQL 术语中，我会做这样的事情：

SELECT * FROM Product 
JOIN ProductCategoryFeatureValue AS feature1 ON Product.id = feature1.ProductID AND     feature1.categoryfeatureid = 643229
JOIN productcategoryfeaturevalue AS feature2 ON Product.id = feature2.ProductID AND feature2.categoryfeatureid = 667811

我可以做的另一个查询是这样的：

SELECT * FROM product WHERE 
((SELECT id FROM productcategoryfeaturevalue AS feature1 WHERE feature1.ItemGroupID = product.id AND feature1.categoryFeatureID = 643229 LIMIT 1) IS     NOT NULL)
AND 
((SELECT id FROM productcategoryfeaturevalue AS feature2 WHERE feature2.ItemGroupID = product.id AND feature2.categoryFeatureID = 667811 LIMIT 1) IS NOT NULL)

两者都经过测试并且运行良好。但是，我似乎无法使用 NHibernate 重现它们。有什么想法吗？

谢谢！

Answer 1

我相信您希望在 SQL 中实现类似的功能，

Select *
from Products p
where p.id in (
   select fv.ProductId
   from ProductCategoryFeatureValue fv
   where fv.CategoryFeatureID in (643229,643230)
   group by fv.ProductId
   having count(*)=@NumberOfDistinctFeaturesSelected 
)

这样您就不必为用户选择的每个功能多次 JOIN 到 ProductCategoryFeatureValue 表。至少你会得到一个更好的查询计划。如果您不喜欢 IN 子句，您也可以使用临时表。

就将其转换为 NHibernate 而言，它不支持 Criteria API 中的任何 HAVING 子句逻辑，但使用 HQL 支持。

HQL 示例

var results = session.CreateQuery("from Product p where p.Id in (
    select fv.Product.Id
    from ProductCategoryFeatureValue fv
    where fv.CategoryFeature.Id in :featureids
    group by fv.Product.Id
    having count(fv)=:features
)")
.SetParameter("featureids", arrayOfFeatureIds)
.SetParameter("features", arrayOfFeatureIds.Count)
.List<Product>();

Answer 2

从问题中不能 100% 确定您的映射到底是什么，但这可能接近您需要的

object[] featureIds = new object[2];

featureIds[0] = 643229;
featureIds[1] = 667811;

ICriteria criteria = base.CreateCriteria(typeof(Product));
criteria.CreateAlias("ProductCategoryFeatureValueList", 
"ProductCategoryFeatureValue", JoinType.InnerJoin);
criteria.CreateAlias("ProductCategoryFeatureValue.CategoryFeatureID", 
"CategoryFeature", JoinType.InnerJoin);

criteria.Add(Expression.In("CategoryFeature.ID", featureIds));

如果“Expression.In”不完全符合您的要求，您可以快速循环添加

criteria.Add(Expression.Eq("CategoryFeature.ID", featureIds[i]));

Answer 3

我认为你最大的问题是在连接上添加条件。我还没有尝试过，但一直期待 NH 3.+ 的功能，它可以让您添加条件加入。

CreateAlias（字符串关联路径，字符串别名，JoinType joinType，ICriterion withClause）CreateCriteria（字符串关联路径，字符串别名，JoinType joinType，ICriterion withClause）