Linq2sql/GridView: Pagination not working?

Question

What's the best method to bind a linq2sql collection to a GridView, given that I want ot use builtin pagination and sorting?

I've tried:

public static void BindEnquiryList(EnquiryQuery query, GridView view)
        {
            DataContext db = DataContextManager.Context
            //view.DataSource = (from e in EnquiryMethods.BuildQuery(query)
            view.DataSource = (from e in db.Enquiries
                               select new
                               {
                                    Id = e.Id,
                                    Name = e.Name,
                                    PublicId = EnquiryMethods.GetPublicId(e.PublicId),
                                    What = e.WorkType.DescriptionText,
                                    Where = e.EnquiryArea.DescriptionText,
                                    Who = e.EnquiryType0.DescriptionText,
                                    When = e.EnquiryTime0.DescriptionText,
                                    PriceRange = e.EnquiryPrice0.DescriptionText,
                                    DisplayPriceRange = e.EnquiryPrice0.Display,
                                    Description = e.Description,
                                    Published = e.EnquiryPublished
                               });

            view.DataBind();
        }

And the GridView is set to pagesize 20 (and properly shows 20) but the execution takes forever to load and when I log the sql I get:

SELECT [t0].[Id], [t0].[Name], [t0].[PublicId] AS [publicId], [t1].[DescriptionText] AS [What], [t2].[DescriptionText] AS [Where], [t3].[DescriptionText] AS [Who], [t4].[DescriptionText] AS [When], [t5].[DescriptionText] AS [PriceRange], [t5].[Display] AS [DisplayPriceRange], [t0].[Description], [t0].[EnquiryPublished] AS [Published]
FROM [dbo].[Enquirys] AS [t0]
LEFT OUTER JOIN [dbo].[WorkTypes] AS [t1] ON [t1].[Id] = [t0].[EnquiryPlace]
LEFT OUTER JOIN [dbo].[EnquiryAreas] AS [t2] ON [t2].[Id] = [t0].[EnquiryPlace]
LEFT OUTER JOIN [dbo].[EnquiryTypes] AS [t3] ON [t3].[Id] = [t0].[EnquiryType]
LEFT OUTER JOIN [dbo].[EnquiryTimes] AS [t4] ON [t4].[Id] = [t0].[EnquiryTime]
LEFT OUTER JOIN [dbo].[EnquiryPrices] AS [t5] ON [t5].[Id] = [t0].[EnquiryPrice]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Without any top. I'd expected the GridView to manage Take and Skip operations?

Answer 1

I'm not sure, but can you use a LinqDataSource? Otherwise, I think GridView requires that the data be in a DataSet in order for it to do paging and sorting on its own.

Answer 2

IEnumerable<T> is not supported for sorting and paging by the GridView Control. But you can sertainly use a List<T>. Just .ToList() your result set.

grid_PageIndexChanging(object sender, GridViewPageEventArgs e) {
        this.grid.PageIndex = e.NewPageIndex;
        this.grid.DataSource = this.DataSource;
        this.grid.DataBind();
    }

Answer 3

The best way is to use the LinqDataSource. Create an event handler for the selecting event on the LinqDataSource.

protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
    e.Result = (from e in db.Enquiries
                               select new
                               {
                                    Id = e.Id,
                                    Name = e.Name,
                                    PublicId = EnquiryMethods.GetPublicId(e.PublicId),
                                    What = e.WorkType.DescriptionText,
                                    Where = e.EnquiryArea.DescriptionText,
                                    Who = e.EnquiryType0.DescriptionText,
                                    When = e.EnquiryTime0.DescriptionText,
                                    PriceRange = e.EnquiryPrice0.DescriptionText,
                                    DisplayPriceRange = e.EnquiryPrice0.Display,
                                    Description = e.Description,
                                    Published = e.EnquiryPublished
                               });

}

Then the LinqDataSource will handle the paging and sorting for you and you get compile checks on you linq query