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();
}

Najera · Accepted Answer · 2014-03-10 18:30:58Z

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

answered Mar 10, 2014 at 18:30

Najera

2,8793 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Radim Köhler Over a year ago

This seems to be a brilliant answer!

Onur Gumus · Accepted Answer · 2014-03-10 18:37:23Z

0

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.

answered Mar 10, 2014 at 18:37

Onur Gumus

1,44913 silver badges29 bronze badges

Collectives™ on Stack Overflow

NHibernate select query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related