NHibernate select query

Question

I have the following problem:

public IList<Category> GetLeafCategories()
{
    // select all categories that are not ParentCategory
    string sqlQuery =  @"
    select * from Catalog_Categories c
    where c.CategoryId not in (
        select distinct ParentCategoryId 
        from Catalog_Categories)
    ";

    //
    // I need an equivalent nHibernate query
    //
    var categs = NHibernateSession.Current.Query<Category>();
    IQueryable<Category> leafCategs = from cat in categs
                                      where cat.Id not in // HOW TO???
                                          (from c in categs 
                                           select c.ParentCategory.Id)
                                        select cat;
    return leafCategs.ToList();
}

Answer 1

var categs = NHibernateSession.Current.Query<Category>();
IQueryable<Category> leafCategs = from cat in categs
                                  let parentIds = 
                                      (from c in categs 
                                       select c.ParentCategory.Id)
                                  where !parentIds.Any( p => p == cat.Id)
                                 select cat;

You can do it like above.

Answer 2

Im not tested this, but it should work:

var session = NHibernateSession.Current;

var subquery = session.Query<Category>
    .Select(x => x.ParentCategory.Id);

return session.Query<Category>
    .Where(x => !subquery.Contains(x.Id))
    .ToList();