Hi, I have the following scenario. There is a MainTable, along with RelationTable1, RelationTable2, RelationTable3, etc. On MainTable, there is a relation with those other tables via foreign keys, in other words there are fields like RelationTable1_ID, RelationTable2_ID, etc. Additionally, each RelationTable has a unique Code field (these are simple read-only lookup tables).
When creating a new MainTable entity, I need to populate those relation fields. One way is via the FK, where I already know the key value and just set the RelationTableX_ID field. Unfortunately, the application is not aware of specific keys (they are just ints), but does know specific codes. So what I could do is perform a GetScalar on each of those tables, retrieve the PK for the specified code and put that into the new MainTable entity.
That of course results in many roundtrips to the database. Is there a way to do this in one step? Something like..
MainTableEntity.RelationTable1 = new RelationTable1Entity();
MainTableEntity.RelationTable1.Code = SOME_CODE;
and then somehow there will be a fetch using the unique key DURING the insert and the RelationTable1_ID will be populated automagically?
The only way to do a single roundtrip that I see is via a stored proc. Is there a way to make the number of roundtrips less than # of FKs + 1 (one select to get the PK_ID for each RelationTable + 1 save for the MainTable entity)?
Thanks!
P.S. I'm using adapter.
