How do IN () statements work in NHibernate? (Using Criteria)

Question

I'm trying to create the equivalent of the below using NHibernate. I've done all the mappings using fluent and I can do the basic queries just fine but I have no idea how to do this.

-**Product Table**
Reference
Title
Subjects (Many to Many relationship)
Price

-**Subject table**
SubjectID
Name

-**SubjectToProductMapping Table**
Reference
SubjectID

Now I need to do this:

SELECT * 
FROM Product
WHERE Reference IN 
    (Select Reference FROM SubjectToProductMapping WHERE SubjectID = @SubjectID)

Baring in mind the Product table has been simplified a great deal for the post and that I would prefer to use an IN statement to keep the rest of the query simpler. I would ideally like to create the query using Criteria becuase I will be using Criteria to page the results.

Thanks in advance

Answer 1

Check this article, it may help you to solve your problem: http://www.lostechies.com/blogs/jimmy_bogard/archive/2008/08/26/parameter-lists-in-nhibernate.aspx Hope this helps.

Answer 2

Why would you use an in when a join would suffice? provided your Products class has a mapped collection of subjects then you could just use this Criteria

IList<Product> results = session.CreateCriteria(typeof(Product))
                                .CreateCriteria("Subjects", JoinType.Join)
                                .Add(Resitctions.Eq(Projections.ID, subjectID))
                                .List<Product>();