When records are found from linked tables loaded by LINQ2SQL
Let's say I have two tables:
- Report
- A comment
And if I have a database context:
var reports = db.Reports();
How can I make sure that all comments for each report are also loaded?
At this point I want to disconnect from the database, but still have access to comments. (For example:)
reports[0].Comments[0].Subject
source to share
I am assuming there is a 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 in this data context, comments will be fetched from the db along with it.
Just beware that ALL fields from comments will be selected - there is no way to use this method to select a subset of fields.
Another option is to specify what you want to select in a Linq query, eg.
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 a query is started and the database connection is closed, you will need to understand:
- Deferred Linq and Linq To Sql execution model (basically for Linq to SQL the query is only executed when the results are requested, for example by iterating over a collection or snapping to a grid)
- Difference between IQueryable and IEnumerable
Jon Skeets "C # in depth" gives a great overview, and I've also heard very good things about "Linq in Action" - plus there are many blog posts about these concepts that make topics fairer than I can do here: o)
source to share
Be aware that if you use LoadOptions to define a multiple-hop path (Reports, comments, anotherentity), the third and subsequent hops are loaded (if associated with a 1: n relationship) by code, which is very inefficient: they Execute one request for each parent. For comments-comments this is normal, they will be received in 2 requests.
source to share