Enable ForeignKey Column Sorting by Text

Environment

Product	Telerik UI for ASP.NET MVC Grid
Operating System	Windows 10 64bit

Description

How can I enable ForeignKey column sorting by text in the Telerik UI for ASP.NET MVC Grid?

Solution

The following example demonstrates how to enable the sort-by-text functionality in a ForeignKey column by using a calculated field in a Grid.

• Create an additional text field that will be used for sorting in the ViewModel of the Grid.

    public class ProductViewModel
    {
        public string CategoryName
        { 
            get; 
            set; 
        }
    }

• Prepopulate the data for the Foreign Key column within the controller.

    public class GridController : Controller
    {
        public IActionResult Index()
        {
            ViewData["categories"] = GetCategories();
            return View();
        }
    }

• Create a dictionary of key-value pairs using the prepopulated data.

    <script type="text/javascript">
        var categories= @Html.Raw(Json.Serialize(@ViewData["categories"])); //serialize the data

        //create dictionary of text-values for the FKC
        var categoriesDict = {};
        for (var i = 0; i < categories.length; i++) {
            categoriesDict[categories[i].CategoryID] = categories[i].CategoryName;
        }
    </script>

• Bind the additional text field using the .Bound() configuration option and calculate the field by utilizing the .ClientTemplate() setting.

    @(Html.Kendo().Grid<Kendo.Mvc.Examples.Models.ProductViewModel>()
        .Name("grid")
        .Columns(columns =>
        {
            columns.Bound(p => p.CategoryName).ClientTemplate("#=calculateField(CategoryID)#").EditorTemplateName("CategoryNameEditor");

        })
    )


    <script type="text/javascript">
        function calculateField(categoryId){
            return categoriesDict[categoryId];
        }
    </script>

• Specify a DropDownList for the text field editor in the ~View/Shared/EditorTemplates folder and specify the value field with the data-bind attribute.

    @model ProductViewModel

    @(Html.Kendo().DropDownListFor(m => m.CategoryName)
        .DataValueField("CategoryID")
        .DataTextField("CategoryName")
        .AutoBind(false)
        .HtmlAttributes(new {data_bind="value:CategoryID"})
        .BindTo((System.Collections.IEnumerable)ViewData["categories"])
    )

Example:

     public class ProductViewModel
     {
         public int ProductID
         {
             get;
             set;
         }

         public string ProductName
         {
             get;
             set;
         }

         public decimal UnitPrice
         {
             get;
             set;
         }


         [UIHint("CategoryNameEditor")]
         public string CategoryName
         {
             get;
             set;
         }

         public int? CategoryID { get; set; }
     }

    @(Html.Kendo().Grid<Kendo.Mvc.Examples.Models.ProductViewModel>()
            .Name("grid")
            .Columns(columns =>
            {
                columns.Bound(p => p.ProductName);
                columns.Bound(p => p.CategoryName).ClientTemplate("#=calculateField(CategoryID)#").EditorTemplateName("CategoryNameEditor").Width(200);
                columns.Bound(p => p.UnitPrice).Format("{0:c}").Width(200);
                columns.Command(command => command.Destroy()).Width(150);
            })
            .ToolBar(toolBar =>
            {
                toolBar.Create();
                toolBar.Save();
            })
            .Editable(editable => editable.Mode(GridEditMode.InCell))
            .Pageable()
            .Sortable()
            .Scrollable()
            .HtmlAttributes(new { style = "height:540px;" })
            .DataSource(dataSource => dataSource
                .Ajax()
                .Batch(true)
                .PageSize(20)
                .ServerOperation(false)
                .Model(model =>
                {
                    model.Id(p => p.ProductID);
                    model.Field(p => p.ProductID).Editable(false);
                })
                .Read(read => read.Action("Products_Read", "Grid"))
                .Update(update => update.Action("Products_Update", "Grid"))
                .Create(create => create.Action("Products_Create", "Grid"))
                .Destroy(destroy => destroy.Action("Products_Destroy", "Grid"))
            )
    )

    public class GridController : Controller
    {
        public IActionResult Index()
        {
            ViewData["categories"] = GetCategories();
            return View();
        }
        public IEnumerable<CategoryViewModel> GetCategories()
        {
            var firms = Enumerable.Range(1, 10)
               .Select(i => new CategoryViewModel
               {
                   CategoryID = i,
                   CategoryName = "CategoryName " + i
               }).ToList();

            return firms;
        }
        public IActionResult Products_Read([DataSourceRequest] DataSourceRequest request)
        {
            IEnumerable<ProductViewModel> products = GetProducts();

            return Json(products.ToDataSourceResult(request));
        }

        private static IEnumerable<ProductViewModel> GetProducts()
        {
            return Enumerable.Range(1, 20)
                .Select(i => new ProductViewModel
                {
                    ProductID = i,
                    ProductName = "ProductName" + i,
                    UnitPrice = 1250.50M,
                    CategoryID = i % 5
                });
        }
    }

    @model ProductViewModel

    @(Html.Kendo().DropDownListFor(m=>m.CategoryName)
        .DataValueField("CategoryID")
        .DataTextField("CategoryName")
        .AutoBind(false)
        .HtmlAttributes(new {data_bind="value:CategoryID"})
        .BindTo((System.Collections.IEnumerable)ViewData["categories"])
    )

    <script type="text/javascript">
        var categories= @Html.Raw(Json.Serialize(@ViewData["categories"]));

        //create dictionary of text-values for the FKC
        var categoriesDict = {};
        for (var i = 0; i < categories.length; i++) {
            console.log(categories[i]);
            categoriesDict[categories[i].CategoryID] = categories[i].CategoryName;
        }

        function calculateField(firmId){
            return categoriesDict[firmId];
        }
    </script>