Hi - I'm using an entity with a couple of "Fields on Related Fields" defined. I provide 2 way databinding to an ASPxGridView via the LLBLGenProDataSource2 object data source. I set up the appropriate prefetch paths in Page_Load to populate the fields on related fields automatically. I'm forcing server mode paging and sorting on the ASPxGridView (DataSourceForceStandardPaging="True").

Everything works fine except for sorting on the two Fields on Related Fields. This gives the error message attached. Sorting on all the "native" fields works fine: it's just the related ones that fail.

I'm wondering if this is because the grid is set to server mode? It seems possible that under the hood LLBLGen is trying to send a query to the database to sort on the related fields, but of course they don't exist in the entity's base table and won't be loaded until after the initial entity list is created: sort of a catch 22.

Anyway, any workaround or fix to this (that doesn't involve sorting on the client!) would be appreciated.

Thanks

Rich

LLBLGen Pro 2.6 Final, ASPxGridView 8.2.4, ASP.NET 2, Adapter, SQL Server 2005, runtime library version 2.6.8.1114

<llblgenpro:LLBLGenProDataSource2 ID="dsMain" runat="server" CacheLocation="ASPNetCache" EnablePaging="True" AdapterTypeName="LLBLGen.ConservationEvidence.DatabaseSpecific.DataAccessAdapter, LLBLGen.ConservationEvidenceDBSpecific" DataContainerType="EntityCollection" EntityFactoryTypeName="LLBLGen.ConservationEvidence.FactoryClasses.CaseWithAffiliationIDEntityFactory, LLBLGen.ConservationEvidence">
    <InsertParameters>
        <asp:SessionParameter Name="AddedByUserID" SessionField="UserID" />
    </InsertParameters>
</llblgenpro:LLBLGenProDataSource2>

override protected void Page_Load(object sender, EventArgs e)
{
    base.Page_Load(sender, e);

    dsMain.PrefetchPathToUse = new PrefetchPath2((int)EntityType.CaseWithAffiliationIDEntity);
    dsMain.PrefetchPathToUse.Add(CaseWithAffiliationIDEntity.PrefetchPathAspnetUser);
    dsMain.PrefetchPathToUse.Add(CaseWithAffiliationIDEntity.PrefetchPathUserAffiliation);
}

/// <summary>
/// bind to the search terms
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void gridMain_DataBinding(object sender, EventArgs e)
{
    gridMain.DetailRows.CollapseAllRows();

    dsMain.FilterToUse = null;

    IRelationPredicateBucket bucket = new RelationPredicateBucket();
    if (ddlState.SelectedIndex > 0)
        bucket.PredicateExpression.Add(CaseWithAffiliationIDFields.StateID == Convert.ToByte(ddlState.SelectedValue));

    if (ddlAddedBy.SelectedIndex > 0)
        bucket.PredicateExpression.Add(CaseWithAffiliationIDFields.AddedByUserID == new Guid(ddlAddedBy.SelectedValue));

    if (ddlAffiliation.SelectedIndex > 0)
    {
        bucket.Relations.Add(CaseWithAffiliationIDEntity.Relations.UserAffiliationEntityUsingAddedByUserID);
        bucket.PredicateExpression.Add(UserAffiliationFields.AffiliationID == Convert.ToInt16(ddlAffiliation.SelectedValue));
    }

    if (bucket.PredicateExpression.Count > 0)
        dsMain.FilterToUse = bucket;
}

protected void btnFilter_Click(object sender, EventArgs e)
{
    dsMain.Refetch = true;
    gridMain.DataBind();
}

Please post the declarative aspx of the Grid. I suspect you are setting a wrong SortExpression to that field.

Hi daelmo - here's the declarative code but I'm not sure it's going to help as I found this tucked away in the LLBLGen docs (Generated code - Databinding with ASP.NET 2.0, Adapter):

Server-side sorting only uses EntityField2 objects, so if the entity has a field which isn't a field mapped onto a table/view field, it's ignored in the server-side sorting actions because it's not part of the query send to the database. This is also true for fields mapped onto related fields. In these situations, use client-side sorting.

<dxwgv:ASPxGridView ID="gridMain" runat="server" SkinID="EditableAddDelete" AutoGenerateColumns="False" DataSourceID="dsMain" KeyFieldName="ID"
    OnHtmlCommandCellPrepared="gridMain_HtmlCommandCellPrepared" OnCustomButtonCallback="gridMain_CustomButtonCallback" OnRowDeleting="gridMain_RowDeleting" OnDataBinding="gridMain_DataBinding" OnDetailRowExpandedChanged="gridMain_DetailRowExpandedChanged" OnCustomCallback="gridMain_CustomCallback">
    <Columns>
        <dxwgv:GridViewDataTextColumn FieldName="Title" VisibleIndex="0">
            <PropertiesTextEdit MaxLength="500" EncodeHtml="False">
                <ValidationSettings SetFocusOnError="True">
                    <RequiredField IsRequired="True" />
                </ValidationSettings>
            </PropertiesTextEdit>
            <EditFormSettings VisibleIndex="0" />
        </dxwgv:GridViewDataTextColumn>
        <dxwgv:GridViewDataComboBoxColumn Caption="State" FieldName="StateID" VisibleIndex="1">
            <PropertiesComboBox ValueType="System.String" DataSourceID="dsState" TextField="Name" ValueField="ID">
                <ValidationSettings SetFocusOnError="True">
                    <RequiredField IsRequired="True" />
                </ValidationSettings>
            </PropertiesComboBox>
            <EditFormSettings VisibleIndex="1" />
        </dxwgv:GridViewDataComboBoxColumn>
        <dxwgv:GridViewDataTextColumn FieldName="ConservationEvidenceVolumeID" VisibleIndex="2" Caption="Vol" ReadOnly="True">
            <EditFormSettings Visible="False" />
        </dxwgv:GridViewDataTextColumn>
        <dxwgv:GridViewDataComboBoxColumn Caption="Year" FieldName="ConservationEvidenceVolumeID" VisibleIndex="3">
            <PropertiesComboBox ValueType="System.String" DataSourceID="dsCEVolume" TextField="Year" ValueField="Volume">
            </PropertiesComboBox>
            <EditFormSettings VisibleIndex="2" />
        </dxwgv:GridViewDataComboBoxColumn>
        <dxwgv:GridViewDataTextColumn FieldName="Authors" Visible="False" VisibleIndex="3">
            <PropertiesTextEdit MaxLength="500">
                <ValidationSettings SetFocusOnError="True">
                    <RequiredField IsRequired="True" />
                </ValidationSettings>
            </PropertiesTextEdit>
            <EditFormSettings Visible="True" VisibleIndex="3" />
        </dxwgv:GridViewDataTextColumn>
        <dxwgv:GridViewDataTextColumn FieldName="FullNames" Caption="Author(s) Full Name(s)" Visible="False" VisibleIndex="3">
            <PropertiesTextEdit MaxLength="500">
                <ValidationSettings SetFocusOnError="True">
                    <RequiredField IsRequired="True" />
                </ValidationSettings>
            </PropertiesTextEdit>
            <EditFormSettings Visible="True" VisibleIndex="4" />
        </dxwgv:GridViewDataTextColumn>
        <dxwgv:GridViewDataTextColumn FieldName="CorrespondingAuthorEmail" Visible="False" VisibleIndex="3">
            <PropertiesTextEdit MaxLength="50">
            </PropertiesTextEdit>
            <EditFormSettings Visible="True" VisibleIndex="5" />
        </dxwgv:GridViewDataTextColumn>
        <dxwgv:GridViewDataSpinEditColumn FieldName="FirstPage" VisibleIndex="4">
            <PropertiesSpinEdit DisplayFormatString="g" MaxLength="5" MaxValue="32767" NumberType="Integer" NumberFormat="Custom">
            </PropertiesSpinEdit>
            <EditFormSettings VisibleIndex="8" />
        </dxwgv:GridViewDataSpinEditColumn>
        <dxwgv:GridViewDataSpinEditColumn FieldName="LastPage" VisibleIndex="5">
            <PropertiesSpinEdit DisplayFormatString="g" MaxLength="5" MaxValue="32767" NumberType="Integer" NumberFormat="Custom">
            </PropertiesSpinEdit>
            <EditFormSettings VisibleIndex="9" />
        </dxwgv:GridViewDataSpinEditColumn>
        <dxwgv:GridViewDataTextColumn FieldName="Organisation" Visible="False" VisibleIndex="5">
            <PropertiesTextEdit MaxLength="500">
            </PropertiesTextEdit>
            <EditFormSettings Visible="True" VisibleIndex="6" />
        </dxwgv:GridViewDataTextColumn>
        <dxwgv:GridViewDataTextColumn FieldName="Site" Visible="False" VisibleIndex="5">
            <PropertiesTextEdit MaxLength="500">
            </PropertiesTextEdit>
            <EditFormSettings Visible="True" VisibleIndex="13" />
        </dxwgv:GridViewDataTextColumn>
        <dxwgv:GridViewDataTextColumn FieldName="Involvement" Visible="False" VisibleIndex="5">
            <PropertiesTextEdit MaxLength="500">
            </PropertiesTextEdit>
            <EditFormSettings Visible="True" VisibleIndex="7" />
        </dxwgv:GridViewDataTextColumn>
        <dxwgv:GridViewDataDateColumn FieldName="DateAdded" ReadOnly="True" SortIndex="0" SortOrder="Descending" VisibleIndex="6">
            <PropertiesDateEdit DisplayFormatString="%d MMM yy">
            </PropertiesDateEdit>
            <EditFormSettings Visible="False" />
        </dxwgv:GridViewDataDateColumn>
        <dxwgv:GridViewDataTextColumn FieldName="Background" VisibleIndex="10" Visible="False">
            <EditFormSettings Visible="False" />
        </dxwgv:GridViewDataTextColumn>
        <dxwgv:GridViewDataTextColumn FieldName="Action" VisibleIndex="9" Visible="False">
            <EditFormSettings Visible="False" />
        </dxwgv:GridViewDataTextColumn>
        <dxwgv:GridViewDataTextColumn FieldName="Consequences" VisibleIndex="8" Visible="False">
            <EditFormSettings Visible="False" />
        </dxwgv:GridViewDataTextColumn>
        <dxwgv:GridViewDataTextColumn FieldName="Summary" VisibleIndex="7" Visible="False">
            <EditFormSettings Visible="False" />
        </dxwgv:GridViewDataTextColumn>
        <dxwgv:GridViewDataMemoColumn FieldName="Keywords" VisibleIndex="7" Visible="False">
            <EditFormSettings Visible="True" ColumnSpan="2" VisibleIndex="14" />
        </dxwgv:GridViewDataMemoColumn>
        <dxwgv:GridViewDataTextColumn Caption="Added By" FieldName="UserName" ReadOnly="True" VisibleIndex="7">
            <EditFormSettings Visible="False" />
        </dxwgv:GridViewDataTextColumn>
        <dxwgv:GridViewDataComboBoxColumn Caption="Affiliation" FieldName="AffiliationID" ReadOnly="True" VisibleIndex="8">
            <PropertiesComboBox DataSourceID="dsAffiliation" TextField="Name" ValueField="ID" ValueType="System.String">
            </PropertiesComboBox>
            <EditFormSettings Visible="False" />
        </dxwgv:GridViewDataComboBoxColumn>
        <dxwgv:GridViewDataTextColumn FieldName="Habitat" Visible="False" VisibleIndex="7">
            <PropertiesTextEdit MaxLength="500">
            </PropertiesTextEdit>
            <EditFormSettings Visible="True" VisibleIndex="10" />
        </dxwgv:GridViewDataTextColumn>
        <dxwgv:GridViewDataTextColumn FieldName="Region" Visible="False" VisibleIndex="7">
            <PropertiesTextEdit MaxLength="500">
            </PropertiesTextEdit>
            <EditFormSettings Visible="True" VisibleIndex="12" />
        </dxwgv:GridViewDataTextColumn>
        <dxwgv:GridViewCommandColumn ButtonType="Button" Caption="Publish" VisibleIndex="9">
            <CustomButtons>
                <dxwgv:GridViewCommandColumnCustomButton ID="publish" Text="Publish">
                </dxwgv:GridViewCommandColumnCustomButton>
                <dxwgv:GridViewCommandColumnCustomButton ID="pull" Text="Pull">
                </dxwgv:GridViewCommandColumnCustomButton>
            </CustomButtons>
        </dxwgv:GridViewCommandColumn>
    </Columns>
    <Templates>
        <EditForm>
            <div style="padding:4px 4px 3px 4px" class="editform">
                <dxwgv:ASPxGridViewTemplateReplacement ID="Editors" ReplacementType="EditFormEditors" runat="server" />
                <br />
                Summary
                <dxhe:ASPxHtmlEditor ID="ASPxHtmlEditor4" runat="server" Html='<%# Bind("Summary") %>' />
                <br />
                Background
                <dxhe:ASPxHtmlEditor ID="ASPxHtmlEditor1" runat="server" Html='<%# Bind("Background") %>' />
                <br />
                Action
                <dxhe:ASPxHtmlEditor ID="ASPxHtmlEditor2" runat="server" Html='<%# Bind("Action") %>' />
                <br />
                Consequences
                <dxhe:ASPxHtmlEditor ID="ASPxHtmlEditor3" runat="server" Html='<%# Bind("Consequences") %>' />
            </div>
            <div style="text-align:right; padding:2px 2px 2px 2px">
                <dxwgv:ASPxGridViewTemplateReplacement ID="UpdateButton" ReplacementType="EditFormUpdateButton" runat="server" />
                <dxwgv:ASPxGridViewTemplateReplacement ID="CancelButton" ReplacementType="EditFormCancelButton" runat="server" />
            </div>
        </EditForm>
        <DetailRow>
            <table width="100%" cellpadding="5px">
                <tr valign="top">
                    <td>
                        <h3>Attachments</h3>
                        <asp:Repeater ID="repAttachments" runat="server" Visible="false">
                            <ItemTemplate>
                                <rts:AnnotatedFileLink ID="fl1" runat="server" Filename='<%# Eval("Filename") %>' Description='<%# Eval("Description") %>' />
                                <br />
                            </ItemTemplate>
                        </asp:Repeater>
                        <asp:Literal ID="litAttachmentsNone" runat="server" Text="NONE" />
                        <p><asp:HyperLink ID="hypAttachments" NavigateUrl='<%# GetPopupScript("Attachments", Eval("ID"), Eval("Title")) %>' runat="server">Edit Attachments</asp:HyperLink></p>
                    </td>
                    <td>
                        <h3>Actions</h3>
                        <asp:BulletedList ID="bulActions" runat="server" Visible="false">
                        </asp:BulletedList>
                        <asp:Literal ID="litActionsNone" runat="server" Text="NONE" />
                        <p><asp:HyperLink ID="hypActions" NavigateUrl='<%# GetPopupScript("Actions", Eval("ID"), Eval("Title")) %>' runat="server">Edit Actions</asp:HyperLink></p>
                    </td
                    <td>
                        <h3>Habitats</h3>
                        <asp:BulletedList ID="bulHabitats" runat="server" Visible="false">
                        </asp:BulletedList>
                        <asp:Literal ID="litHabitatsNone" runat="server" Text="NONE" />
                        <p><asp:HyperLink ID="hypHabitats" NavigateUrl='<%# GetPopupScript("Habitats", Eval("ID"), Eval("Title")) %>' runat="server">Edit Habitats</asp:HyperLink></p>
                    </td>
                </tr>
                <tr valign="top">
                    <td>
                        <h3>Organisations</h3>
                        <asp:BulletedList ID="bulOrganisations" runat="server" Visible="false">
                        </asp:BulletedList>
                        <asp:Literal ID="litOrganisationsNone" runat="server" Text="NONE" />
                        <p><asp:HyperLink ID="hypOrganisations" NavigateUrl='<%# GetPopupScript("Organisations", Eval("ID"), Eval("Title")) %>' runat="server">Edit Organisations</asp:HyperLink></p>
                    </td>
                    <td>
                        <h3>Issues</h3>
                        <asp:BulletedList ID="bulIssues" runat="server" Visible="false">
                        </asp:BulletedList>
                        <asp:Literal ID="litIssuesNone" runat="server" Text="NONE" />
                        <p><asp:HyperLink ID="hypIssues" NavigateUrl='<%# GetPopupScript("Issues", Eval("ID"), Eval("Title")) %>' runat="server">Edit Issues</asp:HyperLink></p>
                    </td>
                    <td>
                        <h3>Regions</h3>
                        <asp:BulletedList ID="bulRegions" runat="server" Visible="false">
                        </asp:BulletedList>
                        <asp:Literal ID="litRegionsNone" runat="server" Text="NONE" />
                        <p><asp:HyperLink ID="hypRegions" NavigateUrl='<%# GetPopupScript("Regions", Eval("ID"), Eval("Title")) %>' runat="server">Edit Regions</asp:HyperLink></p>
                    </td>
                </tr>
            </table>
        </DetailRow>
    </Templates>
    <SettingsDetail ShowDetailRow="True" />
    <SettingsEditing PopupEditFormHeight="550px" />
</dxwgv:ASPxGridView>


<llblgenpro:LLBLGenProDataSource2 ID="dsMain" runat="server" CacheLocation="ASPNetCache" EnablePaging="True"
    AdapterTypeName="LLBLGen.ConservationEvidence.DatabaseSpecific.DataAccessAdapter, LLBLGen.ConservationEvidenceDBSpecific" DataContainerType="EntityCollection" EntityFactoryTypeName="LLBLGen.ConservationEvidence.FactoryClasses.CaseWithAffiliationIDEntityFactory, LLBLGen.ConservationEvidence">
    <InsertParameters>
        <asp:ControlParameter Name="AddedByUserName" ControlID="litUserName" PropertyName="Text" />
    </InsertParameters>
</llblgenpro:LLBLGenProDataSource2>

You may turn off LivePersistence and on the PerformSelect event handler, and trap in sorting on these fields, and then you should define and pass the appropriate relations to the fetch method.

So that a JOIN to the related table is done to enable sorting on its fields.