何时来自相关表的记录加载LINQ2SQL

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

2 个解决方案

#1

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

我假设报告和评论之间存在1-M FK关系(1报告可以有很多评论)?

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

一种选择是使用DataLoadOptions.LoadWith方法 - 如下所示:

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.

另一个选项是具体说明要在Linq查询中选择的内容,例如:

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)

Linq和Linq To Sql的延迟执行模型(基本上,对于Linq to SQL,查询仅在要求结果时运行,例如通过迭代集合或绑定到网格)

The difference between IQueryable and IEnumerable

IQueryable和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)

Jon Skeets“C#in depth”给出了很好的概述,并且我也听到了很多关于“Linq in Action”的内容 - 此外还有很多关于这些概念的博客文章,这些文章比我能做的更加公正。这里; o)

#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.

请记住,如果使用LoadOptions定义多跳路径(报告,注释,连贯性),则会通过代码加载第3个和更多跳(如果与1:n关系相关),效率非常低:它们将执行每个父母一个查询。对于报告 - 评论,没关系,它们将在2个查询中获取。

#1