How can I map rows from three tables into one entity with NHibernate/FluentNHibernate?

I've come unstuck this particular problem as I'm still fresh to NHibernate/FluentNHibernate. I will describe the problem in detail but before I do I would like to mention that I am free to change both the domain model and the underlying database as I see fit. However I have specific reasons for my current design and would like to stick to it if I can.

I have a User entity. A User has some Preferences, such as their chosen culture ("en-US"/"fr-CA", etc) and their preferred conversion system (Metric/British Imperial, etc). There are other preferences as well, and further preferences can (and will) be added over time. I therefore do not want to represent preferences as columns on the underlying User table. Rather I want the table structure to look like this (pseudo SQL):

Table Preference (int Id, nvarchar(50) Description)
Table PreferenceOption (bigint Id, int PreferenceId, nvarchar(50) Value)
Table UserPreferences (int Id, uniqueidentifier UserId, bigint PreferenceOptionId)

One Preference (i.e. "Culture") can have multiple preference options (i.e. "en-GB", "fr-CA"). A user's preferences are held in the UserPreferences table which has a foreign key to the PreferenceOption table.

I would like my domain model to look like this:

public class User
{
  public virtual Guid {get;set;}
  public virtual Preferences {get;set;}
}

public class Preferences
{
  public virtual CultureInfo Culture{get;}
  public virtual ConversionType ConversionType {get;}
}

I imagined that I'd try and load all preferences belonging to 开发者_运维技巧a user into a collection (on the Preferences class) and let the properties do the respective conversion into CultureInfo and ConverstionType (a custom enum) respectively.

I'd be really grateful for any pointers on this.

I don't think NHibernate will be able to handle all the corner cases in this situation. What happens if you don't have a preference with the Id for CultureInfo in the PreferenceOption table? What if the Preference doesn't exist at all? How does it know to map CultureInfo to a specific row in the PreferenceOption table? (Is Description really a constant that describes which property to populate?)

I think you're asking for trouble. If you want to use a schema like this, I think you'll have better luck with a more direct mapping of your schema to your NHibernate POCOs. You can write some code to populate your Preferences object easily enough from the rows of the PreferenceOption table related to your user.

Frankly, if you already know that there will be some user preferences, add them to your schema. It's far easier to deal with in many situations (think reporting, etc - at least one less join and more determinism) than having a "dumping ground" table for essentially untyped, dynamic data.

If you think you'll need it, I'd leave the dumping ground in, in case of emergency. But if you have the flexibility to include the data you know you need in your schema during this point in your development, do that instead.