Issue with sorting a gridview, that uses template and boundfields

Question

JLAdjustmentList.aspx.cs

    protected void Page_Load(object sender, EventArgs e) {
            LoadApprovalList();
        }

        protected override void OnPreRender(EventArgs e) {
            base.OnPreRender(e);
            gridAdjustments.DataBind();
        }

        protected void gridAdjustments_OnSorting(object sender, EventArgs e) {
            LoadApprovalList();
        }

        private void LoadApprovalList() {
            if (PermissionHelper.IsPermissionGranted(Permission.JLAdjustmentView)) {
                gridAdjustments.DataSource = Facade.AdAdjustment.GetListForAdministrator();
                Page.Title = "JL Adjustments List";
            } else if (PermissionHelper.IsPermissionGranted(Permission.JLAdjustmentApprove)) {
                gridAdjustments.DataSource =
                    Facade.AdAdjustment.GetListForAdministrator();
                Page.Title = "JL Adjustments Approval";
            } else if (PermissionHelper.IsPermissionGranted(Permission.JLAdjustmentEdit)) {
                gridAdjustments.DataSource =
                    Facade.AdAdjustment.GetListForManager(Current.User.Id);
                Page.Title = "JL Adjustments List";
            }
        }

        protected string GetBillableHours(AdAdjustmentListData adjustmentListData) {
            return TimeFormat.MinutesToString(adjustmentListData.BillableHoursSum);
        }

        protected string GetNonBillableHours(AdAdjustmentListData adjustmentListData) {
            return TimeFormat.MinutesToString(adjustmentListData.NonBillableHoursSum);
        }

        protected string GetDetailLink(AdAdjustmentListData adjustmentListData) {
            if (PermissionHelper.IsPermissionGranted(Permission.JLAdjustmentEdit)) {
                return string.Format("~/JLAdjustmentEdit.aspx?Id={0}", adjustmentListData.AdjustmentId);
            }
            return string.Format("~/JLAdjustmentView.aspx?Id={0}", adjustmentListData.AdjustmentId);
        }
    }
}

JLAdjustmentList.aspx

<it:GridView runat="server" ID="gridAdjustments" CssClass="object_list" OnSorting="gridAdjustments_OnSorting">
        <Columns>
            <it:BoundField DataField="AdjustmentId" HeaderText="Id" SortExpression="AdjustmentId" />
            <it:BoundField DataField="Week.DisplayName" HeaderText="Week Ending Date" SortExpression="Week.DisplayName" />
            <it:BoundField DataField="EmployeeFullName" HeaderText="USERID" SortExpression="EmployeeFullName" />
            <it:BoundField DataField="DepartmentFullName" HeaderText="Department" SortExpression="DepartmentFullName" />
            <it:BoundField DataField="Portfolio" HeaderText="Portfolio" SortExpression="Portfolio" />
            <asp:TemplateField HeaderText="Billable Hours Changed">
                <ItemStyle Width="70px"></ItemStyle>
                <ItemTemplate>
                    <%# GetBillableHours((AdAdjustmentListData)Container.DataItem) %>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Non-Billable Hours Changed">
                <ItemStyle Width="70px"></ItemStyle>
                <ItemTemplate>
                    <%# GetNonBillableHours((AdAdjustmentListData)Container.DataItem)%>
                </ItemTemplate>
            </asp:TemplateField>
            <it:BoundField DataField="Reason" HeaderText="Reason For Adjustment" />
            <it:BoundField DataField="StatusName" HeaderText="Status" SortExpression="StatusName" />
            <it:BoundField DataField="LastChangedDateFormatted" HeaderText="Date of Last Action" />
            <asp:TemplateField HeaderText="Details">
                <ItemTemplate><center>
                    <asp:ImageButton runat="server" ImageUrl="~/img/edit.gif" 
                        PostBackUrl='<%# GetDetailLink((AdAdjustmentListData)Container.DataItem) %>' /></center>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </it:GridView>

SortHelper.cs

 public static class SortHelper {
        private static Dictionary<Type, Dictionary<String, ITypedSorter>> cache;

        static SortHelper() {
            cache = new Dictionary<Type, Dictionary<String, ITypedSorter>>();
        }

        public static IEnumerable Sort(this IEnumerable enumerable, string propertyName, bool desc) {
            if (enumerable != null) {
                if (enumerable is IQueryable) {
                    var itemType = (enumerable as IQueryable).ElementType;
                    var sorter = EnsureSorter(itemType, propertyName);
                    return sorter.Sort(enumerable, desc);
                }
                else {
                    var enumerator = enumerable.GetEnumerator();
                    enumerator.Reset();
                    if (enumerator.MoveNext()) {
                        var itemType = enumerator.Current.GetType();
                        var sorter = EnsureSorter(itemType, propertyName);
                        return sorter.Sort(enumerable, desc);
                    }
                }
            }
            return enumerable;
        }

I keep getting a "Specified method is not supported." AT if (enumerator.MoveNext()) {

Stacktrace

  at Microsoft.Data.Extensions.Materializer`1.<Materialize>d__0.System.Collections.IEnumerator.Reset()
   at satispu.Utils.Helpers.SortHelper.Sort(IEnumerable enumerable, String propertyName, Boolean desc) in D:\Documents and Settings\aolaol\Desktop\Source_Build2\satispu.Utils\Helpers\SortHelper.cs:line 24
   at satispu.Web.Controls.GridView.OnDataBinding(EventArgs e) in D:\Documents and Settings\aolaol\Desktop\Source_Build2\satispu.Web\Controls\GridView.cs:line 119
   at System.Web.UI.WebControls.DataBoundControl.PerformSelect()
   at System.Web.UI.WebControls.BaseDataBoundControl.DataBind()
   at System.Web.UI.WebControls.GridView.DataBind()
   at satispu.Web.JournalAdjustmentList.OnPreRender(EventArgs e) in D:\Documents and Settings\aolaol\Desktop\Source_Build2\satispu.Web\JournalAdjustmentList.aspx.cs:line 19
   at System.Web.UI.Control.PreRenderRecursiveInternal()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

What could be the issue or What would you reccomend to fix this or to enable sorting is this gridview.

Heres The Call

public IEnumerable<ADAdjustmentListData> Adjustment_GetListForAdministrator(int year) {
    return this.CreateStoreCommand(
        "GetListForAdministrator",
        CommandType.StoredProcedure,
        new SqlParameter("@Year", year)
    ).Materialize<ADAdjustmentListData>();
}




  void GridView_Sorting(object sender, System.Web.UI.WebControls.GridViewSortEventArgs e) {
            if (!String.IsNullOrEmpty(e.SortExpression)) {
                ToggleSorting(e);
            }
            if (Sorting != null) {
                e.SortDirection = SortDirection;
                Sorting(sender, e);
            }
        }

Answer 1

I assume you are using Entity Framework or LINQ to SQL... If so, you are using SortHelper inside of a LINQ query, which is going against the database. when it does that, it tries to translate SortHelper to SQL, it fails to do so, and throws that error. For instance, if you do this:

var q = from o in ctx.Users select new { SortedResults = SortHelper.Sort(q) };

This will fail because it will try to translate SortHelper.Sort to a SQL method. But this will work OK only if it doesn't modify the resultset in a way it doesn't expect.

var q = from o in ctx.Users select o;
return SortHelper.Sort(q);

The best way to ensure it will work is to call ToList() after the query - ToList() executes the query and then everything after is a LINQ to Objects operation:

var q = (from o in ctx.Users select o).ToList();
return SortHelper.Sort(q);