I have a simple one-to-many relation between an Article table and a Price table. Article -> Price.

For simplicity the Article table has 2 columns ARTICLEID and NAME. The Price table has 4 columns PRICEID, ARTICLEID, FROMDATE and SALEPRICE.

What I want to do is get a list of all Articles that has future prices. I also want the Article.Prices collection to only contain the future prices.

I see 2 approaches to solve the problem, both with some minor drawbacks.


---- First approach ----

Articles with future prices could easily be retrieved with HQL:

"select distinct price.Article from Price price where FromDate >= '2005-08-01'"

So I have a collection with all Articles that has future prices. By default the Article.Prices collection will contain all the prices for the Article.

So to get only the prices that I'm interested in I could either

1. Filter the Prices collection on all the returned Articles.

session.Filter(article.Prices, 'where FromDate >= ?', FromDate, NHibernate.Type.TypeFactory.GetDateType)

or

2. Use HQL to get the Prices for each and every Article by running one query pr. Article (or get a sorted collection of Prices in one HQL).
"from Prices where Article.Id = :id and FromDate >= :fromdate"



---- Second approach ----
Use HQL to get the Articles with a populated Prices collection.

from Article article left join fetch article.Prices price where price.FromDate >= '2005-08-01'

This *almost* gives me what I want. The drawback here is that it returns one Article object for every record found in the Price table. Each duplicate Article contains the same Prices.

adding

select distinct boat

to the HQL above does not return one object pr. Article.

So basically I would need to remove the duplicate article objects from the collection to get my wanted result.

---- ----


The second approach is retrieving less data from the database, so I would definitely go for the second approach.

But are there any other solutions to this?