NHibernate Return Values

Question

Hi, I am currently working on a project using NHiberate as the DAL with .NET 2.0 and NHibernate 2.2.

Today I came to a point where I had to join a bunch of entities/collections to get what I want. That is fine.

What got me was that I do not want the query to return a list of objects of a certain entity type but rather the result would include various properties from different entities.

The following query is not what I am doing but it is kind of query that I am talking about here.

select order.id, sum(price.amount), count(item)
from Order as order
    join order.lineItems as item
    join item.product as product,
    Catalog as catalog
    join catalog.prices as price
where order.paid = false
    and order.customer = :customer
    and price.product = product
    and catalog.effectiveDate < sysdate
    and catalog.effectiveDate >= all (
        select cat.effectiveDate
        from Catalog as cat
        where cat.effectiveDate < sysdate
    )
group by order
having sum(price.amount) > :minAmount
order by sum(price.amount) desc

My question is, in this case what type result is supposed to be returned? It is certainly not of type Order, neither is of type LineItems.

Thanks for your help!

John

Answer 1

you can always use List of object[] for returning data and it will work fine.

Answer 2

This is called a projection, and it happens any time you specify an explicit select clause that contains rows from various tables (or even aggregate / summary data from a single table).

Using LINQ you can create anonymous objects to store these rows of data, like this:

var crunchies = (from foo in bar
                where foo.baz == quux
                select new { foo.corge, foo.grault }).ToList();

Then you can do crunchies[0].corge for example to pull out the rows & columns.

If you are using NHibernate.Linq this will "just work".

If you're using HQL or Criteria API, then what Fahad mentioned will work. You'll get a List<object[]> as a result, and the index of the array references the order of the columns that you returned in your select clause.