.net - NHibernate Mapping a one-to-many relationship with the many coming from multiple sources -

background

i have been working on tool supposed work in conjuction legacy application, , have hit small brick wall. have spent last day or searching clues this, have had no luck.

i trying map relationship between table , multiple other tables follows:

the parent table represents "dictionary." dictionary list of database fields can used in ad-hoc records queries in (horrible) legacy application. each record holds table , column item can found, unique identfying "friendly" name, key use in global value lookup table, , flag determines how lookup values pulled, , text column defines sql run obtain lookup data if flag value set appropriately. table name [dictionary] (see below table structure.)

the flag can have 1 of 4 values:

• freeform
• standard
• system
• dynamic

if flag set freeform, end user can enter value want. if other value, value must picked list follows:

1. standard: legacy application pulls values "union all" query of 2 tables difference 1 table fiscal-year based, , other "global" value table. these tables hold lookup values "standard" flagged dictionary records. these 2 tables called [fy_lookup_values] , [lookup_values] respectively (see below table structure)
2. system: if friendly name in dictionary table "state" pulls list of states in [state] table; if country, same [country] table (see below table structure)
3. dynamic: lookup values populated based on query dynamic sql field described above in dictionary table. these queries not alias selected columns names similar column names other 2 lookup types above. provide example of 1 of many tables used in these queries below.

table structures

table [dictionary]     token int not null identity primary key     name varchar(10) not null     table_name varchar(50) not null     column_name varchar(30) not null     lookup_key varchar(10) not null     lookup_type varchar(8) not null     query_text text  table [lookup_values]     lookup_key varchar(10) not null primary key     lookup_value varchar(20) not null primary key     lookup_description text not null  table [fy_lookup_values]     lookup_key varchar(10) not null primary key     lookup_value varchar(20) not null primary key     lookup_description text not null     fy_year_token int not null  table [state]     state_code varchar(4) not null primary key     state_name varchar(30) not null  table [country]     country_code varchar(4) not null primary key     country_name varchar(50) not null  table [banks]     bank_token int not null identity primary key     bank_name varchar(50) not null 

all of lookup value retreivals in application grab 2 columns, code , description. legacy application massages incoming data based on column position, not name.

i cannot touch database structure (even add store procedure), nor can alter way lookups done, these used few legacy applications , changes make management unhappy... wish could, 1 of many things makes hair gray when comes application. so...

my main question, guess, if possible map these in such way nhibernate when grab dictionary item, it's lookup values filled in me? if possible, how? know if of lookups done in same way, could, don't know if possible map based on externally obtained queries.

i hope makes sense, took me while wrap brain around it.

edit here examples of trying accomplish.

we have following records in [dictionary] table:

token, name     , table_name, column_name, lookup_key, lookup_type, query_text 1    , gender   , customer  , gender     , gender    , standard   , null 2    , addr_st  , customer  , addr_st    , state     , system     , null 3    , acct_type, cust_accts, type_code  , acct_type , dynamic    , select type_code, descr acct_types active = 1 

so, in legacy application, when pulled up, here sql executed pull lookup values.

gender:

select lookup_value,        lookup_description   lookup_values union select lookup_value,        lookup_description   lookup_values  fy_year_token = @p1 

address state:

select state_code,        state_name   state 

account type:

select type_code,        descr   acct_types  active = 1 

you may able using subclass strategy. lookups can inherit abstract class called lookup, can map using table per concrete class strategy. dictionary object can have collection of lookups using appropriate keys.

due complex fetching strategy need implement kind of custom loader each of lookups. caveat loader used load fetching. if want write hql or criteria query against these lookups go against table , mapping. however, don't need that.