NHibernate选择查询

Question

我有以下问题:

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 session = NHibernateSession.Current;
var subquery = session.Query<Category>
    .Select(x => x.ParentCategory.Id);
return session.Query<Category>
    .Where(x => !subquery.Contains(x.Id))
    .ToList();

Answer 2

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;