Telerik MVC Grid - Sort dropdown-column by Text instead of Value

[Stefan]

Hello,

in our grid we have a ForeignKey-column to show the Text of a dropodown instead of the Value (see discussion here: https://www.telerik.com/forums/telerik-mvc-grid---show-selectlistitem-text-instead-of-value):

columns.ForeignKey(c => c.STATUS, Helper.IDTB_PRUEFUNG.Dropdowns.Status, "Value", "Text");

This is the corresponding dropdown:

public class Dropdowns
{
    public static List<SelectListItem> Status
    {
        get
        {
            return new List<SelectListItem>()
            {
                new SelectListItem()
                {
                    Text = "New",
                    Value = "NEU",
                    Selected = true
                },                
                new SelectListItem()
                {
                    Text = "Locked",
                    Value = "GESPERRT"
                }
            };
        }
    }
}

The Read-Action on the server is fairly simple:

[AcceptVerbs(HttpVerbs.Post)]
public JsonResult Read([DataSourceRequest] DataSourceRequest request, int lot_id)
{
    var pruefungen = db.IDTB_PRÜFUNG.Where(p => p.LOT_ID == lot_id);

    return Json(pruefungen.ToDataSourceResult(request), JsonRequestBehavior.AllowGet);
}

Now when we sort this column, the sorting is applied on the Value. I assume this is because the sorting is done on the database level? (We use Entity Framework here.)

Is there a way to have the sorting applied on the Text instead of the Value?

(I am aware that this is asking for a bit of magic here, since the database only knows about the Value and knows nothing about the Text. So I would even be happy for some hints in the a general direction to solving this (maybe client-side sorting, or sth. else). :) )

[Eyup]

Hi Stefan,

 

Thank you for contacting us.

You can achieve this requirement using the Compare function:
https://docs.telerik.com/aspnet-mvc/api/kendo.mvc.ui.fluent/gridboundcolumnsortablebuilder#comparesystemstring

One key condition is to disable the .ServerOperation(false) of the Grid's DataSource instance.

Do you like this idea? Let me know what you think.

 

Regards,
Eyup
Progress Telerik

[Stefan]

Hello Eyup,

thank you for the information.

Unfortunately the compare function also seems to see only the Value ("NEU", "GESPERRT") of a dropdown, and does not know about the Text  ("New," "Locked")?!

Is there a way to have the Text also available?

Kind regards.

[Eyup]

Yes, for the Grid to be able to sort with the Text content of the cells, it needs to have the Text field added to its own DataSource.

A similar scenario is discussed in the following forum post that I wanted to share with you:

• Sort By Foreign Key Text

In general, as explained by my colleague Alex, a possible approach would be to implement the "IComparable" interface or alternatively modify the request.

Another approach that is not discussed in the forum post is to add the text field of the ForeignKey column to the Model that is bound to the Grid. Then in the document.ready() event gets a reference of the header for the column and changes the data-field attribute to point to the text field. For example:

@{
    var categories = Enumerable.Range(3,10).Select(x => new CategoryViewModel()
    {
        CategoryID = x,
        CategoryName = "Category " + x
    }).ToList();
    categories.Add(new CategoryViewModel(){CategoryID = 1, CategoryName = "AAA"});
    categories.Add(new CategoryViewModel(){CategoryID = 2, CategoryName = "BBB"});
}

@(Html.Kendo().Grid<Kendo.Mvc.Examples.Models.ProductViewModel>()
    .Name("grid")
    .Columns(columns =>
    {
        columns.Bound(p => p.ProductName);
        columns.ForeignKey(p => p.CategoryID, (System.Collections.IEnumerable)@categories, "CategoryID", "CategoryName")
            .Title("Category").Width(150);
        columns.Bound(p => p.UnitPrice).Width(150);
        columns.Command(command => command.Destroy()).Width(110);
    })
    ...
    .DataSource(dataSource => dataSource
        .Ajax()
        .Batch(true)
        .PageSize(20)
        .ServerOperation(false)
        ...
        .Model(model =>
        {
            model.Id(p => p.ProductID);
            model.Field(p => p.ProductID).Editable(false);
            model.Field(p => p.CategoryID).DefaultValue(1);
        })
        .Read(read => read.Action("ForeignKeyColumn_Read", "Grid"))
        ...
    )
)
<script type="text/javascript">
    $(function () {
        var grid = $("#grid").data("kendoGrid");
        grid.thead.find("th[data-field='CategoryID']").attr("data-field", "Category.CategoryName");
    });
    ...
</script>

Here is a runnable example that illustrates the approach:

• Telerik REPL for ASP.NET Core Example

In the example above the ProductViewModel contains a "Category" property, along with the ForeignKey "CategoryID", thus the data-field attribute points to the Category.CategoryName field.

Do you find this explanation helpful?