c# - Nhibernate 不存在的地方

Question

如何编写匹配该查询的 NHibernate 条件？

select * from InventoryItems i
where not exists (select inventory_id from InventoryItemCategories c where i.id = c.inventory_id and c.Category_Id = '805cec1e-1d7b-4062-9427-a26d010f4fb3')

我已经写了这个，但不存在只需要一个参数!

DetachedCriteria detachedCriteria =
   DetachedCriteria.For(typeof (InventoryItemCategories))
        .SetProjection(Projections.Property("Catgory_Id"));

var criteria=Session.CreateCriteria(typeof(InventoryItem)).
Add(Subqueries.NotExists(inventoryCategoryId,detachedCriteria));

非常感谢提前的帮助

Answer 1

我们可以使用别名、昵称来获取子查询范围内的外表。因此，如果我们(稍后)将条件命名为 "myAlias"，我们可以这样扩展子查询定义:

var detachedCriteria = DetachedCriteria.For<InventoryItemCategories>()
    // here we have to use the C# property names instead of the column names
    .Add(Restrictions.EqProperty("InventoryId"  // local table c, col inventory_id
                               , "myAlias.ID")) // outer table i, col id
    .Add(Restrictions.Eq("CategoryId"           // local table c, col Category_Id   
                               , myGuid))       // the parameter passed alá '805ce...
    .SetProjection(Projections.Property("Catgory_Id"));

并调整标准:

// outer table nickname "myAlias" here 
var criteria = session.CreateCriteria<InventoryItem>("myAlias"); 
criteria.Add(Subqueries.NotExists(detachedCriteria ));