How to make disjoined NHibernate sub-query/criteria fetch

tomage · **Joined:** Thu Dec 10, 2009 6:20 am **Posts:** 2

Hi!

I´m having slight problems with NHibernate in C#. I'll submit a really simplified version of my model to explain:

- Case object: Describes a task/objective for a customer, handled by employee - but lets skip the employee object for now.
- Customer object: Contains information of a customer

The Case table contains foreign key reference to both Customer and Employee table.
The probem is that I need to create select statement like so (third line the most important):

Code:

SELECT * FROM Case, Customer, Employee
WHERE Case.id_customer = Customer.id AND case.state = "Open"
AND ( Customer.name = "Pete" OR Customer.name = "Diane")

I.e. I want to create subcriteria/subquery on the Customer object of the Case object.

This should be a fairly simple query to make, but somehow I cannot do it usnig the Criteria API.

I've already invested some time using the Criteria API for other things, so I'd rather not fall back to HQL..

Any ideas or solutions?

Here is a code snippet for the retrieval:

Code:

var crit = Current.CreateCriteria(typeof (Case));
var disj = new Disjunction();
disj.Add(Restrictions.Eq("State", "Open"));
// ... add the customer subqueries ...
crit.Add(disj);
var list = crit.List();

cheers,
Tómas

wolli · **Posted:** Thu Dec 10, 2009 9:16 am

Depending on your mapping it should be something like this:

var crit = Current.CreateCriteria(typeof (Case))
.Add(Restrictions.Eq("State", "Open"
.CreateCriteria("Customer", "c")
.Add(Restrictions.In("c.Name", listOfNames );

tomage · **Joined:** Thu Dec 10, 2009 6:20 am **Posts:** 2

wolli wrote:

Depending on your mapping it should be something like this:

var crit = Current.CreateCriteria(typeof (Case))
.Add(Restrictions.Eq("State", "Open"
.CreateCriteria("Customer", "c")
.Add(Restrictions.In("c.Name", listOfNames );

I should've described the problem a bit better.. Your solutions surely is correct, but the reason for wanting the "or" between was a bit more complicated. I wanted to do something like:

Code:

(State = "Open" AND Custmer.Name = "Pete")
OR (State = "Open" AND Custmer.Name = "Diane")
OR (State = "Closed" AND Customer.Age > 20)
OR ... etc., etc. ...

So an "In" restriction would not have satisfied. Thanks a bunch though..

I already formed a solution myself, once I accepted that a single alias could be used in more general sense than I first thought... Here is my solution (the code still is simplification for what I really need to do - but the real code is like 100 lines long, so I won't post it..besides I am not permitted to):

Code:

var crit = Current.CreateCriteria(typeof (Case));
var disj = new Disjunction();
disj.Add(Restrictions.Eq("State", "Open"));
crit.CreateAlias("Customer", "CustomerAlias");
disj.Add(Restrictions.Eq("CustomerAlias.Name", "Pete");
disj.Add(Restrictions.Eq("CustomerAlias.Name", "Diane");
crit.Add(disj);
var list = crit.List();

First, I thought I had to create many aliases for each new restriction, e.g. CustomerAlias1, CustomerAlias2 etc.

Thanks a bunch though..