Subquery entry in nHibernate criteria

Question

I read about the subquery in the criteria, but I still can't get it right. Here I take one example, and if someone can help me write that using a subquery will be great.

Let's say we have a table

Employee{EmployeeId.(int),Name(string),Post(string),No_Of_years_working(int)} 

Now I want all employees to be managers and work less than 10 years. I know that we can get the result without using subqueries, but I want to use a subquery to understand how it works in the criteria.

So, how can I write criteria using a subquery to get these employees.

Answer 1

Well, the code should be something like this:

 DetachedCriteria dc = DetachedCriteria.For<Employee>() .Add (Subqueries.PropertyIn("EmployeeId", DetachedCriteria.For<Employee>() .SetProjection(Projections.Property("EmployeeId")) .Add(Restrictions.Lt("No_Of_years_working", 10)) .Add(Restrictions.Eq("Post", "Manager")) ); 

Hope this helps.

Answer 2

I tried to accomplish something similar to the Bipul task when I found this question, so I basically got the idea of ​​bernhardrusch's answer, but I realized that without adding Projections.projectionList the subquery does not work. So I decided to drop a few lines of code with the final version:

 Session session; //You get the session according with your app logic //Let define first the subquery DetachedCriteria sub = DetachedCriteria.forClass(Employee.class); sub.add( Restrictions.lt("No_Of_years_working", 10) ); sub.add( Restrictions.eq("Post", "Manager") ); sub.setProjection( Projections.projectionList().add( Projections.property("EmployeeId") ) ); //Now the main query Criteria criteria = session.createCriteria(Employee.class); criteria.add( Property.forName("EmployeeId").in(sub) );