SubSonic reading joined tables cost

Question

Assuming I have the following schema

table A:
ID int primary key
value varchar(255) not null

table B:
ID int primary key
AID foreign key refences A (ID)
name varchar(40) not null

now when I execute the following subsonic linq

var items = 
from a in A.All() 
where any-condition select a;

all is fine.

the killer is when I do the following

for(var item in items)
{
   for(var nestedItem in item.B) // troubling!
   {
     DoSomething(nestedItem)
   }
}

I am not very familiar with the inner workings, But I am pretty sure it does additional trips to the DB to get the joined table rows.

Can you please tell me how can I avoid such expensive trips?

Answer 1

This is a classic problem with ORMs - SELECT N + 1. What you're coming up against is Lazy vs. Eager loading and in this case you need to either do some kind of outer join (using the query tool) or pull B into a list.