GridView binded from LINQ SP and smart pagination
Hello
Following ScottGu's tutorial http://weblogs.asp.net/scottgu/archive/2007/09/07/linq-to-sql-part-9-using-a-custom-linq-expression-with-the-lt-asp-linqdatasource-gt-control.aspx I have tried to bind a gridview from a SP that I've addded to the LINQ data context.
Inside LinqDataSource OnSelection I've added:
RHDataContext db = new RHDataContext();
var result = db.GetBrowseGuilds();
e.Result = result;
This piece of code gives "The query results cannot be enumerated more than once." error.
Replacing "e.Result = result;" with "e.Result = result.ToList();" fix the error, but it will also break the intelligent paging according to what ScottGu writes in his tutorial.
The code I pasted is all the code I wrote in the _Selecting function. The LinqDataSource it's used to bind a GridView, and in gridview's _RowDataBound I retrieve some values and use them to pupulate some controls in the gridview. That's all I have. Results shouldn't be iterated twice.
By debuging the page I've noticed that after "e.Result = result;" it runs the GetBrowseGuildsResult { } constructor for every row returned. The error appears right after this iteration is over.
Also Ive noticed that no matter if I use .ToList() or not, the GetBrowseGuildsResult { } is ran for all the 1.000 records the stored proc returns.
Any help on this issue please ?
hi,
i encountered a similar problem when i was calling Count() on a linq query before calling First() on it. calling Count() caused the query to be enumerated and you basically can't enumerate it again because it uses a DataReader internally. Matt Warren explains it well in this
post.
i would guess that the GetBrowseGuilds() method enumerates the query already, maybe you could try writing a linq query from scratch and see if that solves it.
hope this helps
tim
hazarding a total guess here, i would say that the GridView is accessing the Count property on your GetBrowseGuilds() result, so that it can figure out what to do with paging, and this may explain the double enumeration of your data. does the stack trace give any clues? once you have the result in a List then you are out of Linq and then there is no problem enumerating the data several times. that's all i can think of, out of my depth after that.
tim
A question just poped in my mind. To have a gridview binded from a LinqDataSource which use a SProc what do I have to enable: GridView's pagination and / or LinqDataSource AutoPagination ?
On the other side, I think I would be ok with RJ's code he posted in his blog:
Protected Sub ldsCustomers_Selecting(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) _
Handles ldsOrders.Selecting
Dim dcNwind = New NorthwindDataContext()
e.Arguments.RetrieveTotalRowCount = False
e.Arguments.TotalRowCount = 91
Dim intMaxRows As Integer = e.Arguments.MaximumRows
Dim intStartRow As Integer = e.Arguments.StartRowIndex
Dim intRowCount As Integer = 0
e.Result = dcNwind.usp_GetCustomersPaged(intMaxRows, intStartRow)
End Sub
LinqDataSource is handling the pagination, not GridView. When AutoPage is true LinqDataSource does automatic paging. It calls Count on the query to provide the DataSourceSelectArguments.TotalRowCount for the databound control, then uses Skip and Take. Count is only called if DataSourceSelectArguments.RetrieveTotalRowCount is true - which is typically the case when the databound control is paging.
When AutoPage is false, LinqDataSource requires that the user handle the paging manually during the Selecting event. In this case, you need to set DataSourceSelectArguments.TotalRowCount yourself and perform the paging manually using DataSourceSelectArguments.StartRowIndex and DataSourceSelectArguments.MaximumRows (pageSize).
