LINQ performing multiple queries instead of a single "joined" query

Question

I am not a LINQ power user by any means, but can fumble my way around on a basic level. I have a question regarding how LINQ formulates it's query "strategy". I will try to explain this as best as I can and write an extremely dumbed-down example by memory.

I have a data model that contains multiple database views. Let's say the views have a column structure as follows:

PersonView

PersonViewId | Surname | GivenName | OtherViewId
------------------------------------------------

OtherView View

OtherViewId | PersonViewId | Name
---------------------------------

After setting the primary keys for the view (PersonView.PersonViewId / OtherView.OtherViewId) and setting the appropriate fields to be non-nulable, I create an association between PersonView.PersonViewId (Parent) to OtherView.PersonViewId (Child). I set it to be "one to one" and write some code to consume it:

StringBuilder s = new StringBuilder();
foreach(PersonView p in dc.PersonViews)
{
    s.AppendLine(p.OtherViews.Name + "<br />");
}

After noticing extremely poor performance, I profiled the database and noticed it was doing a query for each of the PersonView's in the foreach statement.

At this point I rewrote the query and replaced the association in the DBML with a JOIN in the LINQ query, profiled the database and it queried the DB as expected, only once.

I am thinking that it is something to do with then the DB is actually being queried, but am not sure where to be debugging that. Can someone point me in the proper direction on this to help me improve the performance of using associations or am I stuck using JOIN's to accomplish what I need?

Thanks :)

Answer 1

This is caused by lazy loading - you can get around that by applying LoadWith() (the equivalent of EF's Include() for Linq to SQL) and then doing your query afterwards:

var dlo = new DataLoadOptions();
dlo.LoadWith<PersonView>(p => p.OtherViews);
dc.LoadOptions = dlo;
//your query here