Why is Entity Framework ignoring the Take () method when generating SQL?
I am using .Skip and .Take methods in Entity Framework. The execution of the .Skip call is done when the SQL is generated. Not. This code:
public IList<DocPullRun> GetDocRunsPaginated(int startRowIndex, int maximumRows) {
Logger.Debug("GetDocRunsPaginated: startRowIndex: {0}, maximumRows: {1}", startRowIndex, maximumRows);
Debug.Assert(startRowIndex >= 0);
IOrderedQueryable<DocPullRun> sortedPulls =
from run in DB.DocPullRuns
.Include("DocumentPullDefinition")
.Include("DocumentPullDefinition.Case")
.Include("DocumentPullDefinition.DocCategory")
.Include("DocumentPullDefinition.Repository")
.Include("DocumentPullDefinition.Repository.ConcordanceRepository")
orderby run.PullStarted descending
select run;
IQueryable<DocPullRun> query = sortedPulls.Skip(startRowIndex);
if (maximumRows > 0)
query.Take(maximumRows);
return query.ToList();
}
The results in this SQL are shown below (note that with this call, startRowIndex is 0 and maximumRows is 10 ). The Where clause contains WHERE [Project1]. [Row_number]> 0 (this is part of .Skip), but I expected to see Select TOP 10 , but instead just select Select. The resulting list contains more than 10 items. Note that I ran SQL Trace and see that the SQL is indeed deferred and not actually sent until the last line: query.ToList (). Is this some type of error?
SELECT
[Project1].[C1] AS [C1],
[Project1].[Id] AS [Id],
[Project1].[PullStarted] AS [PullStarted],
[Project1].[PullEnded] AS [PullEnded],
[Project1].[MatchedQuery] AS [MatchedQuery],
[Project1].[NewDocs] AS [NewDocs],
[Project1].[UpdatedDocs] AS [UpdatedDocs],
[Project1].[DeletedDocs] AS [DeletedDocs],
[Project1].[Errors] AS [Errors],
[Project1].[C2] AS [C2],
[Project1].[Id1] AS [Id1],
[Project1].[Requestor] AS [Requestor],
[Project1].[RequestTime] AS [RequestTime],
[Project1].[Query] AS [Query],
[Project1].[Enabled] AS [Enabled],
[Project1].[LastPullTime] AS [LastPullTime],
[Project1].[Case_ID] AS [Case_ID],
[Project1].[Case_Name] AS [Case_Name],
[Project1].[Client_ID] AS [Client_ID],
[Project1].[Matter_ID] AS [Matter_ID],
[Project1].[Is_Active] AS [Is_Active],
[Project1].[Requires_Case_Specific_Permissions] AS [Requires_Case_Specific_Permissions],
[Project1].[Last_Update] AS [Last_Update],
[Project1].[C3] AS [C3],
[Project1].[Category_ID] AS [Category_ID],
[Project1].[CategoryName] AS [CategoryName],
[Project1].[DefaultGridLayout] AS [DefaultGridLayout],
[Project1].[C4] AS [C4],
[Project1].[Repository_ID] AS [Repository_ID],
[Project1].[Description] AS [Description],
[Project1].[OfficeAbbr] AS [OfficeAbbr],
[Project1].[C5] AS [C5],
[Project1].[Repository_ID1] AS [Repository_ID1],
[Project1].[ConcordanceDBId] AS [ConcordanceDBId],
[Project1].[Repository_ID2] AS [Repository_ID2],
[Project1].[RepositoryType_Id] AS [RepositoryType_Id]
FROM ( SELECT [Project1].[Id] AS [Id], [Project1].[PullStarted] AS [PullStarted], [Project1].[PullEnded] AS [PullEnded], [Project1].[MatchedQuery] AS [MatchedQuery], [Project1].[NewDocs] AS [NewDocs], [Project1].[UpdatedDocs] AS [UpdatedDocs], [Project1].[DeletedDocs] AS [DeletedDocs], [Project1].[Errors] AS [Errors], [Project1].[Id1] AS [Id1], [Project1].[Requestor] AS [Requestor], [Project1].[RequestTime] AS [RequestTime], [Project1].[Query] AS [Query], [Project1].[Enabled] AS [Enabled], [Project1].[LastPullTime] AS [LastPullTime], [Project1].[Case_ID] AS [Case_ID], [Project1].[Case_Name] AS [Case_Name], [Project1].[Client_ID] AS [Client_ID], [Project1].[Matter_ID] AS [Matter_ID], [Project1].[Is_Active] AS [Is_Active], [Project1].[Requires_Case_Specific_Permissions] AS [Requires_Case_Specific_Permissions], [Project1].[Last_Update] AS [Last_Update], [Project1].[Category_ID] AS [Category_ID], [Project1].[CategoryName] AS [CategoryName], [Project1].[DefaultGridLayout] AS [DefaultGridLayout], [Project1].[Repository_ID] AS [Repository_ID], [Project1].[Description] AS [Description], [Project1].[OfficeAbbr] AS [OfficeAbbr], [Project1].[Repository_ID1] AS [Repository_ID1], [Project1].[ConcordanceDBId] AS [ConcordanceDBId], [Project1].[Repository_ID2] AS [Repository_ID2], [Project1].[RepositoryType_Id] AS [RepositoryType_Id], [Project1].[C1] AS [C1], [Project1].[C2] AS [C2], [Project1].[C3] AS [C3], [Project1].[C4] AS [C4], [Project1].[C5] AS [C5], row_number() OVER (ORDER BY [Project1].[PullStarted] DESC) AS [row_number]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[PullStarted] AS [PullStarted],
[Extent1].[PullEnded] AS [PullEnded],
[Extent1].[MatchedQuery] AS [MatchedQuery],
[Extent1].[NewDocs] AS [NewDocs],
[Extent1].[UpdatedDocs] AS [UpdatedDocs],
[Extent1].[DeletedDocs] AS [DeletedDocs],
[Extent1].[Errors] AS [Errors],
[Extent2].[Id] AS [Id1],
[Extent2].[Requestor] AS [Requestor],
[Extent2].[RequestTime] AS [RequestTime],
[Extent2].[Query] AS [Query],
[Extent2].[Enabled] AS [Enabled],
[Extent2].[LastPullTime] AS [LastPullTime],
[Extent3].[Case_ID] AS [Case_ID],
[Extent3].[Case_Name] AS [Case_Name],
[Extent3].[Client_ID] AS [Client_ID],
[Extent3].[Matter_ID] AS [Matter_ID],
[Extent3].[Is_Active] AS [Is_Active],
[Extent3].[Requires_Case_Specific_Permissions] AS [Requires_Case_Specific_Permissions],
[Extent3].[Last_Update] AS [Last_Update],
[Extent4].[Category_ID] AS [Category_ID],
[Extent4].[CategoryName] AS [CategoryName],
[Extent5].[DefaultGridLayout] AS [DefaultGridLayout],
[Extent6].[Repository_ID] AS [Repository_ID],
[Extent6].[Description] AS [Description],
[Extent6].[OfficeAbbr] AS [OfficeAbbr],
[Extent9].[Repository_ID] AS [Repository_ID1],
[Extent9].[ConcordanceDBId] AS [ConcordanceDBId],
[Extent12].[Repository_ID] AS [Repository_ID2],
[Extent13].[RepositoryType_Id] AS [RepositoryType_Id],
1 AS [C1],
1 AS [C2],
1 AS [C3],
1 AS [C4],
1 AS [C5]
FROM [dbo].[DocPullRuns] AS [Extent1]
LEFT OUTER JOIN [dbo].[DocumentPullDefinitions] AS [Extent2] ON [Extent1].[DocumentPullDefinitionId] = [Extent2].[Id]
LEFT OUTER JOIN [dbo].[Cases] AS [Extent3] ON [Extent2].[CaseId] = [Extent3].[Case_ID]
LEFT OUTER JOIN [dbo].[DocCategories] AS [Extent4] ON [Extent2].[CategoryId] = [Extent4].[Category_ID]
LEFT OUTER JOIN [dbo].[DocCategories] AS [Extent5] ON [Extent2].[CategoryId] = [Extent5].[Category_ID]
LEFT OUTER JOIN [dbo].[Repositories] AS [Extent6] ON [Extent2].[RepositoryId] = [Extent6].[Repository_ID]
LEFT OUTER JOIN (SELECT [Extent7].[Repository_ID] AS [Repository_ID3], [Extent7].[RepositoryType_Id] AS [RepositoryType_Id], [Extent7].[Description] AS [Description], [Extent7].[OfficeAbbr] AS [OfficeAbbr], [Extent8].[Repository_ID] AS [Repository_ID4], [Extent8].[ConcordanceDBId] AS [ConcordanceDBId]
FROM [dbo].[Repositories] AS [Extent7]
LEFT OUTER JOIN [dbo].[ConcordanceRepositories] AS [Extent8] ON [Extent7].[Repository_ID] = [Extent8].[Repository_ID] ) AS [Join6] ON [Extent2].[RepositoryId] = [Join6].[Repository_ID3]
LEFT OUTER JOIN [dbo].[ConcordanceRepositories] AS [Extent9] ON [Join6].[Repository_ID4] = [Extent9].[Repository_ID]
LEFT OUTER JOIN (SELECT [Extent10].[Repository_ID] AS [Repository_ID5], [Extent10].[RepositoryType_Id] AS [RepositoryType_Id], [Extent10].[Description] AS [Description], [Extent10].[OfficeAbbr] AS [OfficeAbbr], [Extent11].[Repository_ID] AS [Repository_ID6], [Extent11].[ConcordanceDBId] AS [ConcordanceDBId]
FROM [dbo].[Repositories] AS [Extent10]
LEFT OUTER JOIN [dbo].[ConcordanceRepositories] AS [Extent11] ON [Extent10].[Repository_ID] = [Extent11].[Repository_ID] ) AS [Join9] ON [Extent2].[RepositoryId] = [Join9].[Repository_ID5]
LEFT OUTER JOIN [dbo].[ConcordanceRepositories] AS [Extent12] ON [Join9].[Repository_ID6] = [Extent12].[Repository_ID]
LEFT OUTER JOIN [dbo].[Repositories] AS [Extent13] ON [Extent2].[RepositoryId] = [Extent13].[Repository_ID]
) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[PullStarted] DESC
+2
source to share