O/R Mapping - Creating a hierarchy within the objects to use dot notation

Question

Hi,

I'm working on a project requiring some basic O/R mapping and I'm wondering what the best/recommended practice is for establishing a hierarchy and grouping of values that have been mapped from the database so that i can use dot notation. An example of pseudo-data and how i want to map it will help explain:

I have the following database table structure:

Table: metric_values (this is the main table that holds the data that is mapped to objects)

security_id      metric_id      value

1                    12                34.23
1                    13                32.21
1                    14                54.56
.
.
.
2                    13                12.32
2                    14                102.32
2                    15                54.84
2                    16                523.12
.
.
.

Table: metric_details

id     metric_id     metric_type_id      metric_name
1      13              1                          Revenue
2      14              1                          Net Debt
3      15              2                          Revenue
4      16              2                          Net Debt
.
.
.

Table: metric_type_details

 

id      metric_type_id      metric_type_name
1       1                         Fiscal Year Data
2       2                         Calendar Year Data
.
.
.

To map this, i have a Security Class with fields and properties for each metric_id. I'm mapping metric_ids to properties using reflection and attributes as described in this post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2741007&SiteID=1).

Within the Security object i will have fields for both metric_ids that have a metric_type_id of 1 (i.e. Fiscal Raw Data) and and metric_ids that have a metric_type_id of 2 (i.e. Calendar Raw Data).

As the pseudo-data shows records with metric_ids 13 and 15 both correspond to Revenue but 13 has a metric_type_id of 1 and so is "Fiscal Year Data" whereas 15 has a metric_type_id of 2 so is "Calendar Year Data".

What i want, is to create this separation in my object so that if i want "Fiscal Year Data" i can type:

this.FiscalData.Revenue

and if i want "Calendar Year Data" i can type:

this.CalendarData.Revenue

Data which is then calculated within C# (and subsequently persisted to the database) will be stored in the object in a similar manner. i.e.

this.CalculatedData.Whatever

What i'm therefore needing help with, is creating this kind of hierarchy within my objects. I know it's possible to create FiscalData and CalendarData classes with the same fields and properties within each (obviously mapping to different underlying metric_ids) and then instantiate FiscalData and CalendarData objects within the Security class but that doesn't seem like a good way of doing this since, within the context of the data, a FiscalData or CalendarData object doesn't make make much sense form an OO point of view. Another alternative is nested classes but that pretty much the same thing.

I also don't think collections within the Security Class for FiscalData and CalendarData would work since i would then have to type something like:

this.FiscalData["Revenue"]

which is not what i want. When i type "this.FiscalData." i want IntelliSense to give me a list of fields.

I also don't need the complexity of a full blown OR Mapper like NHibernate or LLBLGen.

So... any ideas how i achieve this.

Many thanks.

M.