Fluent Nhibernate mapping a property from another table

Question

Hello, I'm having a problem mapping a column in table A to a property of a class, which is primarily mapped to table B. The following explains this better:

There's a class CustomerRisk, which has properties Risk and CustomerNumber.

In the database, this consists of two tables: Customer, which has a column CustomerNumber, and CustomerRisk, which has a Foreign Key to Customer, and one to Risk.

Mapping the Risk isn't hard, but mapping the CustomerNumber is the problem. Is it possible to do this without creating a Customer class*? So that NHibernate joins to the Customer class to select the CustomerNumber:

select cn.CustomerNumber, r.Description
from CustomerRisk cr
    inner join Customer c on c.CustomerID = cr.CustomerID
    inner join Risk r on r.RiskID = cr.RiskID

Multiple CustomerRisk records can have the same CustomerID of course.

Hoping this is a little clear, thank a lot.

• I know you'd normally need a customer class, but I've 'anonymized' the classes.

Answer 1

This appears to be the same as this question.

Answer 2

One thing to keep in mind when working with NHibernate is that it deals with entities and only knows what you've told it (through the mapping files). HQL is an object-oriented version of SQL so you can't use it to access fields that aren't part of your mapped domain model.

You can still execute plain old SQL through an NHibernate session though, which is really the only way to access data that you haven't mapped into your ORM. There might be a lightweight mapping solution (though I can't say without seeing your data model), but if you're against mapping the "Customer" completely, I think SQL is your best bet.