如何渴望使用LINQ to SQL加载同级数据? [英] How to eager load sibling data using LINQ to SQL?

问题描述

目标是使用LINQ to SQL向SQL Server发出最少查询，而无需使用匿名类型.该方法的返回类型将需要为IList< Child1>.关系如下:

The goal is to issue the fewest queries to SQL Server using LINQ to SQL without using anonymous types. The return type for the method will need to be IList<Child1>. The relationships are as follows:

            Parent
    Child1          Child2
Grandchild1

父母> Child1 是一对多关系

Child1> Grandchild1 是一对n关系(其中n为零到无穷大)

Child1 > Grandchild1 is a one-to-n relationship (where n is zero to infinity)

父母> Child2 是一对一的关系(其中n为零到无穷大)

Parent > Child2 is a one-to-n relationship (where n is zero to infinity)

我能够急于将Parent，Child1和Grandchild1数据加载到SQL Server中，从而产生一个查询.

I am able to eager load the Parent, Child1 and Grandchild1 data resulting in one query to SQL Server.

此查询带有加载选项，可加载所有数据，兄弟数据库(Child2)除外:

This query with load options eager loads all of the data, except the sibling data (Child2):

DataLoadOptions loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Child1>(o => o.GrandChild1List);
loadOptions.LoadWith<Child1>(o => o.Parent);

dataContext.LoadOptions = loadOptions;

IQueryable<Child1> children = from child in dataContext.Child1
                                select child;

我还需要加载同级数据.我尝试过的一种方法是将查询分为两个LINQ to SQL查询，并将结果集合并在一起(不是很漂亮)，但是在访问同级数据时，无论如何它都是延迟加载的.

I need to load the sibling data as well. One approach I have tried is splitting the query into two LINQ to SQL queries and merging the result sets together (not pretty), however upon accessing the sibling data it is lazy loaded anyway.

添加同级加载选项将向SQL Server发出针对每条Grandchild1和Child2记录的查询(这正是我要避免的事情):

Adding the sibling load option will issue a query to SQL Server for each Grandchild1 and Child2 record (which is exactly what I am trying to avoid):

DataLoadOptions loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Child1>(o => o.GrandChild1List);
loadOptions.LoadWith<Child1>(o => o.Parent);
loadOptions.LoadWith<Parent>(o => o.Child2List);

dataContext.LoadOptions = loadOptions;

IQueryable<Child1> children = from child in dataContext.Child1
                                select child;


exec sp_executesql N'SELECT * FROM [dbo].[Child2] AS [t0]
WHERE [t0].[ForeignKeyToParent] = @p0',N'@p0 int',@p0=1

exec sp_executesql N'SELECT * FROM [dbo].[Child2] AS [t0]
WHERE [t0].[ForeignKeyToParent] = @p0',N'@p0 int',@p0=2

exec sp_executesql N'SELECT * FROM [dbo].[Child2] AS [t0]
WHERE [t0].[ForeignKeyToParent] = @p0',N'@p0 int',@p0=3

exec sp_executesql N'SELECT * FROM [dbo].[Child2] AS [t0]
WHERE [t0].[ForeignKeyToParent] = @p0',N'@p0 int',@p0=4

我还编写了LINQ to SQL查询以加入所有数据，希望它渴望加载数据，但是当访问Child2或Grandchild1的LINQ to SQL EntitySet时，它会延迟加载数据.

I've also written LINQ to SQL queries to join in all of the data in hopes that it would eager load the data, however when the LINQ to SQL EntitySet of Child2 or Grandchild1 are accessed it lazy loads the data.

返回IList< Child1>的原因是为业务对象补水.

The reason for returning the IList<Child1> is to hydrate business objects.

我的想法是:

1. 以错误的方式解决此问题.
2. 可以选择调用存储过程吗?
3. 我的组织不应该将LINQ to SQL用作ORM?

非常感谢您的帮助.

谢谢

-斯科特

推荐答案

应该正确的内容，除了已经设置的LoadOptions外，还需要添加dataContext.DeferredLoadingEnabled = false;.

What you have should be correct, you need to add this dataContext.DeferredLoadingEnabled = false; in addition to the LoadOptions you are already setting.

这篇关于如何渴望使用LINQ to SQL加载同级数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！