Make a SQL Query to Nhibernate

Question

How do I get this SQL query to Nhibernate?

SELECT Customer.name
FROM Company INNER JOIN Customer ON Company.CompanyId = Customer.CompanyId
where CompanyId = 2

Answer 1

If you are familiar with LINQ it is very very simple,
you have to directly access the reference filed just as an entity. and you will get the properties of that entity and so on you can access till the nth node.

Nhibernate will take care of reference fields as entities..

        //Here is the sample this may work 
        //CustomerList is a nhibernate entity list.
        //CompanyId is also an entity which is a reference to the CompanyId of Company table.
        // you will get the list of customers based on condition. 
        var CustomerList = new List<Customer>();
        var custList = from cust in CustomerList where cust.CompanyId.CompanyId == 2 select cust;

Answer 2

Assuming you've got a company which represents the company with ID 2. You can use an ICriterion:

return this.GetSession().CreateCriteria<Customer>()
    .Add(Restrictions.Eq("Company", company))
    .List<Customer>();

This will return a list of the Customers associated with the company (assuming the property on the Customer class is called "Company").

To get the names just do:

customers.Select(c => c.name);

I'd suggest this approach as you know all the customers will be loaded in one db hit rather than lazily loading them one at a time.