So I have two entities, ProductPrice and Currency. ProductPrice has a Currency property.

I'm able to map a stored procedure to populate a list of ProductPrice entities. The problem is that during when I'm databinding, I need to display the Currency.Name property. So I have the Select N+1 problem: for each ProductPrice entity, I'm executing an additional query to get the associated Currency entity.

My stored procedure does a join with the Currency table, but I can't figure out how to get NHibernate to use the extra columns to prepopulate the Currency object. I've used FetchMode.Join with the Criteria API, but that doesn't apply to a stored proc. I've read about aliasing, but that too doesn't apply here.

Thanks in advance.

Two options immediately come to me. If neither of these work for you then I'm not sure I can really be of much help.

First, do you really need to use stored procedures? I assume you do this because it is a company policy? The application will perform better if you allow it to write the dynamic sql. Development time would also be greatly reduced. I realize this may not be an option.

Second, you could use a "reporting object" to encapsulate just the fields you want to return. So make a new object which is named something like ProductPriceList which has a child Price property and child CurrencyPrice property. (Of course you would also include any other columns you needed). This will then let you use just one query to populate all values, but if you ever needed to modify the price or currency you would still need to do a load from the database for that record. The "reporting object" would not be an object which mapped directly to any table, it would just be used to return the results of the stored procedure.

Ditto on jchapman's points. In addition, it sounds like the Currency objects are relatively static. If that is the case, employing the second-level cache would also be a viable option.

Yes, our standard is to use stored procs, but I have relaxed that requirement a bit by letting NHibernate generate the query when it is a simple fetch, or a simple join on a foreign key. This particular query is a little more involved so I prefer to implement it as a stored proc.



The ProductPrice object already has the schema that I want to use. I could create another object that flattens the ProductPrice and Currency fields into a single object, but this is ugly.

My stored procedure returns the following fields:
ProductID
CurrencyCode
CurrencyName
CurrencySymbol
CurrencyUsdValue
Price

Can I not group the Currency* fields into a Currency object? Can I map them to a Currency component?

Honestly, I would recommend learning the new technology. HQL is a very powerful query language. If possible write the query that way.



I don't think this approach is ugly at all. If you really do find it ugly you could do the really ugly thing and just return an array of objects. You don't have to wrap it in a type if you don't want to. The approach we are describing with the "reporting objects" is very similar to what Microsoft will recommend with Linq to Sql, except Linq to Sql will use the new anonymous type feature of .NET 3.5 so it will create the new type for you.

I cannot use HQL. This code will have to be maintained by others in the organization and I cannot expect them to learn a proprietary query language.



Flattening the object graph makes it more relational and less OO. My object shouldn't have CurrencyCode, CurrencyName, CurrencySymbol fields; that's why I have a Currency object. In Linq you can map the result set columns to properties and sub-properties of child objects. Can I not achieve this with an NHibernate <sql-query> mapping? Isn't that what components are for?

Ok, I disagree regarding the OO statement, but that is ok. I don't feel that data binding is a very OO style concept. Business logic is a different story and you would never use the "reporting object" for anything besides, well, reporting.

If those ideas don't work, take the other suggestion which karlchu said. Add a second level cache. How many unique currencies are there?

Plus if all prices are the same currency it won't load it n times, it'll just load it once. Are we trying to solve a problem before we know it exists?

I'm loading the price list for a single product in the various currencies, so it's not the same currency. Caching is an option, as there aren't that many currencies, but it's unnecessary because if I were using a DataSet for example I'd just do a join and be done with it. Of course this works because the table is a flat object, and I can do a similar thing with NHibernate. But to me the OO approach would be to encapsulate all the currency information in my Currency object, and I'm thinking that there must be a way to do this. Otherwise, a stored proc in NHibernate can return only a single, flat entity?