Comparison with count in inner subquery (NHibernate QueryOver API)

Question

I have the following model:

create table Products (
    Id UNIQUEIDENTIFIER not null,
   Name NVARCHAR(255) null,
   CategoryId UNIQUEIDENTIFIER not null,
   primary key (Id),
  unique (Name, CategoryId)
)

create table Rates (
    Id UNIQUEIDENTIFIER not null,
   Price NUMERIC(19,5) not null,
   TimeStamp DATETIME not null,
   UserId UNIQUEIDENTIFIER not null,
   ProductId UNIQUEIDENTIFIER not null,
   primary key (Id),
  unique (Price, UserId, ProductId)
)

create table Categories (
    Id UNIQUEIDENTIFIER not null,
   Name NVARCHAR(255) not null unique,
   primary key (Id)
)

EDIT: The domain model is as follows:

public class Category
{
    public string Name { get; set; }
    public IList<Product> Products { get; set; }
    public Guid Id { get; set; }
}

public class Product
{
    public string Name { get; set; }
    public Category Category { get; set; }
    public IList<Rate> Rates { get; set; }
    public Guid Id { get; set; }
}

public class Rate
{
    public User User { get; set; }
    public Product Product { get; set; }
    public decimal Price { get; set; }
    public DateTime TimeStamp { get; set; }
    public Guid Id { get; set; }
}

What I want to do is to select all the categories that do not have associated Products with associated Rates. I.e. in SQL this would look like:

select * from Categories category where
    (select count(*)
     from Products product 
     inner join Rates rate on rate.ProductId = product.Id 
     where product.CategoryId = category.Id) = 0;

How can I do this using QueryOver API?

I don't see any domain-model; I only see your database-model. To be able to answer your question, we'll have to know how the classes look like. (Next to that, I thinkyour SQL query is wrong). — Frederik Gheysels, Apr 01 '12 at 19:04

score 0 · Answer 1 · answered Apr 02 '12 at 16:41

I can't check generated sql, but you can try this, maybe it's help:

Product product = null;
Rate rate = null;

_session.QueryOver<Category>()
  .JoinAlias(category => category.Products, () => product)
  .JoinAlias(() => product.Rate, () => rate)
  .Where(Restrictions.Eq(Projections.Count(() => product.Id), 0)
  .List<Category>();

score 0 · Accepted Answer · edited May 23 '17 at 10:34

The solution is found:

Category categoryAlias = null;
session.QueryOver<Category>(() => categoryAlias)
    .WithSubquery
    .WhereNotExists(QueryOver.Of<Product>()
        .Where(product => product.Category.Id == categoryAlias.Id)
        .JoinQueryOver<Rate>(product => product.Rates)
        .Select(product => product.Category))
    .List<Category>();

Thanks to https://stackoverflow.com/a/5196609/359730.

Comparison with count in inner subquery (NHibernate QueryOver API)

2 Answers2