How to use Kendo UI Grid with ToDataSourceResult(), IQueryable, ViewModel and AutoMapper?

Question

What is the best approach to load/filter/order a Kendo grid with the following classes:

Domain:

public class Car
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual bool IsActive { get; set; }
}

ViewModel

public class CarViewModel
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string IsActiveText { get; set; }
}

AutoMapper

Mapper.CreateMap<Car, CarViewModel>()
      .ForMember(dest => dest.IsActiveText, 
                 src => src.MapFrom(m => m.IsActive ? "Yes" : "No"));

IQueryable

var domainList = RepositoryFactory.GetCarRepository().GetAllQueryable();

DataSourceResult

var dataSourceResult = domainList.ToDataSourceResult<Car, CarViewModel>(request, 
                          domain => Mapper.Map<Car, ViewModel>(domain));

Grid

...Kendo()
  .Grid<CarViewModel>()
  .Name("gridCars")
  .Columns(columns =>
  {
     columns.Bound(c => c.Name);
     columns.Bound(c => c.IsActiveText);
  })
  .DataSource(dataSource => dataSource
     .Ajax()
     .Read(read => read.Action("ListGrid", "CarsController"))
  )
  .Sortable()
  .Pageable(p => p.PageSizes(true))

Ok, the grid loads perfectly for the first time, but when I filter/order by IsActiveText I get the following message:

Invalid property or field - 'IsActiveText' for type: Car

What is the best approach in this scenario?

what is the code that is initializing the grid? – CodingWithSpike May 10 '13 at 22:08 — CodingWithSpike, May 10 '13 at 22:08

score 13 · Answer 1 · edited Jun 26 '23 at 21:13

I don't like the way Kendo has implemented "DataSourceRequestAttribute" and "DataSourceRequestModelBinder", but thats another story.

To be able to filter/sort by VM properties which are "flattened" objects, try this:

Domain model:

public class Administrator
{
    public int Id { get; set; }

    public int UserId { get; set; }

    // Navigation Property to User
    public virtual User User { get; set; }
}

public class User
{
    public int Id { get; set; }

    public string UserName { get; set; }

    public string Email { get; set; }
}

View model:

public class AdministratorGridItemViewModel
{
    public int Id { get; set; }

    [Display(Name = "E-mail")]
    public string User_Email { get; set; }

    [Display(Name = "Username")]
    public string User_UserName { get; set; }
}

Extensions:

public static class DataSourceRequestExtensions
{
    /// <summary>
    /// Enable flattened properties in the ViewModel to be used in DataSource.
    /// </summary>
    public static void Deflatten(this DataSourceRequest dataSourceRequest)
    {
        foreach (var filterDescriptor in dataSourceRequest.Filters.Cast<FilterDescriptor>())
        {
            filterDescriptor.Member = DeflattenString(filterDescriptor.Member);
        }

        foreach (var sortDescriptor in dataSourceRequest.Sorts)
        {
            sortDescriptor.Member = DeflattenString(sortDescriptor.Member);
        }
    }

    private static string DeflattenString(string source)
    {
        return source.Replace('_', '.');
    }
}

Attributes:

[AttributeUsage(AttributeTargets.Method)]
public class KendoGridAttribute : ActionFilterAttribute
{
    public override void OnActionExecuting(ActionExecutingContext filterContext)
    {
        base.OnActionExecuting(filterContext);

        foreach (var sataSourceRequest in filterContext.ActionParameters.Values.Where(x => x is DataSourceRequest).Cast<DataSourceRequest>())
        {
            sataSourceRequest.Deflatten();
        }
    }
}

Controller action for Ajax data load:

[KendoGrid]
public virtual JsonResult AdministratorsLoad([DataSourceRequestAttribute]DataSourceRequest request)
    {
        var administrators = this._administartorRepository.Table;

        var result = administrators.ToDataSourceResult(
            request,
            data => new AdministratorGridItemViewModel { Id = data.Id, User_Email = data.User.Email, User_UserName = data.User.UserName, });

        return this.Json(result);
    }

Nice solution. Before I saw your solution, I tried using '_' in my dependent properties and thought that kendo will "OF COURSE" bind the properties but it didn't. I wonder why they didn't implemented this logic.. — yakya, Feb 17 '14 at 11:07
Absolutely brilliant, to use it with AutoMapper use : http://stackoverflow.com/a/1630696/260556 — hokkos, Mar 04 '14 at 13:12
Bit confused... this does the job of only mapping the required objects, but what about sorting/filtering? I may be missing something - but if I try and sort on a column (mapped to the VM) it throws an error as DataSourceRequest can't find the field name on the Model. — pfeds, Jun 10 '15 at 03:32
Here is a version of the extension for CompositeFilterDescriptor usage : http://pastebin.com/RVkuwhSS — Oswin, Aug 25 '16 at 09:06
In .net core the `ActionParameters` property has become `ActionArguments`. This makes the for loop: `foreach (var sataSourceRequest in context.ActionArguments.Values.OfType())` — H. de Jonge, Jan 12 '23 at 08:11

score 5 · Accepted Answer · answered May 11 '13 at 00:45

5

Something about that seems weird. You told Kendo UI to make a grid for CarViewModel

.Grid<CarViewModel>()

and told it there is an IsActive column:

columns.Bound(c => c.IsActive);

but CarViewModel doesn't have a column by that name:

public class CarViewModel
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string IsActiveText { get; set; }
}

My guess is that Kendo is passing up the field name from the CarViewModel IsActiveText, but on the server you are running ToDataSourceResult() against Car objects (an IQueryable<Car>), which do not have a property by that name. The mapping happens after the filtering & sorting.

If you want the filtering and sorting to happen in the database, then you would need to call .ToDataSourceResult() on the IQueryable before it runs against the DB.

If you have already fetched all your Car records out of the DB, then you can fix this by doing your mapping first, then calling .ToDataSourceResult() on an IQueryable<CarViewModel>.

answered May 11 '13 at 00:45

CodingWithSpike

42,906
18
101
138

I edited the topic, because i made a mistake when writing the grid code. I am actually displaying the IsActiveText property. I just wondering if there is another way to make a GetAllQueryable() against NHibernate and ToDataSourceResult() with AutoMapper without load all entities in memory. – rGiosa May 11 '13 at 11:48
`.ToDataSourceResult()` is adding expressions to the Linq queryable it is called on. If that queryable hasn't executed against the DB yet, then the filtering and sorting will be applied to the DB statement. – CodingWithSpike May 12 '13 at 16:43
And actually now that I look at your types again, filtering on `IsActive` against the DB will be difficult because you have `Car.IsActive` defined as a `bit` which isn't a JavaScript type. `boolean` would be better, but even then it doesn't match what is in the DB which is a `string`. If you want `IsActive` to automatically filter on the DB using `ToDataSourceResult` then you should make it a string. Otherwise you will have to write some code yourself to translate the `bit` to the appropriate `string`. – CodingWithSpike May 12 '13 at 16:47
I edited the topic (again!). IsActive is a boolean type. My point is that if ToDataSourceResult can make the "inverse path" using AutoMapper Map to translate the IsActiveText filter against IsActive DB column? – rGiosa May 13 '13 at 11:42
No, Kendo won't do that by default,, as it has no idea what AutoMapper is going to do. You might be able to alter the `request` object before the DB call, and change `IsActiveText` to `IsActive`, but it would be manual on your part. – CodingWithSpike May 13 '13 at 18:18

score 4 · Answer 3 · answered Jun 23 '15 at 14:49

František's solution is very nice! But be careful with casting Filters to FilterDescriptor. Some of them can be composite.

Use this implementation of DataSourceRequestExtensions instead of František's:

public static class DataSourceRequestExtensions
{
    /// <summary>
    /// Enable flattened properties in the ViewModel to be used in DataSource.
    /// </summary>
    public static void Deflatten(this DataSourceRequest dataSourceRequest)
    {
        DeflattenFilters(dataSourceRequest.Filters);

        foreach (var sortDescriptor in dataSourceRequest.Sorts)
        {
            sortDescriptor.Member = DeflattenString(sortDescriptor.Member);
        }
    }

    private static void DeflattenFilters(IList<IFilterDescriptor> filters)
    {
        foreach (var filterDescriptor in filters)
        {
            if (filterDescriptor is CompositeFilterDescriptor)
            {
                var descriptors
                    = (filterDescriptor as CompositeFilterDescriptor).FilterDescriptors;
                DeflattenFilters(descriptors);
            }
            else
            {
                var filter = filterDescriptor as FilterDescriptor;
                filter.Member = DeflattenString(filter.Member);
            }
        }
    }

    private static string DeflattenString(string source)
    {
        return source.Replace('_', '.');
    }
}

This is the right answer! I adapted your solution to replace a member that comes from the POST of the ViewModel with a property of the database model. Thank you! — Philippe, Nov 19 '21 at 05:09

score 4 · Answer 4 · answered May 11 '16 at 21:39

I followed the suggestion of CodingWithSpike and it works. I created an extension method for the DataSourceRequest class:

public static class DataSourceRequestExtensions
    {
        /// <summary>
        /// Finds a Filter Member with the "memberName" name and renames it for "newMemberName".
        /// </summary>
        /// <param name="request">The DataSourceRequest instance. <see cref="Kendo.Mvc.UI.DataSourceRequest"/></param>
        /// <param name="memberName">The Name of the Filter to be renamed.</param>
        /// <param name="newMemberName">The New Name of the Filter.</param>
        public static void RenameRequestFilterMember(this DataSourceRequest request, string memberName, string newMemberName)
        {
            foreach (var filter in request.Filters)
            {
                var descriptor = filter as Kendo.Mvc.FilterDescriptor;
                if (descriptor.Member.Equals(memberName))
                {
                    descriptor.Member = newMemberName;
                }
            } 
        }
    }

Then in your controller, add the using to the extension class and before the call to ToDataSourceResult(), add this:

request.RenameRequestFilterMember("IsActiveText", "IsActive");

As mentionned in other answers, be careful if you have a `CompositeFilterDescriptor` — Gabriel GM, Sep 29 '16 at 19:01
This answer is gold. It took me a while to find a decent solution (Telerik website failed). I had to add code to parse Composite Filters and Sorts but it works like a charm now. — pfeds, Nov 24 '20 at 08:57

score 2 · Answer 5 · answered Nov 27 '15 at 11:51

One good way to solve it if you use Telerik Data Access or any other IQueryable enabled interface/ORM over your data, is to create views directly in your database RDBMS that map one-to-one (with automapper) to your viewmodel.

Create the viewmodel you wish to use

public class MyViewModelVM
{
    public int Id { get; set; }
    public string MyFlattenedProperty { get; set; }
}

Create a view in your SQL Server (or whatever RDBMS you're working with) with columns exactly matching the viewmodel property names, and of course build your view to query the correct tables. Make sure you include this view in your ORM classes
```
CREATE VIEW MyDatabaseView
AS
SELECT
t1.T1ID as Id,
t2.T2SomeColumn as MyFlattenedProperty
FROM MyTable1 t1
INNER JOIN MyTable2 t2 on t2.ForeignKeyToT1 = t1.PrimaryKey
```
Configure AutoMapper to map your ORM view class to your viewmodel
```
Mapper.CreateMap<MyDatabaseView, MyViewModelVM>();
```

In your Kendo grid Read action, use the view to build your query, and project the ToDataSourceQueryResult using Automapper

public ActionResult Read([DataSourceRequest]DataSourceRequest request)
{
    if (ModelState.IsValid)
    {
        var dbViewQuery = context.MyDatabaseView;

        var result = dbViewQuery.ToDataSourceResult(request, r => Mapper.Map<MyViewModelVM>(r));

        return Json(result);
    }

    return Json(new List<MyViewModelVM>().ToDataSourceResult(request));
}

It's a bit of overhead but it will help you in achieve performance on two levels when working with large datasets:

You are using native RDBMS views which you can tune yourself. Will always outperform complex LINQ queries you build in .NET
You can leverage the Telerik ToDataSourceResult benefits of filtering, grouping, aggregating, ...

score 2 · Answer 6 · answered Jan 17 '17 at 14:53

I came across this same issue and after lots of research I resolved it permanently by using AutoMapper.QueryableExtensions library. It has an extension method that will project your entity query to your model and after that you can apply ToDataSourceResult extension method on your projected model.

public ActionResult GetData([DataSourceRequest]DataSourceRequest request)
{
     IQueryable<CarModel> entity = getCars().ProjectTo<CarModel>();
     var response = entity.ToDataSourceResult(request);
     return Json(response,JsonRequestBehavior.AllowGet);
}

Remember to configure Automapper using CreateMap.

Note: Here getCars will return IQueryable result car.

How to use Kendo UI Grid with ToDataSourceResult(), IQueryable, ViewModel and AutoMapper?

6 Answers6

Linked