Telerik MVC Grid - Group by Date

Question

I'm trying to use Telerik MVC Grid for an application which requires lots of filtering and grouping... On every model I have, it has a DateTime property for storing CreationDate. Sometimes when showing the grid to the user, time isn't important. Also, I had to use ViewModels to avoid circular references since I'm using LINQ.

The problem comes when resorting or grouping results by date. If I use the CreationDate field as a DateTime on my ViewModel and then give it a Format on the View to show only the date, it sorts fine, works fine, but when grouping it groups using the whole datetime value, so there won't ever be anything grouped by. If I use the CreationDate as a string in the ViewModel, it shows fine the first time but will give an error if re-sorting or grouping by date.

Here's the code I have for this case:

ViewModel:

public class CenterViewModel
{
    public int Id { get; set; }
    public string Name{ get; set; }
    public string CityName { get; set; }
    public string Phone{ get; set; }
    public string CreationDate { get; set; }
    public bool Active { get; set; }
}

Controller:

[GridAction]
public ActionResult AjaxIndex()
{
    var model = repository.GetAllRecords()
        .Select(o => new CenterViewModel
        {
            Id = o.Id,
            Name = o.Name,
            CityName = o.City.Name,
            Phone = o.Phone,
            CreationDate = o.CreationDate.ToShortDateString(),
            Active = o.Active
        });

    return View(new GridModel
    {
        Data = model
    });
}

public ActionResult Index()
{
    return View();
}

View:

@model IEnumerable<CenterViewModel>

@(Html.Telerik().Grid<CenterViewModel>()
    .Name("Grid")
    .DataKeys(keys =>
    {
        keys.Add(p => p.Id);
    })
    .Columns(columns =>
    {
        columns.Bound(o => o.Name);
        columns.Bound(o => o.CityName);
        columns.Bound(o => o.Phone);            
        columns.Bound(o => o.CreationDate).Width(200);
        columns.Bound(o => o.Active).Width(100)
    })
    .DataBinding(dataBinding =>
    {
        dataBinding.Ajax().Select("AjaxIndex", "Centers", null);
    })
    .Pageable()
    .Sortable()
    .Groupable()
    .Filterable()
)

The above code would work only for the first load of data, when you re-sort or group by date it will throw the following exception: "Method 'System.String ToShortDateString()' has no supported translation to SQL." which makes sense, but, I think my intention is pretty clear now.

Does anyone know how to solve this issue? Thanks in advance,

Answer 1

One thing you could try is in your model to use a date that removes the time element. Then in the view, format the date.

ViewModel:

public DateTime CreationDate { get; set; }

Controller:

var model = repository.GetAllRecords()
    .Select(o => new CenterViewModel
    {
        Id = o.Id,
        Name = o.Name,
        CityName = o.City.Name,
        Phone = o.Phone,
        CreationDate = new DateTime(o.CreationDate.Year, o.CreationDate.Month, o.CreationDate.Day),
        Active = o.Active
    });

View:

columns.Bound(o => o.CreationDate).Width(200).Format("{0:MM/dd/yyyy}");