I’d have no problem doing this in SQL but I’m relatively new to ORM.
Simplifying a bit, this is the basic schema I’m working with (four tables):
Events -|-o< Registrations -|-o< FunctionRegistrations >o-|- Functions
o-|- Events
The sort column is on Functions- so functions are sorted within a
particular event.
People register for the Event (Registrations) and register for functions
within the event (RegistrationFunctions).
I am looking at one specific Registration, one person for one event.
What I want to get here is a list of functions for this particular
registration, sorted correctly. The sort column is in Functions. I then
need to also get information related to that specific function registration
(like the price paid, which is in FunctionRegistrations).
Looking at a given registration, I have tried:
-
a one-to-many relationship with FunctionRegistrations. This lets me get
the functions and the price, but I can’t sort the functions correctly as
the sort column is in the next table over. -
a many-to-many relationship between Registrations and Functions, with
FunctionRegistrations as the link table and an orderby=“sort”. This gets me
my functions in the right order but I am not clear then on how I can get
information like the price out of the link table. I tried defining a
one-to-many relationship back from Functions to FunctionRegistrations but
this then gets me all the registrations. Which is sort of what you’d expect
all right but not what I need!
Do I need to use HQL to do this, or can I get the default sort order right
in the model so I can just use EntityLoad()?