How to Load Relational Data into a Telerik UI for ASP.NET Core Grid with Microsoft SQL Server

In this post, we’ll review the intricacies of integrating relational data from an SQL Server database into a Telerik UI for ASP.NET Core Grid component.

Progress Telerik UI for ASP.NET Core provides rich tools for creating sophisticated grid views. I will show you how to quickly combine these tools with server-side data in ASP.NET Core, mainly when dealing with relational databases such as SQL Server.

We are adapting and using Progress Telerik’s official ASP.NET Core repository: ui-for-aspnet-core-examples.

We fetch and bind relational data from SQL Server to populate a Grid.

@(Html.Kendo().Grid<ForeignKeyOrderViewModel>()
  .Name("grid")
  .Columns(columns =>
  {
    columns.Bound(p => p.OrderID).Filterable(false).Width(110);
    columns.Bound(p => p.Freight).Width(100);
    columns.Bound(p => p.OrderDate).Format("{0:MM/dd/yyyy}").Width(220);
    columns.Bound(p => p.ShipName).Width(150);

    columns.ForeignKey(p => p.TaskID, ds => ds.Read(r => r.Action("List", "Tasks")), "TaskID", "Title")
      .Title("To do").Width(200);

    columns.ForeignKey(p => p.ShipCityId, 
      (System.Collections.IEnumerable)ViewData["cities"], "CityID", "CityName")
      .Title("City").Width(400);
    columns.Command(c => c.Edit());
})

We’ll start using the source available in Telerik’s official GitHub repository for ASP.NET Core examples. We will adapt the examples to our ASP.NET Core application and show how to link SQL Server data to a Telerik UI Grid with a Foreign Key.

This image shows our end goal. The “To do”* column is a foreign key from SQL Server.

*This demonstrates that the relation, order and tasks are just samples.

Let’s Get Started!

1. Start cloning the repository https://github.com/telerik/ui-for-aspnet-core-examples.
2. Inside Controllers\Grid create a cs named TasksController.cs:
   
3. Add this code to TasksController.cs:

using Kendo.Mvc.Extensions;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Linq;
using Telerik.Examples.Mvc.Models;

namespace Telerik.Examples.Mvc.Controllers.Grid;

public class TasksController : Controller
{
  private readonly GeneralDbContext _context; 

  public TasksController(GeneralDbContext context)
  {
    _context = context;
  }
  public ActionResult List()
  {
    IEnumerable<Telerik.Examples.Mvc.Models.Task> taks;

    taks = _context.Tasks
      .Select(c => new Telerik.Examples.Mvc.Models.Task
      {
        TaskID = c.TaskID,
        Title = c.Title
      })
      .OrderBy(e => e.Title).ToList();

    return Json(taks);
  }
}

4. Add to the Model ForeignKeyOrderViewModel.cs the field TaskID:

Source code:

public int TaskID
{
  get;
  set;
}

5. Add the column to the grid:

Source code:

columns.ForeignKey(p => p.TaskID, ds => ds.Read(r => r.Action("List", "Tasks")), "TaskID", "Title")
  .Title("To do").Width(200);

6. Add Telerik to your NuGet.config:

<add key="Telerik" value="https://nuget.telerik.com/v3/index.json" />

7. You must install your Telerik trial from https://www.telerik.com/try/devcraft-ultimate and replace the Telerik.UI.for.AspNET.Core.Trial reference by Telerik.UI.for.AspNet.Core:

8. It’s ready to run at https://localhost:44316/EncodedForeignKeyValues/EncodedForeignKeyValues.

How Does the Magic Work?

Let’s analyze the Program.cs. The database context is initialized. It’s necessary to start the DbContext in the database.

builder.Services.AddDbContext<GeneralDbContext>(options =>
   options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

DbContext bridges your domain or entity classes and the C# ASP.NET Core database. It manages database connections, object retrieval, change tracking and query translation to SQL as part of the Entity Framework Core (EF Core) package. It allows you to interface with the database through LINQ queries or direct SQL.

The DefaultConnection is stored in the appsettings.json solution. “DefaultConnection” is the name of the connection string and should be added as code for reference.

The GeneralDbContext binds the tables from the SQL Server database:

public class GeneralDbContext : IdentityDbContext
{
  public GeneralDbContext(DbContextOptions<GeneralDbContext> options)
    : base(options)
  { }

  public DbSet<EditorData> EditorData { get; set; }
  public DbSet<Task> Tasks { get; set; }
}

The DbSet represents each table from the SQL Server database:

How Does the Column in Telerik UI Work?

columns.ForeignKey(p => p.TaskID, ds => ds.Read(r => r.Action("List", "Tasks")), "TaskID", "Title")
  .Title("To do").Width(200);

In this specific case, the method ForeignKey has four parameters:

ForeignKey (method)

Expression<Func<TModel, TValue>> expression – we select the field that has a foreign key: p.TaskID;
 
Action<ReadOnlyDataSourceBuilder> dataSource – we add the data source using the API List from the Tasks controller: ds => ds.Read(r => 
  r.Action("List", "Tasks"));

string dataFieldValue – we identify the key field of the foreign key: TaskID;

string dataFieldText – we identify the description for the column, in this case, “Title.”

.Title() – Defines the caption for the column and .Width() the size of the column.

Extras

The API List for Tasks, returns an IEnumerable reading from the _context.Tasks reference:

public ActionResult List()
{
  IEnumerable<Telerik.Examples.Mvc.Models.Task> taks;

  taks = _context.Tasks
    .Select(c => new Telerik.Examples.Mvc.Models.Task
    {
      TaskID = c.TaskID,
      Title = c.Title
    })
    .OrderBy(e => e.Title).ToList();

  return Json(taks);
}

The _context is automatically initialized with the Controller:

private readonly GeneralDbContext _context; 

public TasksController(GeneralDbContext context)
{
  _context = context;
}

For this sample, I customized the cities:

private void PopulateCities()
{
  var cities = new CityViewModel[] { new() { CityID = 1, CityName = "Washington, D.C." }, new() { CityID = 2, CityName = "London" }, new() { CityID = 3, CityName = "Berlin" }, new() { CityID = 4, CityName = "Tokyo" }, new() { CityID = 5, CityName = "Beijing" }, new() { CityID = 6, CityName = "Canberra" }, new() { CityID = 7, CityName = "Ottawa" }, new() { CityID = 8, CityName = "Paris" }, new() { CityID = 9, CityName = "Rome" }, new() { CityID = 10, CityName = "Madrid" }, new() { CityID = 11, CityName = "Moscow" }, new() { CityID = 12, CityName = "New Delhi" }, new() { CityID = 13, CityName = "Brasilia" }, new() { CityID = 14, CityName = "Cairo" }, new() { CityID = 15, CityName = "Buenos Aires" }, new() { CityID = 16, CityName = "Seoul" }, new() { CityID = 17, CityName = "Cape Town" }, new() { CityID = 18, CityName = "Helsinki" }, new() { CityID = 19, CityName = "Oslo" }, new() { CityID = 20, CityName = "Stockholm" } };

  ViewData["cities"] = cities;
}

Conclusion

Adding a foreign key in the Grid component of Telerik UI for ASP.NET Core is easier than it appears. You may create a solid and efficient grid by carefully organizing the items. Hopefully, this post has cleared up any confusion and provided you with an excellent framework to build from. Feel free to continue experimenting to get the most out of your Telerik UI Grid implementation.

Download a trial today and start using Telerik UI immediately. Rest assured, you can reach out to the legendary support team for assistance at any time, even during the trial period.

References

GitHub – https://github.com/telerik/ui-for-aspnet-core-examples
Foreign Key Doc – https://docs.telerik.com/aspnet-core/html-helpers/data-management/grid/columns/foreignkey-column
Demo – https://demos.telerik.com/aspnet-core/grid/foreignkeycolumn