When are records from related tables loaded with LINQ2SQL

Question

Let's say I have two tables:

• Report
• Comment

And assuming I have a database context:

var reports = db.Reports();

How can I make sure all Comments for each report are loaded as well?

At this point I want to disconnect from the database but still have access to the comments. (For example:)

reports[0].Comments[0].Subject

Answer 1

I'm assuming that there is an 1-M FK relationship between reports and comments (1 Report can have many Comments)?

One option is to use the DataLoadOptions.LoadWith method - something like the following:

var reports = db.Reports();
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Reports>(r => r.Comments);      // Ask for Comments along with reports
reports.LoadOptions = dlo;

Now, every time you select a report on that data context, the comments will be fetched from the db along with it.

Just beware that ALL fields from comments will be selected - there is no way using this method to select a subset of fields.

Another option is to be specific about what you want to select in the Linq query, e.g.

var myReportsList = from report in db.Reports
                    select new {  // Using anonymous type, but could use a custom class
                       Report = report,
                       Comment = report.Comment.Detail,   // for example
                       Subject = report.Comment.Subject
                    };

To understand when the query gets run and the database connection closed, you will need to understand:

• The deferred execution model of Linq and Linq To Sql (Basically, for Linq to SQL, the query only runs when the results are asked for e.g. by iterating over the collection or binding to a grid)
• The difference between IQueryable and IEnumerable

Jon Skeets "C# in depth" gives a great overview of these, and i've also heard very good things about "Linq in Action" - plus there are plenty of blog posts about these concepts which do the subjects more justice than I can do here ;o)

Answer 2

Keep in mind that if you use LoadOptions to define a multi-hop path (Reports, comments, anotherentity), the 3rd and further hops are loaded (if related over 1:n relationships) by code which is very inefficient: they'll execute one query per parent. For reports-comments, it's ok, they'll be fetched in 2 queries.