I have a letter object with a customers collection that is lazy loaded.

The letter object contains information about the letter ie template and formatting.

The problem is my email sender needs to iterate one record at a time through the rows sending an email to each customer. Filling the whole collection is not an option as there are literly millions of customers.

After each email is update sent for the customer a flag is set against the customer record which needs to be saved to the database.

What would be the best way to do this?

At present if i was not working with objects, i would open a data reader and walk through each record and send the email based on the columns in the datareader.

is there any support for walking through a large collection of rows in Nhibernate. I tried using methods on criteria objects setfirstrecord and setmaxresults but the sql generated under MS sql2000 dialect is very inefficient. IE it selects top <n> records to get the first record required by the index. Imagine running querys as you walk through the records like select top 1 from ...., select top 2 from ....., select top 3 ...., select top 4 ....

Any suggestions would be appreciated.

If you're using SQL Server, then I don't think you'll be able to do it through NHibernate until SQL 2005. This post has a workaround that someone in a similar situation used.

thanks. ill have a look at that