NHibernate - Joining by ID property, not object

Question

I have the following tables :

Customer(ID, Name)
Transaction(ID, CustomerID, Date)

The mapping objects for the tables are defined without objects, only primitive types :

Customer(int ID, string Name)
Transaction(int ID, string CustomerID, DateTime Date)

And I want to select all the customers and their last purchase date in ascending order. I tried using aliases and creating a join with customer Id and transaction's customer Id but I get an exception saying

could not resolve property: customer of: Transaction

Transaction transaction = null;
Customer customer = null;

session.QueryOver<Transaction>(() => transaction).
                SelectList(list => list.
                    Select(() => customer.Name).
                    SelectGroup(() => transaction.CustomerId).
                    SelectMin(() => transaction.Date)).
                JoinQueryOver(() => customer).Where(() => customer.Id == transaction.Id).
                OrderBy(Projections.Min<Transaction>(trans => trans.Date)).Asc.
                List<object[]>().
                ToList();

I don't understand why it's trying to look for a customer property in transaction, the only case they togheter in a line is in the where clause. I probably didn't quite get the Join that well.

Can anybody shed some light on this exception?

I would say that the answer is here http://stackoverflow.com/q/20528760/1679310 — Radim Köhler, Nov 18 '15 at 05:42

score 0 · Answer 1 · answered Nov 24 '15 at 14:48

The source of the error seems to be this call: JoinQueryOver(() => customer) It tells NHibernate to try and find the property named 'customer' of your Transaction class.

I believe your query should look something like this:

session.QueryOver<Transaction>(() => transaction).
SelectList(list => list.
    SelectSubQuery(QueryOver.Of<Customer>().Where(c => c.ID == transaction.ID).Select(c => c.Name)).
    SelectGroup(() => transaction.CustomerId).
    SelectMin(() => transaction.Date)
).
.OrderBy(Projections.Min<Transaction>(trans => trans.Date)).Asc.
List<object[]>().
ToList();

NHibernate - Joining by ID property, not object

1 Answers1