How to use Kendo UI Grid with ToDataSourceResult(), IQueryable<T>, 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?

Solution 1

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>.

Solution 2

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; }

    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; }

    [Displaye(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);
    }

Solution 3

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('_', '.');
    }
}

Solution 4

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");

Solution 5

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.

1. Create the viewmodel you wish to use

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

2. 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
   

3. Configure AutoMapper to map your ORM view class to your viewmodel

   Mapper.CreateMap<MyDatabaseView, MyViewModelVM>();
   

4. 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, ...

View more solutions

Comments

[rGiosa]

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.

[CodingWithSpike]

.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]

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.

[rGiosa]

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?

[CodingWithSpike]

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.

[sotn]

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..

[hokkos]

Absolutely brilliant, to use it with AutoMapper use : stackoverflow.com/a/1630696/260556

[pfeds]

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.

[Oswin]

Here is a version of the extension for CompositeFilterDescriptor usage : pastebin.com/RVkuwhSS

[Gabriel GM]

As mentionned in other answers, be careful if you have a CompositeFilterDescriptor

[pfeds]

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.

[Philippe]

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!