GridTemplateColumn does not sort when bound to EntityDataSource navigation property

[Bryan]

I have a RadGrid bound to an EntityDataSource entity set that contains navigation properties. In the data model, these navigation properties are essentially simple lookup tables with foreign key references in the main entity table. Each navigation property is displayed in the grid using a GridTemplateColumn to allow for editing, filtering, and sorting based on the friendly text of the lookup table data rather than the associated FK IDs.

An example declaration of a template column (where SequenceId is a field on the main entity set and Sequence.Name is the EF navigation property):

<telerik:GridTemplateColumn UniqueName="SequenceId" DataField="SequenceId" SortExpression="Sequence.Name" HeaderText="Sequence">...</telerik:GridTemplateColumn>

This displays as expected and can be filtered and edited correctly (see full code below). But clicking on the header to sort the column by the friendly text (via the navigation property) results in the following exception being thrown:

"Input string was not in a correct format.Couldn't store <First Sequence> in Sequence.Name Column.  Expected type is Int32."

As part of troubleshooting this issue, I have added handlers for the EntityDataSource OnSelected and OnSelecting events and confirmed in a debug session that:

1. The SortExpression is correctly set in the OnSelecting event (e.g. "Sequence.Name ASC").
2. The returned Results collection in the OnSelected event is correctly sorted/ordered.

But when the Results collection is bound to the grid, it errors as listed above, and no sorting occurs.

Note: I can get sorting to work as expected by setting DataField="Sequence.Name" in the GridTemplateColumn, but then inserting and updating fails with this exception:

"A property named 'Sequence.Name' was not found on the entity during an insert, update, or delete operation. Check to ensure that properties specified as binding expressions are available to the data source."

Is there any way to get this to work with both? Sort based on a navigation property and insert/update based on the data model foreign key ID?

This issue can be reproduced with the following code:

Entity Framework Models:

public partial class SortTest
{
    public int SortTestId { get; set; }
    public string Description { get; set; }
    public Nullable<int> SequenceId { get; set; }

    public virtual Sequence Sequence { get; set; }
}

public partial class Sequence
{
    public Sequence()
    {
        this.SortTests = new HashSet<SortTest>();
    }

    public int SequenceId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<SortTest> SortTests { get; set; }
}

An aspx page containing a simple RadGrid and EntityDataSource declarations:

<telerik:RadAjaxPanel runat="server" ID="RadAjaxPanel1">
    <telerik:RadGrid ID="ResultGrid" DataSourceID="SortTestsEntitySource" runat="server" AllowSorting="True" AllowFilteringByColumn="true" AutoGenerateColumns="false">

        <MasterTableView CommandItemDisplay="Top" DataKeyNames="SortTestId" EditMode="Batch" PageSize="20" TableLayout="Auto" Width="95%" HeaderStyle-Width="100px">
            <BatchEditingSettings EditType="Row"></BatchEditingSettings>

            <Columns>
                <telerik:GridBoundColumn DataField="SortTestId" HeaderText="SortTestId"></telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="Description" HeaderText="Description"></telerik:GridBoundColumn>

                <telerik:GridTemplateColumn UniqueName="SequenceId" DataField="SequenceId" SortExpression="Sequence.Name" HeaderText="Sequence">
                    <FilterTemplate>
                        <telerik:RadComboBox ID="RadComboBoxModel" DataSourceID="SequencesEntitySource" DataTextField="Name" DataValueField="SequenceId"
                            AppendDataBoundItems="true" SelectedValue='<%# ((GridItem)Container).OwnerTableView.GetColumn("SequenceId").CurrentFilterValue %>'
                            runat="server" OnClientSelectedIndexChanged="SequencesIndexChanged">
                            <Items>
                                <telerik:RadComboBoxItem Text="All" />
                            </Items>
                        </telerik:RadComboBox>
                        <telerik:RadScriptBlock ID="RadScriptBlock1" runat="server">
                            <script type="text/javascript">
                                function SequencesIndexChanged(sender, args) {
                                    var tableView = $find("<%# ((GridItem)Container).OwnerTableView.ClientID %>");
                                    tableView.filter("SequenceId", args.get_item().get_value(), "EqualTo");
                                }
                            </script>
                        </telerik:RadScriptBlock>
                    </FilterTemplate>
                    <ItemTemplate>
                        <%# Eval("Sequence.Name") %>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <telerik:RadDropDownList style="width:auto;" runat="server" ID="SequenceDropDown" DataValueField="SequenceId" DataTextField="Name" DataSourceID="SequencesEntitySource"></telerik:RadDropDownList>
                    </EditItemTemplate>
                </telerik:GridTemplateColumn>
            </Columns>
        </MasterTableView>

    </telerik:RadGrid>
</telerik:RadAjaxPanel>

EntityDataSource Declarations:

<asp:EntityDataSource runat="server" ID="SortTestsEntitySource"
    ConnectionString="name=TelerikSortTestEntities"
    DefaultContainerName="TelerikSortTestEntities"
    OnSelecting="SortTestsEntitySource_OnSelecting"
    OnSelected="SortTestsEntitySource_OnSelected"
    EnableFlattening="False"
    EnableInsert="False"
    EnableUpdate="True"
    EnableDelete="False"
    EntitySetName="SortTests"
    EntityTypeFilter="SortTest"
    Include="Sequence">
</asp:EntityDataSource>
<asp:EntityDataSource runat="server" ID="SequencesEntitySource"
    ConnectionString="name=TelerikSortTestEntities"
    DefaultContainerName="TelerikSortTestEntities"
    EntitySetName="Sequences"
    EntityTypeFilter="Sequence">
</asp:EntityDataSource>

Code behind event handlers for the aspx page:

protected void SortTestsEntitySource_OnSelecting(object sender, EntityDataSourceSelectingEventArgs e)
{
    // verify that e.SelectArguments.SortExpression is correct in a Watch window or Immediate Window etc.
}

protected void SortTestsEntitySource_OnSelected(object sender, EntityDataSourceSelectedEventArgs e)
{
    // inspect the e.Results collection and verify it is correctly sorted/ordered
}

Exception full stack trace:

at System.Data.DataColumn.set_Item(Int32 record, Object value)
at System.Data.DataRow.set_Item(DataColumn column, Object value)
at System.Data.DataRow.set_Item(String columnName, Object value)
at Telerik.Web.UI.GridDataTableFromEnumerable.FillDataTableFromEnumerable(IQueryable enumerable)
at Telerik.Web.UI.GridDataTableFromEnumerable.FillData35()
at Telerik.Web.UI.GridDataTableFromEnumerable.FillData()
at Telerik.Web.UI.GridResolveEnumerable.Initialize()
at Telerik.Web.UI.GridResolveEnumerable.EnsureInitialized()
at Telerik.Web.UI.GridResolveEnumerable.get_DataTable()
at Telerik.Web.UI.GridEnumerableFromDataView..ctor(GridTableView owner, IEnumerable enumerable, Boolean CaseSensitive, Boolean autoGenerateColumns, GridColumnCollection presentColumns, String[] additionalField, Boolean retrieveAllFields, Boolean enableSplitHeaderText)
at Telerik.Web.UI.GridDataSourceHelper.CreateGridEnumerable(GridTableView owner, IEnumerable enumerable, Boolean caseSensitive, Boolean autoGenerateColumns, GridColumnCollection presentColumns, String[] additionalField, Boolean retrieveAllFields, Boolean enableSplitHeaderText)
at Telerik.Web.UI.GridDataSourceHelper.GetResolvedDataSource(GridTableView owner, Object dataSource, String dataMember, Boolean caseSensitive, Boolean autoGenerateColumns, GridColumnCollection presentColumns, String[] additionalField, Boolean retrieveAllFields, Boolean enableSplitHeaderText)
at Telerik.Web.UI.GridTableView.get_ResolvedDataSource()
at Telerik.Web.UI.GridTableView.CreateControlHierarchy(Boolean useDataSource)
at Telerik.Web.UI.GridTableView.CreateChildControls(IEnumerable dataSource, Boolean useDataSource)
at System.Web.UI.WebControls.CompositeDataBoundControl.PerformDataBinding(IEnumerable data)
at System.Web.UI.WebControls.DataBoundControl.OnDataSourceViewSelectCallback(IEnumerable data)
at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback)
at System.Web.UI.WebControls.DataBoundControl.PerformSelect()
at Telerik.Web.UI.GridTableView.PerformSelect()
at System.Web.UI.WebControls.BaseDataBoundControl.DataBind()
at Telerik.Web.UI.GridTableView.DataBind()
at Telerik.Web.UI.GridSortCommandEventArgs.ExecuteCommand(Object source)
at Telerik.Web.UI.RadGrid.OnBubbleEvent(Object source, EventArgs e)
at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
at Telerik.Web.UI.GridItem.OnBubbleEvent(Object source, EventArgs e)
at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
at Telerik.Web.UI.GridItem.OnBubbleEvent(Object source, EventArgs e)
at Telerik.Web.UI.GridItem.FireCommandEvent(String commandName, Object commandArgument)
at Telerik.Web.UI.RadGrid.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

[Kostadin]

Hi Bryan,

The reason for this exception is because the Sequence.Name field is not part of the RadGrid datasource. A possible solution is to add a new column with this filed and set its Visible property to false. This way the filed will be included and you will be able to sort by it.

<telerik:GridBoundColumn DataField="Sequence.Name" HeaderText="Sequence.Name" Visible="false"></telerik:GridBoundColumn>
<telerik:GridTemplateColumn UniqueName="SequenceId" DataField="SequenceId" SortExpression="Sequence.Name" HeaderText="Sequence">

Regards,
Kostadin
Telerik

 

 

[Bryan]

Hi Kostadin,

Thank you for investigating and responding. This does allow sorting to occur without error. Unfortunately, saving changes after an update or insert throws the same exception I noted above when I tried the workaround of setting DataField="Sequence.Name" on the original GridTemplateColumn:

"A property named 'Sequence.Name' was not found on the entity during an insert, update, or delete operation. Check to ensure that properties specified as binding expressions are available to the data source."

So I'm still not able to get both sorting and inserting/updating to work for navigation property columns.

Do you have any other suggestions?

Thanks again.

Bryan

[Bryan]

Kostadin,

After looking at this some more, I am able to get everything to work without error by using your suggestion and also adding the ReadOnly property set to true:

<telerik:GridBoundColumn DataField="Sequence.Name" ReadOnly="true" HeaderText="Sequence.Name" Visible="false"></telerik:GridBoundColumn>
<telerik:GridTemplateColumn UniqueName="SequenceId" DataField="SequenceId" SortExpression="Sequence.Name" HeaderText="Sequence">....</telerik:GridTemplateColumn>

While this "solves" the original problem, I am perplexed as to why it was failing at all. As I noted in the original question, by subscribing to the OnSelected event of the EntityDataSource I was able to inspect and confirm that the Results collection is returned without error both on initial page load and the sort operation. The entity set collection in each case is identical aside from being ordered differently. Yet, with the original markup that doesn't include this workaround, the RadGrid happily binds to the non-sorted Results set but fails miserably on the sorted one.

In any event, thanks again for pointing me in a direction that led to something that works.

Bryan