LINQ to SQL不带跳过导致多个SQL语句 [英] LINQ to SQL Take w/o Skip Causes Multiple SQL Statements
问题描述
我有一个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.
这似乎是由于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).
如果您跳过并获取，则查询翻译器将在父行上加入一些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.
如果您跳过(0)并接受，那么跳过程序会被翻译员视为非操作而删除-就像您从未说过跳过一样.
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(0)取消为非操作的位置.我们需要呼叫跳过"，并在以后提供跳过的号码.
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不带跳过导致多个SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
