How do you avoid the n+1 problem with SubSonic?

Question

Ayende has a blog post detailing how to combat the "n+1" problem in nHibernate. In essence, the problem is this:

Assume you have two tables, "BlogPosts" and "Comments" with a one-to-many relationship between them (i.e. each BlogPost can have multiple Comments). Now, let's say you want to execute the following nested for loop:

foreach (BlogPost post in blogposts)
{
    foreach (Comment comment in post.Comments)
    {
        // Do something
    }
}

From what I understand, the classes that SubSonic generate are lazyload by default (I don't want to turn this off completely because it's useful in most circumstances, just not this one). That means that every time the inner loop is executed (i.e. when post.Comments is accessed), a separate query must be sent to the database to retrieve the comments.

So if you have 80 blog posts, that's 1 query to get the list of blog posts, then 80 queries to get the comments for each of them. If the comments were eager loaded, however, this would be reduced to 1 query.

Is there any way to combat this problem in SubSonic currently?

Answer 1

Unless you create a query to select from Comment based on post IDs, I don't think there's a way to combat it. This would get you down to two. One query to select the post IDs you want, then another to get all the comments for that list of post IDs.

Answer 2

I think what you should do is create a partial class method that will get all the comments. Not so clean but should work.

Answer 3

I too use partial classes and load related table classes like so:

Partial Public Class Book

    Private _Author as Database.Author 
    Property Author() as Database.Author
      Get
         If _Author is nothing then
           ' Load the author class here.
         End if
         return _Author
      End get
      Set
         '....
      End Set
    End Property

End Class