views:

238

answers:

1

I'm trying to make what seems to be an advanced use of NHibernate with sql server functions.
I'm using NHibernate's ICriteria interface to supply paging, sorting and filtering for my listviews. one of business objects is an aggregation of items from 3 different tables. in order to do this aggregation in the DB I've used a transact-sql function, accepting parameters.
I'm using the IQuery interface returned by session.GetNamedQuery to invoke the function. but in order to use the paging/filtering/sorting code i'd like to use the ICriteria interface. in order to achieve that I considered:

  1. Opening a new transaction
  2. calling the function which will create a global temporary table (instead of returning the result as it does now)
  3. somehow alter the NHibernate mapping so it applies for the temporary table (not sure I can do that, also this has to be specific to the scope where I create the transaction...)
  4. run the query on the new table using the new mapping, using the ICriteria interface
  5. Delete the temporary table

so a number of questions:

  1. can you suggest an alternative?
  2. is it possible to replace the table in an NHibernate mapping on run time, locally for a specific code scope?
  3. how costly would it be to generate and dispose of the temporary table?
+1  A: 

Can you replace the function with a view? This view could aggregate the 3 tables, be mapped by NHibernate, and easily paged/sorted/filtered.

dotjoe
I could do that but it would hurt performance, since the join will be performed on all rows in the 3 tables, vs. the join I use in the function which is done for a specific foreign key. I hope to get around to supply more info soon, as suggested by @Paco, pardon my latency... thanks!
Yonatan Karni