LINQ to SQL Take w/o Skip 导致多个 SQL 语句

LINQ to SQL Take w/o Skip Causes Multiple SQL Statements(LINQ to SQL Take w/o Skip 导致多个 SQL 语句)

问题描述

我有一个 LINQ to SQL 查询:

I have a LINQ to SQL query:

from at in Context.Transaction
select new  {
    at.Amount,
    at.PostingDate,
    Details = 
        from tb in at.TransactionDetail
        select new {
            Amount = tb.Amount,
            Description = tb.Desc
        }
}

这会导致执行一个 SQL 语句.一切都很好.

This results in one SQL statement being executed. All is good.

但是，如果我尝试从此查询返回已知类型，即使它们与匿名类型具有相同的结构，我也会为顶级执行一个 SQL 语句，然后为每个子"集执行一个额外的 SQL 语句.

However, if I attempt to return known types from this query, even if they have the same structure as the anonymous types, I get one SQL statement executed for the top level and then an additional SQL statement for each "child" set.

有什么方法可以让 LINQ to SQL 发出一条 SQL 语句并使用已知类型?

Is there any way to get LINQ to SQL to issue one SQL statement and use known types?

我必须有另一个问题.当我将一个非常简单(但仍然分层)的查询版本插入 LINQPad 并使用只有 2 或 3 个成员的新创建的已知类型时，我确实得到了一个 SQL 语句.当我知道更多时，我会发布和更新.

I must have another issue. When I plugged a very simplistic (but still hieararchical) version of my query into LINQPad and used freshly created known types with just 2 or 3 members, I did get one SQL statement. I will post and update when I know more.

编辑 2:这似乎是由于 Take 中的一个错误.详情请参阅下面我的回答.

EDIT 2: This appears to be due to a bug in Take. See my answer below for details.

推荐答案

首先 - 一些 Take 错误的原因.

First - some reasoning for the Take bug.

如果您只接受，则查询翻译器只使用 top.如果加入子集合破坏了基数，Top10 将不会给出正确答案.所以查询翻译器不会加入子集合(而是重新查询子集合).

If you just Take, the query translator just uses top. Top10 will not give the right answer if cardinality is broken by joining in a child collection. So the query translator doesn't join in the child collection (instead it requeries for the children).

如果你Skip and Take，那么查询翻译器会在父行上使用一些 RowNumber 逻辑......这些行号让它有 10 个父行，即使每个父行实际上有 50 条记录父母有 5 个孩子.

If you Skip and Take, then the query translator kicks in with some RowNumber logic over the parent rows... these rownumbers let it take 10 parents, even if that's really 50 records due to each parent having 5 children.

如果您Skip(0) and Take，Skip 被翻译器作为非操作删除 - 就像您从未说过 Skip 一样.

If you Skip(0) and Take, Skip is removed as a non-operation by the translator - it's just like you never said Skip.

从您所处的位置(调用 Skip and Take)到简单的解决方法"，这将是一个艰难的概念飞跃.我们需要做的是强制翻译发生在翻译器无法将 Skip(0) 作为非操作删除的点.我们需要调用 Skip，并在稍后提供跳过的号码.

This is going to be a hard conceptual leap to from where you are (calling Skip and Take) to a "simple workaround". What we need to do - is force the translation to occur at a point where the translator can't remove Skip(0) as a non-operation. We need to call Skip, and supply the skipped number at a later point.

DataClasses1DataContext myDC = new DataClasses1DataContext();
  //setting up log so we can see what's going on
myDC.Log = Console.Out;

  //hierarchical query - not important
var query = myDC.Options.Select(option => new{
  ID = option.ParentID,
  Others = myDC.Options.Select(option2 => new{
    ID = option2.ParentID
  })
});
  //request translation of the query!  Important!
var compQuery = System.Data.Linq.CompiledQuery
  .Compile<DataClasses1DataContext, int, int, System.Collections.IEnumerable>
  ( (dc, skip, take) => query.Skip(skip).Take(take) );

  //now run the query and specify that 0 rows are to be skipped.
compQuery.Invoke(myDC, 0, 10);

这会产生以下查询:

SELECT [t1].[ParentID], [t2].[ParentID] AS [ParentID2], (
    SELECT COUNT(*)
    FROM [dbo].[Option] AS [t3]
    ) AS [value]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ID]) AS [ROW_NUMBER], [t0].[ParentID]
    FROM [dbo].[Option] AS [t0]
    ) AS [t1]
LEFT OUTER JOIN [dbo].[Option] AS [t2] ON 1=1 
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p1 + @p2
ORDER BY [t1].[ROW_NUMBER], [t2].[ID]
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

这就是我们获胜的地方！

And here's where we win!

WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p1 + @p2

这篇关于LINQ to SQL Take w/o Skip 导致多个 SQL 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！