Need help with query

jnesbitt · **Joined:** Tue Sep 18, 2007 10:35 am **Posts:** 8

I have a many to many relationship between Articles and Categories which is expressed as a bi directional relationship in code. I'd like to query for all articles except the ones that are related to certain categories. My first inclination is to loop through the excluded categories and build the HQL query string like "category.Id not in (1, 3, 6, ..)" but I'd prefer to use a parametrized query if possible. I'm having trouble figuring out how to express this in HQL or query by criteria. Anyone know how to do this?

Thanks

wolli · **Posted:** Mon Feb 02, 2009 6:03 am

I think this should work:

Code:

IQuery query = session.CreateQuery("from Articles a join fetch a.Categories c where c in (:CategoryList).SetParameter(categoryList);

categoryList is a List<Category> or Category[] or something else that is IEnumerabe. If you only have the ids available you can change the query to "c.id in (:categoryIdList)" and pass a list of ids.

jnesbitt · **Joined:** Tue Sep 18, 2007 10:35 am **Posts:** 8

Couple of things. I want to find all the articles that are NOT related to certain categories so maybe I need to use "not in" instead of "in"?

Also, I get an error when I run this query saying that NHibernate cannot resolve the property Categories on the object Article even though there is one. Categories is a read only IEnumerable<Category> backed by a field saved by field access. Does there need to be a setter for this to work?

wolli · **Posted:** Mon Feb 02, 2009 2:13 pm

If you have field access, you have to use the field name, not the property name. Probably:

Code:

IQuery query = session.CreateQuery("from Articles a join fetch a.categories c where c in (:CategoryList).SetParameter(categoryList);

And you need "not in", of course. Sorry.

jnesbitt · **Joined:** Tue Sep 18, 2007 10:35 am **Posts:** 8

Thank you so much. One more question.

How would I modify the query so that only distinct results are returned? As is, I get a lot of duplicates.

wolli · **Posted:** Mon Feb 02, 2009 4:37 pm

You can try

"select distinct a from Articles a ..."

or

query.SetResultTransformer( new DistinctRootEntityResultTransformer() )

You're welcome.