Foreign Key Drop Downs on Telerik MVC Grid

By | January 13, 2012

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

    public string LastName { getset; }

    public string FirstName { getset; }

    public string TelephoneNumber { getset; }

    public string FaxNumber { getset; }

    public string MobileNumber { getset; }

    public string EmailAddress { getset; }

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

    public string Value { getset; }
}

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

    public string LastName { getset; }

    public string FirstName { getset; }

    public string TelephoneNumber { getset; }

    public string FaxNumber { getset; }

    public string MobileNumber { getset; }

    public string EmailAddress { getset; }

    public int ContactTypeId { getset; }  
}

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.


10 thoughts on “Foreign Key Drop Downs on Telerik MVC Grid

  1. Fyodor

    Thank you, Raymund, for such a good article.

    Could you please explain me from where “Model.ContactTypes” goes ? I mean in the last view-file where you’re showing foreignkey usage?
    columns.ForeignKey(a => a.ContactTypeId, Model.ContactTypes, “Id”, “Name”);

    I didn’t see it in ViewModel class.

    Reply
    1. rsmacaalay

      Ooop I forgot there should be

      public IList AccountContacts { get; set; }

      on the AccountContactsViewModel class which you need to populate together with the AccountContact

      Reply
      1. Miike

        can you please elaborate on this, I’ve tested what you have done here and I cannot populate the IList with the AccountContacts and get it to add the Foreign key Column. Any chance I could download a working version, It would be much appreciated!!!

        Reply
  2. subhash

    Hi,

    it is very helpfull to me…just little more help.;…

    can you give me full example of this sample…?
    what is the ClientEvents(e => e.OnDataBound(“GridHelper.onDataBound”)
    .OnEdit(“GridHelper.onEdit”)
    .OnSave(“GridHelper.onSave”))

    i mean…what should we write in this event…
    i am new in mvc and jquery both so no idea.

    Thanks
    Subhash

    Reply
    1. rsmacaalay

      Hi subhash, those are just javscript calls so you can do anything you can on those events. i.e.

      var GridHelper = (function () {
       
          return {
       
              onDataBound: function () {
       
                  alert('Data Bound Invoked');
       
              },
       
              onEdit: function (control) {
       
                  alert('On Edit Invoked');
              },
       
              onSave: function () {
       
                  alert('On Save Invoked');
              }
          };
       
      })();

      which is saved as a .js file and called in Layout view like such

      <script src="@Url.Content("~/{YourScriptDirectory}/{YourScriptFileName}.js")" type="text/javascript"></script>
      
      Reply
  3. Rajesh Tibrewala

    I actually liked this feature. However, i am struggling trying to figure how to bind to the change event for the drop down list (i need to disable/enable certain columns in the grid depending upon the value selected by the user in the combo box). I am using ajax bind with inline editing.

    Reply
  4. Bhagyashree

    Hi,

    I have added a drop down ReasonsTypes in Telerik grid using columns.ForeignKey(),I have applied required attribute for the field int? ReasonTypeId in my view model, In editor template I am using following template
    @(Html.Telerik().DropDownList()
    .Name(ViewData.TemplateInfo.GetFullHtmlFieldName(“”))
    .BindTo((SelectList)ViewData[ViewData.TemplateInfo.GetFullHtmlFieldName(“”) + “_Data”])
    .SelectedIndex(0)

    The issue is the validation message is not set for the field even if I have applied Required attribute for the field.

    Reply
    1. amy

      Hi Raymound,
      I was struggling to get the grid bind to different dropdownlist, then i found your article, it is a great article, so i tried to use columns.ForeignKey(a => a.ContactTypeId, Model.ContactTypes, “Id”, “Name”); the problem is Model always null
      Do you have any idea why? Thanks,
      Amy

      Reply
      1. Abhishek Umar

        i am also struggling with same issue, have you got solution?

        Reply

Leave a Reply