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.