(Felt quite helpless in formulating an appropriate title...)
In my C# app I display a list of "A" objects, along with some properties of their associated "B" objects and properties of B's associated "C" objects:
A.Name B.Name B.SomeValue C.Name
Foo Bar 123 HelloWorld
Bar Hello 432 World
...
To clarify: A has an FK to B, B has an FK to C. (Such as, e.g. BankAccount - Person - Company).
I have tried two approaches to load these properties from the database (using NHibernate): A fast approach and a clean approach. My eventual question is how to do a fast & clean approach.
Fast approach:
Define a view in the database which joins A, B, C and provides all these fields.
In the A class, define properties "BName", "BSomeValue", "CName"
Define a hibernate mapping between A and the View, whereas the needed B and C properties are mapped with update="false" insert="false" and do actually stem from B and C tables, but Hibernate is not aware of that since it uses the view.
This way, the listing only loads one object per "A" record, which is quite fast. If the code tries to access the actual associated property, "A.B", I issue another HQL query to get B, set the property and update the faked BName and BSomeValue properties as well.
Clean approach:
There is no view.
Class A is mapped to table A, B to B, C to C.
When loading the list of A, I do a double left-join-fetch to get B and C as well:
from A a left join fetch a.B left join fetch a.B.C
B.Name, B.SomeValue and C.Name are accessed through the eagerly loaded associations.
The disadvantage of this approach is that it gets slower and takes more memory, since it needs to created and map 3 objects per "A" record: An A, B, and C object each.
Fast and clean approach:
I feel somehow uncomfortable using a database view that hides a join and treat that in NHibernate as if it was a table. So I would like to do something like:
Have no views in the database.
Declare properties "BName", "BSomeValue", "CName" in class "A".
Define the mapping for A such that NHibernate fetches A and these properties together using a join SQL query as a database view would do.
The mapping should still allow for defining lazy many-to-one associations for getting A.B.C
My questions:
Is this possible?
Is it [un]artful?
Is there a better way?
