I am trying to achieve the following using the Datalist and Linq to Sql:
- Paged Datalist
- Efficient paging i.e if there are 3000 records only retrieve the records needed for the current page.
- Have the page number displayed in the querystring to allow pages to be cached.
However, when I look at the sql which is outputted it is doing multiple data calls for each page (see below).
So my questions are:
- Why are there 2 calls for the number of records?
- Why is the first 10 records returned initially?
- How can I set this up to only have 2 datacalls?
My Code
<asp:ListView ID="lv" runat="server" DataSourceID="ds">
<LayoutTemplate>
<asp:PlaceHolder runat="server" ID="itemPlaceHolder" />
</LayoutTemplate>
<ItemTemplate>
<p>
<b>
<asp:Literal ID="Literal2" runat="server" Text='<%# Eval("Submitted") %>' />
</b>
<br />
<asp:Literal ID="Literal1" runat="server" Text='<%# Eval("Text") %>' />
</p>
</ItemTemplate>
</asp:ListView>
<asp:DataPager runat="server" ID="dp" PagedControlID="lv" QueryStringField="page">
<Fields>
<asp:NextPreviousPagerField ButtonType="Button" ShowFirstPageButton="True"
ShowNextPageButton="true" ShowPreviousPageButton="False" />
<asp:NumericPagerField />
<asp:NextPreviousPagerField ButtonType="Button" ShowLastPageButton="True"
ShowNextPageButton="true" ShowPreviousPageButton="False" />
</Fields>
</asp:DataPager>
This is the ouputted sql for page 8 SELECT COUNT(*) AS [value] FROM [dbo].[Entries] AS [t0]
SELECT TOP (10) [t0].[Submitted], [t0].[Text] FROM [dbo].[Entries] AS [t0] ORDER BY [t0].[Submitted]
SELECT COUNT(*) AS [value] FROM [dbo].[Entries] AS [t0]
SELECT [t1].[Submitted], [t1].[Text] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Submitted]) AS [ROW_NUMBER], [t0].[Submitted], [t0].[Text] FROM [dbo].[Entries] AS [t0] ) AS [t1] WHERE [t1].[ROW_NUMBER] BETWEEN 70 + 1 AND 70 + 10 ORDER BY [t1].[ROW_NUMBER]