C# – Linq2sql/GridView: Pagination not working

clinq-to-sqlsql servertsql

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?

Best Answer

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

Related Topic