CODEWITHSUNDEEP
c#nhibernate
setlio
setlio

Reputation: 726

Where not exists with Nhibernate

How do I write a NHibernate criteria matching that query? 

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')

I have written this, but not exists takes only one argument!

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

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

Thanks a lot for the help in advance

Upvotes: 2

Views: 1308

Answers (1)

Radim Köhler
Radim Köhler

Reputation: 123861

We can use aliases, nicknames to get outer table in the subquery scope. So, if we will (later) name the criteria "myAlias", we can extend the subQuery defintion this way:

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"));

And adjusted criteria:

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

Upvotes: 2

Related Questions