Category / Section
How to perform Sorting on ForeignKey column while using Remote Data in .NET Core Grid ?
5 mins read
Usually, foreignkey column in the Grid will be sorted based on the foreignKeyField which is the default behavior in the local data or remote data binding. In the local databinding, it can overcome by using the ej.foreignKeyAdaptor of DataManager. in the remote databinding, ej adaptors can handle this behavior in the server-end and sort the foreignkey column based on the foreignKeyValue instead of the foreignKeyField.
Please make a note that this solution will work only for the single column sorting.
In the server-end, for a foreignkey column, a separate method SortFor has been defined which will receive the Grid data, Foreignkey column data and Sort query. Using this, sorting has been done to the Foreignkey data which has been mapped back to the Grid datasource.
<div id="Grid"></div>
<script type="text/javascript">
$(function () {
var data = @Html.Raw(Json.Encode(ViewBag.dataSource));
$("#Grid").ejGrid({
dataSource: new ej.DataManager({
url: "/Home/UrlDataSource",
adaptor: new ej.UrlAdaptor()
}),
allowPaging: true,
allowSorting: true,
columns: [
{ field: "OrderID", headerText: "Order ID", textAlign: ej.TextAlign.Right },
{
field: "EmployeeID", headerText: "Employee Name", dataSource: data,
foreignKeyField: "EmployeeID",
foreignKeyValue: "FirstName",
textAlign: ej.TextAlign.Right
},
{ field: "Freight", format: "{0:C}", textAlign: ej.TextAlign.Right },
{ field: "CustomerID", headerText: "Customer ID" },
{ field: "ShipCity", headerText: "Ship City" }
]
});
});
</script>
Razor:
@using Syncfusion.JavaScript.Models
@(Html.EJ().Grid<OrdersView>("Grid")
.AllowPaging()
.Datasource(ds =>
{
ds.URL("/Home/UrlDataSource");
ds.Adaptor(AdaptorType.UrlAdaptor);
})
.AllowSorting()
.Columns(col =>
{
col.Field("OrderID").HeaderText("Order ID")
.TextAlign(TextAlign.Right).Add();
col.Field("EmployeeID").HeaderText("Employee Name")
.ForeignKeyField("EmployeeID")
.ForeignKeyValue("FirstName")
.DataSource((IEnumerable<object>)ViewBag.dataSource)
.Add();
col.Field("Freight").HeaderText("Freight")
.TextAlign(TextAlign.Right)
.Format("{0:C}").Add();
col.Field("CustomerID").HeaderText("Customer ID").Add();
col.Field("ShipCity").HeaderText("ShipCity").Add();
})
)
public class HomeController : Controller
{
public ActionResult Index()
{
ViewBag.dataSource = new NorthwindDataContext().EmployeeViews.ToList();
return View();
}
public ActionResult UrlDataSource(DataManager dm)
{
IEnumerable GridData = OrderRepository.GetAllRecords();
IEnumerable empData = new NorthwindDataContext().EmployeeViews.ToList();
DataOperations ds = new DataOperations();
var count = GridData.Cast<EditableOrder>().Count();
if (dm.Sorted != null && dm.Sorted.Count > 0)
{
if (dm.Sorted[0].Name == "EmployeeID")
GridData = SortFor((List<EditableOrder>)GridData, (List<EmployeeView>)empData, dm.Sorted);//Only for foreignKey Column
else
GridData = ds.PerformSorting(GridData, dm.Sorted);
}
GridData = ds.PerformSkip(GridData, dm.Skip);
GridData = ds.PerformTake(GridData, dm.Take);
return Json(new { result = GridData, count = count });
}
public List<EditableOrder> SortFor(List<EditableOrder> ord, List<EmployeeView> emp, List<Sort> Sorted)
{
if (Sorted[0].Direction == "ascending")//check whether ascending or descending
emp = emp.OrderBy(e => e.FirstName).ToList();
else
emp = emp.OrderByDescending(e => e.FirstName).ToList();
List<EditableOrder> or = new List<EditableOrder>();
for (int i = 0; i < emp.Count(); i++)
{ //Select the Field matching records
IEnumerable<EditableOrder> list = ord.Where(pred => pred.EmployeeID == emp[i].EmployeeID).ToList();
or.AddRange(list);
}
return or;
}
}
Webforms:
<ej:Grid ID="Grid" runat="server" AllowPaging="true" AllowSorting="true" > <DataManager URL="Default.aspx/UrlDataSource" Adaptor="WebMethodAdaptor" /> <Columns> <ej:Column Field="OrderID" IsPrimaryKey ="true" TextAlign ="Right" HeaderText="Order ID" /> <ej:Column Field="EmployeeID" HeaderText="Employee Name" ForeignKeyField="EmployeeID" ForeignKeyValue ="First Name" TextAlign ="Right" > </ej:Column> <ej:Column Field="Freight" HeaderText="Freight" /> <ej:Column Field="CustomerID" HeaderText="Customer ID" /> <ej:Column Field="ShipCity" HeaderText="ShipCity" /> </Columns> </ej:Grid>
namespace sqlbinding
{
public partial class _Default : Page
{
protected void Page_Load(object sender, EventArgs e)
{
this.Grid.Columns[1].DataSource = new NorthwindDataContext().EmployeeViews.ToList();
Grid.DataBind();
}
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public static object UrlDataSource(DataManager value)
{
IEnumerable GridData = OrderRepository.GetAllRecords();
IEnumerable empData = new NorthwindDataContext().EmployeeViews.ToList();
DataOperations ds = new DataOperations();
var count = GridData.Cast<EditableOrder>().Count();
if (value.Sorted != null && value.Sorted.Count > 0)
{
if (value.Sorted[0].Name == "EmployeeID")
GridData = SortFor((List<EditableOrder>)GridData, (List<EmployeeView>)empData, dm.Sorted);//Only for foreignKey Column
else
GridData = ds.PerformSorting(GridData, dm.Sorted);
}
GridData = ds.PerformSkip(GridData, value.Skip);
GridData = ds.PerformTake(GridData, value.Take);
return new { result = GridData, count = count };
}
public List<EditableOrder> SortFor(List<EditableOrder> ord, List<EmployeeView> emp, List<Sort> Sorted)
{
if (Sorted[0].Direction == "ascending")//check whether ascending or descending
emp = emp.OrderBy(e => e.FirstName).ToList();
else
emp = emp.OrderByDescending(e => e.FirstName).ToList();
List<EditableOrder> or = new List<EditableOrder>();
for (int i = 0; i < emp.Count(); i++)
{ //Select the Field matching records
IEnumerable<EditableOrder> list = ord.Where(pred => pred.EmployeeID == emp[i].EmployeeID).ToList();
or.AddRange(list);
}
return or;
}
}
}
Angular 2 with MVC backend
Index.cshtml
<ej-app linkdata="@ViewBag.dataSource">Loading...</ej-app>
grid.component.html
<ej-grid [allowPaging]="true" [allowSorting]="true" [dataSource]="gridData">
<e-columns>
<e-column field="OrderID" headerText="Order ID" width="75" textAlign="right"></e-column>
<e-column field="EmployeeID" headerText="Employee Name" foreignKeyField="EmployeeID" foreignKeyValue="FirstName" [dataSource]="foreigndata"></e-column>
<e-column field="CustomerID" headerText="Customer ID" ></e-column>
<e-column field="Freight" width="75" format="{0:C}" textAlign="right"></e-column>
<e-column field="ShipCity" headerText="Ship City"></e-column>
</e-columns>
</ej-grid>
import { Component, OnInit, Input, ElementRef } from '@angular/core';
@Component({
selector: 'ej-app',
templateUrl: 'src/grid/grid.component.html',
})
export class GridComponent {
public gridData: any;
public foreigndata: any;
constructor() {
this.gridData = new ej.DataManager({
url: "../Home/UrlDataSource",
adaptor: new ej.UrlAdaptor()
})
}
ngOnInit(): void {
this.foreigndata = ej.parseJSON($("ej-app").attr("linkdata"));
}
}
namespace Angular2WebApp.Controllers
{
public class HomeController : Controller
{
public ActionResult Index()
{
ViewBag.Title = "Home Page";
//serialize List
ViewBag.dataSource = JsonConvert.SerializeObject(new NorthWndDBDataContext().Employees.ToList());
return View();
}
public ActionResult UrlDataSource(DataManager dm)
{
IEnumerable GridData = new NorthWndDBDataContext().Orders.ToList();
IEnumerable empData = new NorthWndDBDataContext().Employees.ToList();
DataOperations ds = new DataOperations();
var count = GridData.Cast<Order>().Count();
if (dm.Sorted != null && dm.Sorted.Count > 0)
{
if (dm.Sorted[0].Name == "EmployeeID")
GridData = SortFor((List<Order>)GridData, (List<Employee>)empData, dm.Sorted);//Only for foreignKey Column
else
GridData = ds.PerformSorting(GridData, dm.Sorted);
}
GridData = ds.PerformSkip(GridData, dm.Skip);
GridData = ds.PerformTake(GridData, dm.Take);
return Json(new { result = GridData, count = count });
}
public List<Order> SortFor(List<Order> ord, List<Employee> emp, List<Sort> Sorted)
{
if (Sorted[0].Direction == "ascending")//check whether ascending or descending
emp = emp.OrderBy(e => e.FirstName).ToList();
else
emp = emp.OrderByDescending(e => e.FirstName).ToList();
List<Order> or = new List<Order>();
for (int i = 0; i < emp.Count(); i++)
{ //Select the Field matching records
IEnumerable<Order> list = ord.Where(pred => pred.EmployeeID == emp[i].EmployeeID).ToList();
or.AddRange(list);
}
return or;
}
}
}
Asp.Net core:
<ej-grid id="Grid" allow-paging="true" allow-sorting="true">
<e-datamanager url="/Home/DataSource" adaptor="UrlAdaptor"/>
<e-columns>
<e-column field="OrderID" header-text="Order ID" text-align="Right"></e-column>
<e-column field="EmployeeID" header-text="Employee Name" foreign-key-field="EmployeeID" foreign-key-value="FirstName" datasource="ViewBag.dataSource"></e-column>
<e-column field="CustomerID" header-text="Customer ID"></e-column>
<e-column field="Freight" header-text="Freight" format="{0:C2}" text-align="Right"></e-column>
<e-column field="ShipCity" header-text="Ship City"></e-column>
</e-columns>
</ej-grid>
public class HomeController : Controller
{
public ActionResult Index()
{
ViewBag.dataSource = new NorthwindDataContext().EmployeeViews.ToList();
return View();
}
public ActionResult UrlDataSource([FromBody]DataManager dm)
{
IEnumerable GridData = OrderRepository.GetAllRecords();
IEnumerable empData = new NorthwindDataContext().EmployeeViews.ToList();
DataOperations ds = new DataOperations();
var count = GridData.Cast<EditableOrder>().Count();
if (dm.Sorted != null && dm.Sorted.Count > 0)
{
if (dm.Sorted[0].Name == "EmployeeID")
GridData = SortFor((List<EditableOrder>)GridData, (List<EmployeeView>)empData, dm.Sorted);//Only for foreignKey Column
else
GridData = ds.PerformSorting(GridData, dm.Sorted);
}
GridData = ds.PerformSkip(GridData, dm.Skip);
GridData = ds.PerformTake(GridData, dm.Take);
return Json(new { result = GridData, count = count });
}
public List<EditableOrder> SortFor(List<EditableOrder> ord, List<EmployeeView> emp, List<Sort> Sorted)
{
if (Sorted[0].Direction == "ascending")//check whether ascending or descending
emp = emp.OrderBy(e => e.FirstName).ToList();
else
emp = emp.OrderByDescending(e => e.FirstName).ToList();
List<EditableOrder> or = new List<EditableOrder>();
for (int i = 0; i < emp.Count(); i++)
{
//Select the Field matching records
IEnumerable<EditableOrder> list = ord.Where(pred => pred.EmployeeID == emp[i].EmployeeID).ToList();
or.AddRange(list);
}
return or;
}
}
Figure: Grid Sorted with the ForeignKeyValue
I hope you enjoyed learning about how to perform Sorting on ForeignKey column while using Remote Data in .NET Core Grid.
You can refer to our .NET Core Grid feature tour page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our example to understand how to create and manipulate data.
For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.
If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!
