Sorting EntityFramework computed columns

[Jacob]

Hi,

How do I handle sorting in RadGrid using EntityDataSource on computed columns ( in EF partial classes eg.: DisplayName { get { retrun this.Name + " " + this.LastName;}}  ) ?

[Veli]

Hi Jacob,

What do you mean by "partial classes in the EF" ? If you mean the dynamic anonymous objects that are created when you project some entity properties in the LINQ select query, then RadGrid should successfully bind and sort these objects, provided that a public property (e.g. DisplayName) exists for these anonymous objects.

If you particularly need to sort calculated columns (GridCalculatedColumn type), you need to specify SortExpression="DisplayName" to indicate the column will sort the data items by the DisplayName property.

Kind regards,
Veli
the Telerik team

[Shukhrat Nekbaev]

Hi, facing sorting problems, I have grid binding to datasource. DataSource is llblgen typedview. When I try to sort only first (regular GridBoundColumn) is sorted, others give exception, saying that column wasn't found:
--------------------

Cannot find column RejectedInvoicesResult.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.IndexOutOfRangeException: Cannot find column RejectedInvoicesResult.
--------------------

what interesting here is that "Result" text is appended to valid columns when sorting is performed even I set SortExpression manually. Im using latest controls from 29.9.2010 build. Also if possible, can you hint me how to use GridCalculatedColumn for DateTime, in my cause I want to format the data as "M/yyyy" and in footer show number of those records, I tried one way(commented out), but footer value wasn't correct, when I removed DataFormatString footer became correct, but values displayed where in format I don't want.

Thank you!

<div id="gvDiv" style="width:91%">
        <telerik:RadGrid ID="gvSalesOverview" runat="server" DataSourceID="salesOverviewDS" Height="450px" AllowMultiRowSelection="false"
                        GridLines="None" Skin="Windows7" AutoGenerateColumns="False" AllowSorting="true"
                        AllowAutomaticInserts="false" AllowAutomaticUpdates="false" AllowAutomaticDeletes="false" ShowFooter="true" FooterStyle-HorizontalAlign="Right"
                        >
                <ClientSettings>
                    <Scrolling UseStaticHeaders="true" AllowScroll="true" />
                    <Selecting AllowRowSelect="true" UseClientSelectColumnOnly="true" EnableDragToSelectRows="false" />
                </ClientSettings>

                <MasterTableView DataKeyNames="Date" Width="100%" HierarchyLoadMode="ServerBind" DataSourceID="salesOverviewDS"
                    GridLines="Both" BorderColor="Silver" BorderWidth="1px" CellPadding="1" CellSpacing="1">
                        <Columns>
                            <telerik:GridBoundColumn ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="108px" DataField="Date" DataType="System.DateTime" DataFormatString="{0:M/yyyy}"  HeaderText="Period month/year" UniqueName="Date"></telerik:GridBoundColumn>
                            <%--<telerik:GridCalculatedColumn ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="108px" FooterText="# of months: " DataFields="Date" DataType="System.DateTime" Expression="{0}" DataFormatString="{0:M/yyyy}"  Aggregate="Count" HeaderText="Period month/year" UniqueName="Date"></telerik:GridCalculatedColumn>--%>
                            <telerik:GridCalculatedColumn ItemStyle-HorizontalAlign="Right" HeaderStyle-Width="80px" FooterText="Total: " DataType="System.Decimal" DataFields="PaidTotalIncome" DataFormatString="{0:C}" Expression="{0}" Aggregate="Sum" HeaderText="Total income" UniqueName="PaidTotalIncome" SortExpression="PaidTotalIncome"></telerik:GridCalculatedColumn>
                            <telerik:GridCalculatedColumn ItemStyle-HorizontalAlign="Right" HeaderStyle-Width="100px" FooterText="Total: " DataType="System.Decimal" DataFields="PaidInvoiceIncome" DataFormatString="{0:C}" Expression="{0}" Aggregate="Sum" HeaderText="Invoice payments" UniqueName="PaidInvoiceIncome" SortExpression="PaidInvoiceIncome"></telerik:GridCalculatedColumn>
                            <telerik:GridCalculatedColumn ItemStyle-HorizontalAlign="Right" HeaderStyle-Width="116px" FooterText="Total: " DataType="System.Decimal" DataFields="PaidCreditCardIncome" DataFormatString="{0:C}" Expression="{0}" Aggregate="Sum" HeaderText="Creditcard payments" UniqueName="PaidCreditCardIncome" SortExpression="PaidCreditCardIncome"></telerik:GridCalculatedColumn>
                            <telerik:GridCalculatedColumn ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="90px" FooterText="Total: " DataType="System.Int32" DataFields="PaidInvoiceOrders" HeaderText="# invoice orders" Expression="{0}" Aggregate="Sum" UniqueName="PaidInvoiceOrders" SortExpression="PaidInvoiceOrders"></telerik:GridCalculatedColumn>
                            <telerik:GridCalculatedColumn ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="110px" FooterText="Total: " DataType="System.Int32" DataFields="PaidCreditCardOrders" HeaderText="# creditcard orders" Expression="{0}" Aggregate="Sum" UniqueName="PaidCreditCardOrders" SortExpression="PaidCreditCardOrders"></telerik:GridCalculatedColumn>
                            <telerik:GridCalculatedColumn ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="100px" FooterText="Total: " DataType="System.Int32" DataFields="RejectedInvoices" HeaderText="Rejected invoices" Expression="{0}" Aggregate="Sum" UniqueName="RejectedInvoices" SortExpression="RejectedInvoices"></telerik:GridCalculatedColumn>
                            <telerik:GridCalculatedColumn ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="120px" FooterText="Total: " DataType="System.Int32" DataFields="PendingPaymentInvoices" HeaderText="Pending payment invoices" Expression="{0}" Aggregate="Sum" UniqueName="PendingPaymentInvoices" SortExpression="PendingPaymentInvoices"></telerik:GridCalculatedColumn>
                            <telerik:GridCalculatedColumn ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="120px" FooterText="Total: " DataType="System.Int32" DataFields="PendingApprovalInvoices" HeaderText="Pending approval invoices" Expression="{0}" Aggregate="Sum" UniqueName="PendingApprovalInvoices" SortExpression="PendingApprovalInvoices"></telerik:GridCalculatedColumn>
                            <telerik:GridCalculatedColumn ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="100px" FooterText="Total: " DataType="System.Int32" DataFields="RejectedCreditCards" Expression="{0}" Aggregate="Sum" HeaderText="Rejected creditcards" UniqueName="RejectedCreditCards" SortExpression="RejectedCreditCards"></telerik:GridCalculatedColumn>
                            <telerik:GridCalculatedColumn ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="140px" FooterText="Total: " DataType="System.Int32" DataFields="CancelledCreditCardPayments" HeaderText="Cancelled creditcard payments" Expression="{0}" Aggregate="Sum" UniqueName="CancelledCreditCardPayments" SortExpression="CancelledCreditCardPayments"></telerik:GridCalculatedColumn>
                        </Columns>
                </MasterTableView>
                </telerik:RadGrid>
</div>

<llblgenpro:LLBLGenProDataSource ID="salesOverviewDS" runat="server" DataContainerType="TypedView"
    EnablePaging="True" TypedViewTypeName="XXX.Data.TypedViewClasses.SalesOverviewTypedView, XXX.Data">
</llblgenpro:LLBLGenProDataSource>

[Veli]

Hello Shukhrat,

Currently, the GridCalculatedColumn does not support setting custom group sort and filter expressions. It builds automatic expressions by using a data field name formed by the UniqueName of the column + "Result", indicating this is a calculated column. So, effectively, if you have a calculated column with UniqueName="RejectedInvoices", it will sort your data objects if you expose a public property named "RejectedInvoicesResult".

Veli
the Telerik team

[Shukhrat Nekbaev]

Hi, thank for reply, I went "faster" way :)

protected void gvSalesOverview_SortCommand(object sender, GridSortCommandEventArgs e)
        {
            if (e.SortExpression.EndsWith("Result"))
            {
                PropertyInfo pi = typeof(GridSortCommandEventArgs).GetProperty("SortExpression", BindingFlags.Instance | BindingFlags.Public);
                FieldInfo fi = pi.GetBackingField();
                fi.SetValue(e, e.SortExpression.Replace("Result", string.Empty));
            }
        }

[The Wire]

Veli-

Can you show how to expose the public property for sorting on CalculatedColumns?  My calculated column unique name is "Rank" and the error is just like Shukhrat's: "Cannot find column RankResult".

Thanks

[Veli]

Hi,

As mentioned previously, the GridCalculatedColumn does not currently support specifying a sort or filter expression. Shukhrat showed one approach using reflection to change the actual sort expression in the GridSortCommandEventArgs object in RadGrid's SortCommand event. You an try this out, bearing in mind reflection is not permitted in medium trust environments.

Regards,
Veli
the Telerik team

[Shukhrat Nekbaev]

Hi,

Afaik, I used this small library:

https://github.com/jbevain/mono.reflection

I added a reference to it, it had that extension method defined. This solution works in case of my environment.

[The Wire]

I ended up just doing the calculation in my Stored Procedure and then just using a GridBoundColumn so sorting would work.

Thanks

[Paul]

Admin just suggest an alternative solution to Jacob's Original question which I also want to know.
Whether custom calculated column/property/field  defined in partial class of EF entity class are supported by RadGrid binding

[Kostadin]

Hello John,

I would recommend you to check out the following blog post for a possible solution.

Regards,
Kostadin
Telerik