Foreign Key Column in ASP.NET CORE Grid Component
21 Dec 202224 minutes to read
Foreign key column can be enabled by using dataSource, foreignKeyField and foreignKeyValue properties of e-grid-column tag helper.
- dataSource - Defines the foreign data.
- foreignKeyField - Defines the mapping column name to the foreign data.
- foreignKeyValue - Defines the display field from the foreign data.
In the following example, Employee Name is a foreign column which shows FirstName column from foreign data.
<ejs-grid id="Grid" dataSource="@ViewBag.datasource" >
<e-grid-columns>
<e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" textAlign="Right" width="120"></e-grid-column>
<e-grid-column field="EmployeeID" headerText="Empolyee Name" foreignKeyValue="FirstName" dataSource="ViewBag.foreign" width="150"></e-grid-column>
<e-grid-column field="Freight" headerText="Freight" textAlign="Right" format="C2" width="120"></e-grid-column>
<e-grid-column field="ShipName" headerText="Ship Name" width="150"></e-grid-column>
</e-grid-columns>
</ejs-grid>using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace EJ2CoreSampleBrowser.Models
{
public class EmployeeView
{
public EmployeeView()
{
}
public EmployeeView(int EmployeeID, string FirstName, string LastName, string Title, DateTime BirthDate, DateTime HireDate, int ReportsTo, string Address, string PostalCode, string Phone, string City, string Country)
{
this.EmployeeID = EmployeeID;
this.FirstName = FirstName;
this.LastName = LastName;
this.Title = Title;
this.BirthDate = BirthDate;
this.HireDate = HireDate;
this.ReportsTo = ReportsTo;
this.Address = Address;
this.PostalCode = PostalCode;
this.Phone = Phone;
this.City = City;
this.Country = Country;
}
public int EmployeeID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Title { get; set; }
public DateTime BirthDate { get; set; }
public DateTime HireDate { get; set; }
public int ReportsTo { get; set; }
public string Address { get; set; }
public string PostalCode { get; set; }
public string Phone { get; set; }
public string City { get; set; }
public string Country { get; set; }
public static List<EmployeeView> GetAllRecords()
{
List<EmployeeView> Emp = new List<Models.EmployeeView>();
Emp.Add(new EmployeeView(1, "Nancy", "Davolio", "Sales Representative", new DateTime(1948, 12, 08), new DateTime(1992, 05, 01), 2, "507 - 20th Ave. E.Apt. 2A ", " 98122", "(206) 555-9857 ", "Seattle ", "USA"));
Emp.Add(new EmployeeView(2, "Andrew", "Fuller", "Vice President, Sales", new DateTime(1952, 02, 19), new DateTime(1992, 08, 14), 4, "908 W. Capital Way", "98401 ", "(206) 555-9482 ", "Kirkland ", "USA"));
Emp.Add(new EmployeeView(3, "Janet", "Leverling", "Sales Representative", new DateTime(1963, 08, 30), new DateTime(1992, 04, 01), 3, " 4110 Old Redmond Rd.", "98052 ", "(206) 555-8122", "Redmond ", "USA "));
Emp.Add(new EmployeeView(4, "Margaret", "Peacock", "Sales Representative", new DateTime(1937, 09, 19), new DateTime(1993, 05, 03), 6, "14 Garrett Hill ", "SW1 8JR ", "(71) 555-4848 ", "London ", "UK "));
Emp.Add(new EmployeeView(5, "Steven", "Buchanan", "Sales Manager", new DateTime(1955, 03, 04), new DateTime(1993, 10, 17), 8, "Coventry HouseMiner Rd. ", "EC2 7JR ", " (206) 555-8122", "Tacoma ", " USA"));
Emp.Add(new EmployeeView(6, "Michael", "Suyama", "Sales Representative", new DateTime(1963, 07, 02), new DateTime(1993, 10, 17), 2, " 7 Houndstooth Rd.", " WG2 7LT", "(71) 555-4444 ", "London ", "UK "));
Emp.Add(new EmployeeView(7, "Robert", "King", "Sales Representative", new DateTime(1960, 05, 29), new DateTime(1994, 01, 02), 7, "Edgeham HollowWinchester Way ", "RG1 9SP ", "(71) 555-5598 ", "London ", " UK"));
Emp.Add(new EmployeeView(8, "Laura", "Callahan", "Inside Sales Coordinator", new DateTime(1958, 01, 09), new DateTime(1994, 03, 05), 9, "722 Moss Bay Blvd. ", "98033 ", " (206) 555-3412", "Seattle ", "USA "));
Emp.Add(new EmployeeView(9, "Anne", "Dodsworth", "Sales Representative", new DateTime(1966, 01, 27), new DateTime(1994, 11, 15), 5, "4726 - 11th Ave. N.E. ", "98105 ", "(71) 555-5598 ", " London", "UK "));
return Emp;
}
}
}NOTE
- For remote data, the sorting and grouping is done based on foreignKeyField instead of foreignKeyValue.
* If foreignKeyField is not defined, then the column uses field property of e-grid-column tag helper.
Use edit template in foreignkey column
By default, foreign key column uses dropdown component for editing. You can render other than the dropdown by using the edit property of e-grid-column tag helper. The following example demonstrates the way of using edit template in foreign column.
In the following example, The Employee Name is a foreign key column and while editing, AutoComplete component is rendered instead of DropDownList.
<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Cancel", "Update" })">
<e-grid-editSettings allowAdding="true" allowDeleting="true" allowEditing="true" mode="Normal"></e-grid-editSettings>
<e-grid-columns>
<e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" textAlign="Right" width="100"></e-grid-column>
<e-grid-column field="EmployeeID" headerText="Customer ID" type="string" width="120" foreignKeyValue='LastName' dataSource="ViewBag.ForeignData" edit="@(new {create="create", read="read", destroy="destroy", write="write"})"></e-grid-column>
<e-grid-column field="Freight" headerText="Freight" textAlign="Right" format="C2" editType="numericedit" width="120"></e-grid-column>
<e-grid-column field="ShipCountry" headerText="Ship Country" width="150" editType='dropdownedit'></e-grid-column>
</e-grid-columns>
</ejs-grid>
<script>
var autoComplete;
var employeeData = @Json.Serialize(ViewBag.ForeignData);
function create () { // to create input element
return document.createElement('input');
}
function read () { // return edited value to update data source
let value = new ej.data.DataManager(employeeData).executeLocal(new ej.data.Query().where('LastName', 'equal', autoComplete.value));
return value.length && value[0]['EmployeeID']; // to convert foreign key value to local value.
}
function destroy () { // to destroy the custom component.
autoComplete.destroy();
}
function write (args) { // to show the value for custom component
autoComplete = new ej.dropdowns.AutoComplete({
dataSource: employeeData,
fields: { value: args.column.foreignKeyValue },
value: args.foreignKeyData[0][args.column.foreignKeyValue]
});
autoComplete.appendTo(args.element);
}
</script>public IActionResult Index()
{
ViewBag.DataSource = OrderDetails.GetAllRecords();
ViewBag.ForeignData = EmployeeDetails.GetAllRecords();
return View();
}Customize filter UI in foreignkey column
You can create your own filtering UI by using filter property of e-grid-column tag helper. The following example demonstrates the way to create a custom filtering UI in the foreign column.
In the following example, The Employee Name is a foreign key column. DropDownList is rendered using Filter UI.
<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" allowFiltering="true">
<e-grid-filterSettings type="Menu"></e-grid-filterSettings>
<e-grid-columns>
<e-grid-column field="OrderID" headerText="Order ID" textAlign="Right" width="120"></e-grid-column>
<e-grid-column field="EmployeeID" headerText="EmployeeID" width="150" foreignKeyValue="LastName" dataSource="ViewBag.ForeignData"
filter="@(new { ui = new { create = "create", read = "read", write = "write"} })"></e-grid-column>
<e-grid-column field="ShipCity" headerText="Ship City" width="150"></e-grid-column>
<e-grid-column field="ShipName" headerText="Ship Name" width="150"></e-grid-column>
</e-grid-columns>
</ejs-grid>
<script>
var dropInstance;
var fEmployeeData = @Json.Serialize(ViewBag.ForeignData);
function create (args) {
var flValInput = document.createElement('input');
flValInput.classList.add('flm-input');
args.target.appendChild(flValInput);
dropInstance = new ej.dropdowns.DropDownList({
dataSource: new ej.data.DataManager(fEmployeeData),
fields: { text: 'LastName', value: 'EmployeeID' },
placeholder: 'Select a value',
popupHeight: '200px'
});
dropInstance.appendTo(flValInput);
}
function write (args){
dropInstance.text = args.filteredValue || '';
}
function read (args) {
args.fltrObj.filterByColumn(args.column.field, args.operator, dropInstance.text);
}
</script>public IActionResult Index()
{
ViewBag.DataSource = OrderDetails.GetAllRecords();
ViewBag.ForeignData = EmployeeDetails.GetAllRecords();
return View();
}Use filter bar template in foreignkey column
You can use the filter bar template in foreign key column by defining the filterBarTemplate property of e-grid-column tag helper. The following example demonstrates the way to use filter bar template in foreign column.
In the following example, The Employee Name is a foreign key column. This column header shows the custom filter bar template and you can select filter value by using the `DropDown options.
@{
Object filterTemplate = new Object();
filterTemplate = (new { read = "read", write = "write" });
}
<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" allowFiltering="true">
<e-grid-columns>
<e-grid-column field="OrderID" headerText="Order ID" textAlign="Right" width="120"></e-grid-column>
<e-grid-column field="EmployeeID" headerText="Employee Name" foreignKeyValue='FirstName' dataSource="ViewBag.ForeignData" width="150" filterBarTemplate="filterTemplate"></e-grid-column>
<e-grid-column field="ShipCity" headerText="Ship City" width="150"></e-grid-column>
<e-grid-column field="ShipName" headerText="Ship Name" width="150"></e-grid-column>
</e-grid-columns>
</ejs-grid>
<script>
function create (args) {
return ej.base.createElement('input', { className: 'flm-input' });
}
function write (args) {
var fEmployeeData = @Json.Serialize(ViewBag.ForeignData);
fEmployeeData.splice(0, 0, { 'FirstName': 'All' }); // for clear filtering
var dropInstance = new ej.dropdowns.DropDownList({
dataSource: new ej.data.DataManager(fEmployeeData),
fields: { text: 'FirstName' },
placeholder: 'Select a value',
popupHeight: '200px',
index: 0,
change: (args) => {
var grid = document.getElementById("Grid").ej2_instances[0];
if (args.value !== 'All') {
grid.filterByColumn('EmployeeID', 'equal', args.value);
}
else {
grid.clearFiltering(['EmployeeID']);
}
}
});
dropInstance.appendTo(args.element);
}
</script>public IActionResult Index()
{
ViewBag.DataSource = OrderDetails.GetAllRecords();
ViewBag.ForeignData = EmployeeDetails.GetAllRecords();
return View();
}Perform aggregation in foreignkey column
Default aggregations are not supported in a foreign key column. You can achieve aggregation for the foreign key column by using the custom aggregates. The following example demonstrates the way to achieve aggregation in foreign key column.
In the following example, The Employee Name is a foreign key column and the aggregation for the foreign column was calculated in customAggregateFunction.
<ejs-grid id="Grid" dataSource="@ViewBag.DataSource">
<e-grid-columns>
<e-grid-column field="OrderID" headerText="Order ID" textAlign="Right" width="120"></e-grid-column>
<e-grid-column field="EmployeeID" headerText="Customer ID" type="string" width="120" foreignKeyValue='FirstName' dataSource="ViewBag.ForeignData"></e-grid-column>
<e-grid-column field="ShipCity" headerText="Ship City" width="150"></e-grid-column>
<e-grid-column field="ShipName" headerText="Ship Name" width="150"></e-grid-column>
</e-grid-columns>
<e-grid-aggregates>
<e-grid-aggregate>
<e-aggregate-columns>
<e-aggregate-column field="EmployeeID" type="Custom" customAggregate="@("customAggregateFunction")" footerTemplate="Count of Margaret:${Custom}"></e-aggregate-column>
</e-aggregate-columns>
</e-grid-aggregate>
</e-grid-aggregates>
</ejs-grid>
<script>
function customAggregateFunction(data, column) {
var grid = document.getElementById("Grid").ej2_instances[0];
return data.result.filter(function (dObj) {
return ej.base.getValue('FirstName', ej.grids.getForeignData(grid.getColumnByField(column.columnName), dObj)[0]) === 'Margaret';
}).length;
};
</script>public IActionResult Index()
{
ViewBag.DataSource = OrderDetails.GetAllRecords();
ViewBag.ForeignData = EmployeeDetails.GetAllRecords();
return View();
}Bind data on foreign key columns with UrlAdaptor
You can bind the data on foreign key columns using UrlAdaptor. In the following example, EmployeeID is designated as a ForeignKeyField, and ShipName is designated as a ForeignKeyValue.
We use UrlAdaptor concept to communicate with particular data source. UrlAdaptor is used to interact with remote data services and to bind data on foreign key columns.
@{
var datas = new Syncfusion.EJ2.DataManager
{
Url = Url.Action("ForeignKey", "Home"),
Adaptor = "UrlAdaptor"
};
}
<ejs-grid id="HierarchyPrint" allowPaging="true" allowFiltering="true" showColumnChooser="false"
toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Cancel", "Update" })" actionBegin="actionBegin">
<e-grid-pagesettings></e-grid-pagesettings>
<e-grid-filterSettings type="Excel"></e-grid-filterSettings>
<e-grid-editSettings allowAdding="true" allowDeleting="true" allowEditing="true" mode="Normal"></e-grid-editSettings>
<e-data-manager url="/Home/UrlDatasource" insertUrl="/Home/Insert" updateUrl="/Home/Update" removeUrl="/Home/Remove" adaptor="UrlAdaptor"></e-data-manager>
<e-grid-columns>
<e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" textAlign="Center" width="125"></e-grid-column>
<e-grid-column field="EmployeeID" headerText="Employee ID" foreignKeyField="EmployeeID" textAlign="Center" foreignKeyValue="ShipName" dataSource=datas editType="dropdownedit" width="120"></e-grid-column>
<e-grid-column field="Freight" headerText="Freight" textAlign="Center" width="125"></e-grid-column>
<e-grid-column field="OrderDate" headerText="OrderDate" textAlign="Center" width="120" format="yMd"></e-grid-column>
<e-grid-column field="CustomerID" headerText="CustomerID" textAlign="Center" width="125"></e-grid-column>
<e-grid-column field="ShipName" headerText="ShipName" textAlign="Center" width="125"></e-grid-column>
</e-grid-columns>
</ejs-grid>
<script>
function actionBegin(args) {
if (args.requestType == 'add') {
args.rowData[this.getForeignKeyColumns()[0].field] = '';
}
}
</script>namespace WebApplication7.Controllers
{
public class HomeController : Controller
{
public ActionResult Index()
{
ViewBag.DataSource = BigData.GetAllRecords();
return View();
}
public ActionResult ForeignKey([FromBody] DataManagerRequest dm)
{
IEnumerable DataSource = OrdersData.GetAllRecords();
return dm.RequiresCounts ? Json(new { result = DataSource }) : Json(DataSource);
}
public IActionResult UrlDatasource([FromBody]DataManagerRequest dm)
{
IEnumerable DataSource = OrdersDetails.GetAllRecords();
DataOperations operation = new DataOperations();
int count = DataSource.Cast<OrdersDetails>().Count();
if (dm.Skip != 0)
{
DataSource = operation.PerformSkip(DataSource, dm.Skip);
}
if (dm.Take != 0)
{
DataSource = operation.PerformTake(DataSource, dm.Take);
}
return dm.RequiresCounts ? Json(new { result = DataSource, count = count }) : Json(DataSource);
}
public IActionResult Update([FromBody]CRUDModel<OrdersDetails> value)
{
var data = OrdersDetails.GetAllRecords().Where(or => or.OrderID == value.Value.OrderID).FirstOrDefault();
if (data != null)
{
data.OrderID = value.Value.OrderID;
data.EmployeeID = value.Value.EmployeeID;
data.CustomerID = value.Value.CustomerID;
data.Freight = value.Value.Freight;
data.OrderDate = value.Value.OrderDate;
data.ShipName = value.Value.ShipName;
}
return Json( new { value.Value});
}
public IActionResult Insert([FromBody]CRUDModel<OrdersDetails> value)
{
OrdersDetails.GetAllRecords().Insert(0, value.Value);
return Json(new { value.Value});
}
public IActionResult Remove([FromBody]CRUDModel<OrdersDetails> value)
{
var data = OrdersDetails.GetAllRecords().Where(or => or.OrderID.Equals(int.Parse(value.Key.ToString()))).FirstOrDefault();
OrdersDetails.GetAllRecords().Remove(data);
return Json(new {data});
}
}
}Enable multiple foreign key columns
Multiple foreign key columns with editing options are enabled for the ASP.NET Core Grid component.
In the following example, Customer Name and Ship City are foreign key columns that display the ContactName and City columns from foreign data.
<ejs-grid id="Grid" dataSource="ViewBag.DataSource" allowPaging="true" toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Update", "Cancel" })">
<e-grid-editSettings allowAdding="true" allowDeleting="true" allowEditing="true"></e-grid-editSettings>
<e-grid-columns>
<e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" textAlign="Right" width="100"></e-grid-column>
<e-grid-column field="CustomerID" headerText="Customer Name" foreignKeyValue="ContactName" dataSource="ViewBag.CustomerData" validationRules="@(new { required=true})" width="150"></e-grid-column>
<e-grid-column field="Freight" headerText="Freight" format="C2" editType="numericedit" textAlign="Right" width="100"></e-grid-column>
<e-grid-column field="EmployeeID" headerText="Ship City" foreignKeyValue="City" dataSource="ViewBag.EmployeeData" validationRules="@(new { required=true})" width="150"></e-grid-column>
<e-grid-column field="ShipName" headerText="Ship Name" editType='dropdownedit' width="180"></e-grid-column>
</e-grid-columns>
</ejs-grid>public IActionResult Index()
{
ViewBag.DataSource = OrderDetails.GetAllRecords();
ViewBag.CustomerData = CustomerDetails.GetAllRecords();
ViewBag.EmployeeData = EmployeeDetails.GetAllRecords();
return View();
}