CODEWITHSUNDEEP
nhibernatequeryover
Jørn Schou-Rode
Jørn Schou-Rode

Reputation: 38346

Implementing "where not exists" with NHibernate QueryOver

Using the new QueryOver API in NHibernate, I need to do something equivalent of:

select c.*
from Category c
where not exists (
    select *
    from CategoryProduct cp
    where cp.CategoryID = c.Id
    and cp.ProductID = 'DogFood'
)

In other words: "Give me all categories that doesn't contain dog food".

My initial thought was something like:

IEnumerable<Category> FindCategoriesWithoutProduct(Product product)
{
    return _session
        .QueryOver<Category>()
        .Where(c => c.Products.Contains(product))
        .List();
}

However, this makes NHibernate.Impl.ExpressionProcessor blow up with an "unrecognised method call" on System.Collections.Generic.ICollection<T>.Contains().

I assume there must be some other way to do this, probably involving an ICriterion, but my searches here and on Google have returned nothing useful.

Upvotes: 2

Views: 5532

Answers (2)

vllado2
vllado2

Reputation: 180

I'm just running in the same problem, and possible solution is:

Category aliasCategory = null;
CategoryProduct aliasCategoryProduct = null;

var qcp = QueryOver.Of<CategoryProduct>(() => aliasCategoryProduct)
          .Where(() => aliasCategoryProduct.ProductID == "DogFood")
          .Where(() => aliasCategory.Id == aliasCategoryProduct.CategoryID)
          .DetachedCriteria;

return _session.QueryOver<Category>(() => aliasCategory)
               .Where(Subqueries.NotExists(qcp));

It works on my similar criteria.

Upvotes: 5

Craig
Craig

Reputation: 36836

Not tested, but something like this

IEnumerable<Category> FindCategoriesWithoutProduct(Product product)
{
    CategoryProduct categoryProductAlias = null;
    var subQuery = QueryOver.Of<CategoryProduct>(() => categoryProductAlias)   
        .Select(x => categryProductAlias.ID)
        .Where(() => categoryProductAlias.ProductID == "DogFood");

    Category categoryAlias = null;
    return _session
        .QueryOver<Category>(() => categoryAlias)
        .WithSubquery.WhereProperty(() => clientAlias.Id).NotIn(subQuery)
        .List();
}

Upvotes: 2

Related Questions