Sort or group by a computed property with EntityFramework

[Kornelije]

Background:

MSSQL - EntityFramework 4 - RIA services - Silverlight

DataGrid is used in RadDomainDataSource and RadDataPager.

Problem (short):

I want to use the GridView binding in localization scenario, including abilities to Sort and Group by a "computed" column.

Description:

I have an entity class named Item with the following properties:

public class Item
{
    String ID { get; set; }
    DateTime Timestamp { get; set; }
    String Parameters { get; set; }
    ItemType Type { get; set; } // Navigation property
}

I want to display three columns in the grid:

• Timestamp (bound to the Item.Timestamp)
• Type (bound to the Item.Type.Name)
• Description

The content of the Description field does not directly come from the Entity. There is a .resx dictionary with all of the ItemTypes:

for example:

ItemTypesStrings.Food = "{0} calories.";
ItemTypesStrings.Toy = "A Toy for kids - age: {0}" ;
ItemTypesStrings.Book = "Book named {0}, written by {1}";

I then use the Item.Parameters field with the Description template retrieved from the .RESX to generate what should be displayed in the grid cell. 

Attempted solutions:

1) First I tried to make a partial class for the Item entity on the client side. The class contains the calculated Description property that uses it's Type and Parameters to retrieve the localized string.  

The problem with this solution is that remote LINQ do not work since the server-side version of the entity does not have the Description property. When no grouping is set, the query executes correctly, but when I try to group by Description column, an exception is thrown.

2) Then I tried to use the binding for the column to use ValueConverter to convert the object into its String representation. This I did by setting:

column.DataMemberBinding = new Binding() { Converter = new ItemDescriptionValueConverter(); }

The problem with this solution is that I am then unable to group the items by it's description, which is an important feature.

Is there any way to enable grouping by columns bound this way?

[Vlad]

Hi,

 You cannot sort server-side by such property since it cannot be translated to SQL from EntityFramework. 
Usually you can group by a column with assigned IValueConverter - you can check this demo for more info. 

Regards,
Vlad
the Telerik team

[Kornelije]

Would you be so kind as to show me how can I do the same thing in code-behind.

For some reasons, I'd rather not use XAML to set group descriptors.

How to correctly write the binding code for group descriptors and value converter for a column in code-behind?

Thanks.

[Vlad]

Hello,

 You need to create ColumnGroupDescriptor, assign the Column property and add the descriptor to the grid GroupDescriptors collection. 

All the best,
Vlad
the Telerik team

[Kornelije]

Hi!

The scenario you have proposed does not work as desired.

When I create a ColumnGroupDescriptor and add it to the list of datagrid's group descriptors, the grid starts already grouped. Also, if I remove the item from the group bar, I am unable to put that column back onto the group bar. Instead, when I drag the column over the bar, red blocking icon appears (see the attached picture). The picture is localized (so it does not confuse you).

I want the column to be able to be dragged on top of the group bar so that the grid will be grouped by it.

I've made some changes in our model - now the Parameters and Timestamp properties are calculated.

In my example (see the picture), data can't be grouped by Parameters and by Timestamp because those are the calculated properties (values changed by the ValueConverters).

private void createColumns()
{
    GridViewDataColumn columnTime = createColumn("ID", "ID");
    grdItems.Columns.Add(columnTime);

    GridViewDataColumn columnItemTimestamp = createColumn("Timestamp", "Timestamp");
    grdItems.Columns.Add(columnItemTimestamp);
    columnItemTimestamp.DataMemberBinding
                        = new Binding() { Converter = new ItemTimestampVC() };

    GridViewDataColumn columnParameters = createColumn("Parameters", "Parameters");
    grdItems.Columns.Add(columnParameters);
    columnParameters.DataMemberBinding
                        = new Binding() { Converter = new ItemParametersVC() };

    GridViewDataColumn columnType = createColumn("Type", "Type");
    grdItems.Columns.Add(columnType);

    ColumnGroupDescriptor descriptor = new ColumnGroupDescriptor();
    descriptor.Column = columnItemTimestamp;
    grdItems.GroupDescriptors.Add(descriptor);

}

private GridViewDataColumn createColumn(String propertyName, String name)
{
    GridViewDataColumn column = new GridViewDataColumn();
    column.DataMemberBinding = new Binding(propertyName);
    column.Header = name;
    column.UniqueName = name;
    return column;
}

What to do to enable grouping and sorting by such columns by dragging the column header?

[Dimitrina]

Hello,

 Thank you for the additional information. I have tested the described problems but I was not able to reproduce them on this online demo.
I can group and then remove grouping as many times as I want. Then I can group on the calculated column as well (the 'Total value in stock' column). 

Would you please let me know if you can reproduce this behaviour with the latest binaries? 

Greetings,
Didie
the Telerik team

[Kornelije]

I'm using the 2012.1.215.1040 version (runtime v2.0.50727) of Telerik.Windows.Controls.GridView, although all libraries are from the same binaries version and the problem persists.

We have not yet ported to SL5, so we are not using the Telerik's SL5 controls. Don't know if that is the issue, because I believe the Demo you have linked to is using SL5 runtime and Telerik's SL5 controls. Or am I missing something about the versions? (I was unable to find Telerik's new controls for SL4.)

What could affect the ability of the column/property to be grouped by? Maybe my setup is somehow making this impossible. I see the demo is using GridViewExpressionColumn and I am doing it with plain GridViewDataColumn, because my calculation is done in the class' property itself, not in an external class, like in a demo. Is it possible to do this with the simple GridViewDataColumn and plain CLR property (which implements its calculation behaviour) or do I have to do it with GVEC?

[Dimitrina]

Hello,

 The described case should not be related to the version of the Silverlight.
I have attached the sample project I have used for testing. Please check it and let me know what I have to change in order to get the wrong behaviour?

All the best,
Didie
the Telerik team

[Kornelije]

Unfortunatelly, I am almost completely unable to modify the provided solution, since my problem is totally differently set up.

As the original post stated, the datagrid is used with DomainDataSource (over DataPager). 

Every solution proposed from Telerik included a MVVM approach, without using DomainDataSource, which I believe is the culprit. Please read the original post again to see what I'm trying to achieve.

The thing is that my objects (model) is EF4 entity which I can only extend by the partial class and calculated property. But then I can't page, since this property can't be calculated on Server SQL side. And the only proposed way to deal with this by Telerik is to create a value converter (IValueConverter). I have created one and since then I can't group/sort by this property.

if you will be providing a solution for this, please follow this pattern. I know how to make a simple MVVM scenario as described in every proposed demo and your solution. What I need is a solution for this specific set up.

Thank you again for your time.

[Rossen Hristov]

Hello,

There seems to be some huge misunderstanding in this thread. I will do my best to explain how things work.

Regardless of the fact whether you are using Telerik controls or not, Microsoft WCF RIA Serivices cannot perform server-side operations on properties/columns which do not exist in the server/database. So if there is a client-side computed/calculated/expression column, a query involving this column cannot be successfully transferred over the wire and executed against the database. The LINQ-to-Entities QueryProvider will complain about this. Furthermore, I am not sure whether you know this, but WCF RIA Services do not support "real" grouping in the database. When you do grouping -- WCF RIA Services simply sort the data and return it in a flat collection. All the grouping is performed on the client after the data arrives, for example by RadGridView. If you have paging -- only the current page of data will be grouped.

Having established these truths, you have two options:

1. If you want to perform server-side grouping, sorting and filtering you can do that only for "normal" columns which have a corresponding column in the database table. Otherwise the LINQ provider will fail. So you can add these calculated columns in your data layer. For example, you can create a new database view containing these calculations then expose this view to the client with WCF RIA Services. The client will never know that this is a view in the database and all of the properties will be "normal", i.e. they will be calculated on the data layer and not on the client. This is one possible approach.

2. You second option is to pull absolutely all data to the client, but you will have to give up paging if you do so. If you pull all the data to the client, for example by using the DomainContext.Load(EntityQuery<T>), and you then load this entities in a normal in-memory ObservableCollection<T>, you will be able to do absolutely anything, since the LINQ provider in this case will be LINQ-to-Objects. Since all the data will be in-memory you will be able to group, sort and filter on these calculated columns. But you will have to get all the entities to the client.

I know that neither option might be good enough, but this is a limitation imposed by the WCF RIA + EF combination and Telerik cannot do anything to change that. In other words, if you were using only the stock Microsoft controls instead of Telerik's you would be faced with the exactly same behavior.

I hope I have managed to explain how thing word. You can read my blog post for additional information.

Thank you for your understanding.

Greetings,

Ross
the Telerik team