Hello,

I am new to NHibernate and have two simple questions.

1. What is the easiest way to get a lookup value. I have have a persistent class, Patient that maps to tbl_Patients. tbl_Patients has an FK, PatientStatus_ID. Table tbl_PatientStatus is a simple lookup table with PatientStatus_ID as PK and PatientStatus to hold the value. I just want to get the value for Patient Status to come back with the the Patient - a simple join. All the examples I see are about mapping collections and whatnot. Is there just a simple way to map/get the lookup value? Do I have to use HQL?

2. If I just mapped PatientStatus_ID as a property of the Patient class, why do I get no results back?[/code]

1. You can use a <many-to-one> in your Patient mapping like this:
The .net type of this property need to be a PatientStatus class.

2. If you are mapping PatientStatus_ID as a plain old <property>, the .net type of your property needs to be an int or long depending on your db column type.

My suggestion if you go with #1 above is to map the <many-to-one> with the fetch="select" attribute in combination with second-level cache. That way, you are doing one less join and you are not fetching all the PatientStatus objects all the time.

Our application has about 80 of these lookup classes. We found that mapping the PatientStatus_ID column as a true .net enum works way better from a test-driven development perspective. We still have the likes of the PatientStatus classes mapped via NHibernate, but we only fetch it when we need the details from it. Majority of the time, we are doing something like this:

You situation will be different; but if you don't need the properties of the PatientStatus most of the time, then only mapping its ID to the Patient class is, in my opinion, a better way to go. Hope it helps.

Hi Karl,

Thank you for the help. I tried your first suggestion (partly) and it is working! I'm not quite sure what "in combination with second-level cache", so I'll go do some reading about it. Thanks again!

-- Chris

Say you have 20 different PatientStatuses rows in the database, if you specify fetch="select" and you load, say, 1000 Patients, you would end up making up to 21 database round trip: one for the list of Patients, and possibly one for each PatientStatus. If in a later (and different) session, you issue the same query again, you would make another 21 round trips to the database; 20 of which are to load the same PatientStatuses again. If you had the second-level-cache enabled, you would save yourself that 20 round trips the second time since NHibernate would look in the cache first. For something like static lookup data, the second-level-cache is one of the best ways to lighten the load on your database server.

Word of caution: if you use a cache provider that need to serialize your entities (i.e. Memcache and Prevalence), then your entities need to be Serializable. Also, the Memcache provider is a bit buggy with composite id.

Ah, yes. I just finished reading the help docs and specifying the cache provider in my config file and the cache usage in my PatientStatus mapping. All working well at this point. Thanks for helping me out!

-- Chris