Linq2SQl急切加载多个DataLoadOptions

I like to fetch the data with eager-loading using Linq2SQL. The code is similar as :

我喜欢使用Linq2SQL通过eager-loading来获取数据。代码类似于:

       DataLoadOptions options = new DataLoadOptions();

       options.LoadWith<Product>(c => c.ProductCompanies);

       options.LoadWith<Product>(c => c.OrderDetails);

       db.LoadOptions = options;

       IEnumerable<Product> products = db.Products.ToList<Product>();

I check it generated more than 1 SQL query as I expected. Actually it only do eager-loading with Product and OrderDetails, and the ProductCompany is queried one by one. Did I do anything wrong here? Or it is a Linq2SQL issue? Do we have any workaround?

我检查它生成了超过1个SQL查询,如我所料。实际上它只对Product和OrderDetails进行急切加载,并逐一查询ProductCompany。我在这做错了吗?或者它是Linq2SQL问题?我们有任何解决方法吗?

Thanks a lot!

非常感谢!

Update: I check the sql from SQL Profiler. I found both Leppie and Ian are correct. They are bounded in one transaction. But when I set it as lazy load, it opened multiple connection.

更新:我从SQL事件探查器检查sql。我发现Leppie和Ian都是正确的。它们在一次交易中受限制。但是当我将其设置为延迟加载时,它会打开多个连接。

3 个解决方案

#1

No, you didn't do anything wrong, Linq2SQL batches everything in a single transaction, but might execute an unbounded number of queries for the required result. DataLoadOptions is normally only used when the DataContext is not available for the entire context of the resulting usage. If you can keep the DataContext alive during execution, it is best to rely on deferred execution (that is default).

不,你没有做错任何事情,Linq2SQL在一个事务中批量处理所有事情,但可能会对所需结果执行无限数量的查询。 DataLoadOptions通常仅在DataContext不可用于结果使用的整个上下文时使用。如果您可以在执行期间保持DataContext的活动状态,则最好依赖延迟执行(即默认值)。

#2

I hit this issue in some code too, and after much experimenting and googling it looks like LINQ can only join across a single one-to-many relationship from each table : if you try to specify more than one to pre-load it just (randomly?) picks which one to pre-load and which others to leave deferred (simply ignoring those LoadWith hints)

我在一些代码中也遇到了这个问题,经过大量的实验和谷歌搜索后,看起来LINQ只能从每个表中加入一对一的关系:如果你试图指定多个来预加载它(随机?)选择哪一个预加载和哪些其他离开延迟(简单地忽略那些LoadWith提示)

Other people have posted this too, for example

例如,其他人也发布了这个

http://codebetter.com/blogs/david.hayden/archive/2007/08/06/linq-to-sql-query-tuning-appears-to-break-down-in-more-advanced-scenarios.aspx

#3

According to the docs:

根据文件:

When you query for an object, you actually retrieve only the object you requested. The related objects are not automatically fetched at the same time.

查询对象时,实际上只检索所请求的对象。不会同时自动提取相关对象。

The DataLoadOptions class provides two methods to achieve immediate loading of specified related data. The LoadWith method allows for immediate loading of data related to the main target. The AssociateWith method allows for filtering related objects.

DataLoadOptions类提供了两种方法来实现立即加载指定的相关数据。 LoadWith方法允许立即加载与主目标相关的数据。 AssociateWith方法允许过滤相关对象。

Having multiple sql statements doesn't surprise me. I think the difference here is all statements are just loaded up front instead of lazy loading them on demand.

拥有多个sql语句并不让我感到惊讶。我认为这里的区别是所有语句都只是在前面加载而不是根据需要延迟加载它们。

#1

#2