NHibernate - Query by Example - Subquery

Question

Hello, I know there are MANY ways to achieve the following in NHiberante - i am not interested in other scenarios. What I am wondering is if it is possible to automatically generate a subquery using a many-to-one relationship and a query by example in NHibernate.

My setup is as follows:

public class Customer
{
    public virtual int ID { get; set; }
    public virtual string Firstname { get; set; }
    public virtual string Lastname { get; set; }
    public virtual IList<Orders> CustomerOrders { get; set;}
}

My mapping looks something like the following:

<property name="FirstName" type="string" length="200" not-null="true"/>
<property name="Lastname " type="string" length="20" not-null="true"/>


<bag name="CustomerOrders" cascade="save-update" inverse="true">
  <key column="CustomerID" on-delete="cascade" />
  <one-to-many class="myassembly.CustomerOrders, myassembly" />
</bag>

A straightforward QBE would give FirstName and/or LastName a value and return an IList containing Customers who have that first and/or last name.

What I would like to do is add orders to the CustomerOrders list resulting in a query like the following:

select * from CustomerOrders c where FirstName = 'myfirstname' and LastName = 'mylastName' inner join Orders o on c.CustomerID = o.OrderID
where o.OrderName = 'myorderName' and o.OrderNumber = 'myordernumber'

In the above example OrderName and OrderNumber are properties of the Order Class (in the interest of brevity i have not included this class). Does anyone know if this is possible? Please let me know if I need to clarify my question. Essentially I am able to populate primitive values and have them included in the QBE. Can I do the same with collections? Thanks Jason

Answer 1

I'm not sure I understand ....

When you do this :

using (ITransaction tx = session.BeginTransaction())
{
     IList<Customer> customers =  
                 session.CreateCriteria(typeof(Customer))
                .Add(Expression.Eq("FirstName", "MyFirstName"))
                .Add(Expression.Eq("LastName", "MyLastName"))
                .List<Customer>();

     customers[0].CustomerOrders.Add(new Orders());

     tx.Commit();
}

Here it's not QBE but criteria API but you can change.