Foreign Key Drop Downs on Telerik MVC Grid

If you are using MVC on your projects and you are using a Grid View control most probably it will be the Telerik Grid.  While it is one of the best ones around it still have its cons but whats good with it is that the product is regularly updated even for the open source license.  One thing I noticed with the older version before 2011.3.1115.0 is that foreign keys are not natively supported, which means if you have a data structure similar to below then it will be task to perform operations on those in the grid as you will go by the template method.

Logically you will put a drop down on the grid so when you edit or add an item then you will be presented with the contact type options.  Well with that old version it would not be straightforward as you have to make your own template to display that drop down.  For those who are interested well here is how made it.

First is to create your view model, for this sample lets call it AccountContactViewModel

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

    public string LastName { get; set; }

    public string FirstName { get; set; }

    public string TelephoneNumber { get; set; }

    public string FaxNumber { get; set; }

    public string MobileNumber { get; set; }

    public string EmailAddress { get; set; }

    private NumericKeyValuePair contactType;

    [UIHint("AccountContactType"), Required]
    public NumericKeyValuePair ContactType
    {
        get
        {
            if (this.contactType == null)
            {
                NumericKeyValuePair o = new NumericKeyValuePair();
                o.Key = 0;
                o.Value = string.Empty;

                return o;
            }

            return this.contactType;
        }

        set
        {
            this.contactType = value;
        }
    }
}

If you noticed I used a NumericKeyValuePair which is just a class to define Key and value parings to be used for dropdowns

public class NumericKeyValuePair
{
    public int Key { get; set; }

    public string Value { get; set; }
}

Also you will notice the UIHint property, that’s the property to set the name of the field template to use to display the data field which is used for rendering data fields in a data model.  In simple words this is what you need to name your Editor Template to show your drop down so it automatically maps the data field in the model and render it in the control.

Next is you need an Editor template to show that drop down values and like I said above we will name it AccountContactType

The code behind is easy as it is just using a DropDownListFor and it is feeding data from a ContactType Model, you can also manually add items like such

@Html.DropDownList(ViewData.TemplateInfo.GetFullHtmlFieldName(string.Empty), new List<SelectListItem>
{
    new SelectListItem{ Value = "0", Text = string.Empty },
    new SelectListItem{ Value="1", Text = "Primary Debtor" }, 
    new SelectListItem{ Value="2", Text = "Other Debtor" },
    new SelectListItem{ Value="3", Text = "Primary Internal" },  
    new SelectListItem{ Value="4", Text = "Other Internal" }
})

Then on your View where your MVC Grid is, one column should be defined as a Client Template which calls the Client Template above and assigns the ContactTypeId to be linked to the ContactTypes, this is important for the grid to know which item to choose when the row is bound.

@(Html.Telerik().Grid<AccountContactViewModel>()
.Name("grdAccountContact")
.Columns(columns =>
{
    columns.Command(a =>
    {
        a.Delete().ButtonType(GridButtonType.Image);
    }).Width(80);
    columns.Bound(a => a.FirstName);
    columns.Bound(a => a.LastName);
    columns.Bound(a => a.TelephoneNumber);
    columns.Bound(a => a.MobileNumber);
    columns.Bound(a => a.FaxNumber);
    columns.Bound(a => a.EmailAddress);
    columns.Bound(a => a.ContactType).ClientTemplate("<#= ContactType.Value #>").Title("Contact Type");
    columns.Bound(a => a.Id).Hidden();
})
.ToolBar(commands => commands.Insert())
.DataBinding(d => d.Ajax()
    .OperationMode(GridOperationMode.Server)
    .Select("SelectAccountContacts", "Setup", new { accountId = Model.AccountId })
    .Update("UpdateAccountContacts", "Setup", new { accountId = Model.AccountId })
    .Delete("DeleteAccountContacts", "Setup", new { accountId = Model.AccountId })
.Editable(e => e.Mode(GridEditMode.InCell))
.DataKeys(k => k.Add(a => a.Id))
.ToolBar(b => b.SubmitChanges())
.ClientEvents(e => e
    .OnDataBound("GridHelper.onDataBound")
    .OnEdit("GridHelper.onEdit")
    .OnSave("GridHelper.onSave"))
)

Now for your Controller you will have something simple as this

[GridAction]
public ActionResult SelectAccountContacts(int? accountId)
{
    var viewModel = accountSetupQuery.GetAccountContacts(accountId.Value);

    return View(new GridModel<AccountContactViewModel>(viewModel));
}

which calls this query

public IList<AccountContactViewModel> GetAccountContacts(int accountId)
{
    var accountContacts = 
    (from a in Session.Query<AccountContact>()
    where a.Account.Id == accountId
    select new AccountContactViewModel
    {
        Id = a.Account.Id,
        LastName = a.LastName,
        FirstName = a.FirstName,
        TelephoneNumber = a.TelephoneNumber,
        FaxNumber = a.FaxNumber,
        MobileNumber = a.MobileNumber,
        EmailAddress = a.EmailAddress,
        ContactType = new NumericKeyValuePair() { Key = a.ContactType.Id, Value = a.ContactType.Name }
    }).ToList();

    return accountContacts;
}

Take note of that Session.Query<AccountContact> as I am using S#arp Architecture, my LINQ is executing its query to a nHibernateQuery Session of the Account Contact Entity defined in my domain, you can use any queryable collection here.

Now thanks to the new version you don’t have to use the Client Template and all you have to do is to define the column as a Foreign Key.  Here is how it should be done now for those interested.  (BTW the codes above can still be used as a reference for using Client Template)

First is we modify our view model and we remove all instances of the NumericKeyValuePair and replace it with the Foreign Key Id and we will name it ContactTypeId.

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

    public string LastName { get; set; }

    public string FirstName { get; set; }

    public string TelephoneNumber { get; set; }

    public string FaxNumber { get; set; }

    public string MobileNumber { get; set; }

    public string EmailAddress { get; set; }

    public int ContactTypeId { get; set; }  
}

Next is we get rid of the client template then your Grid should be defined like this

@(Html.Telerik().Grid<AccountContactViewModel>()
.Name("grdAccountContact")
.Columns(columns =>
{
    columns.Command(a =>
    {
        a.Delete().ButtonType(GridButtonType.Image);
    }).Width(80);
    columns.Bound(a => a.FirstName);
    columns.Bound(a => a.LastName);
    columns.Bound(a => a.TelephoneNumber);
    columns.Bound(a => a.MobileNumber);
    columns.Bound(a => a.FaxNumber);
    columns.Bound(a => a.EmailAddress);
    columns.ForeignKey(a => a.ContactTypeId, Model.ContactTypes, "Id", "Name");
    columns.Bound(a => a.Id).Hidden();
})
.ToolBar(commands => commands.Insert())
.DataBinding(d => d.Ajax()
    .OperationMode(GridOperationMode.Server)
    .Select("SelectAccountContacts", "Setup", new { accountId = Model.AccountId })
    .Update("UpdateAccountContacts", "Setup", new { accountId = Model.AccountId }))
.Editable(e => e.Mode(GridEditMode.InCell))
.DataKeys(k => k.Add(a => a.Id))
.ToolBar(b => b.SubmitChanges())
.ClientEvents(e => e.OnDataBound("GridHelper.onDataBound")
    .OnEdit("GridHelper.onEdit")
    .OnSave("GridHelper.onSave"))
)

You notice that the old ClientTemplate is now replaced to ForeignKey and it is populated by from the Model.ContactTypes so make sure before the MVC Grid is populated the Model.ContactTypes already have a value.

Now you can still use the same controller but you will need to change your query to something like this

public IList<AccountContactViewModel> GetAccountContacts(int accountId)
{
    var accountContacts = (from a in Session.Query<AccountContact>()
    where a.Account.Id == accountId
    select new AccountContactViewModel
    {
        Id = a.Account.Id,
        LastName = a.LastName,
        FirstName = a.FirstName,
        TelephoneNumber = a.TelephoneNumber,
        FaxNumber = a.FaxNumber,
        MobileNumber = a.MobileNumber,
        EmailAddress = a.EmailAddress,
        ContactTypeId = a.ContactType.Id
    }).ToList();

    return accountContacts;
}

You will notice that you don’t have that NumericKeyValuePair and it was replaced with the Foreign Key Id in your AccountContactViewModel.

That’s it, its simpler than before, now you can put a of foreign keys on that table without creating lots of Client Template.