locked
Displaying foreign key name in place of id inside Jquery Datatable RRS feed

  • Question

  • User349356202 posted

    Greetings,

    I have a table named Subject which has a foreign key with another table named Category. I want to Display the Category name in place of Category id inside a Jquery Datatable that lists all the subjects from the database.

    This is the method used in the controller:

    public ActionResult AllSubjects()
        {
            return View(from i in db.Subject
                        select i);
        }
            public ActionResult GetAllSub()
            {
                using (Part1DbEntities1 db = new Part1DbEntities1())
    
                {
                    var query = (from q in db.Subject
                                 select new { q.SubjectId, q.SubjectTitle, q.CategoryId }).ToList();
                    return Json(new { data = query }, JsonRequestBehavior.AllowGet);
                }
            }

    This is the code in the view:

    $(document).ready(function () {
    
                $("#employeeTable").DataTable(
                    {
                        
                        "ajax": {
                            "url": "/TestMaking/GetAllSub",
                            "type": "GET",
                            "datatype": "json"
                        },
    
                        "columns": [
    
                            { "data": "SubjectId" },
                            { "data": "SubjectTitle" },
                            { "data": "CategoryId" }]
       });

    Thank you.

    Tuesday, April 17, 2018 2:02 PM

All replies

  • User36583972 posted

    Hi MedNasser,

    have a table named Subject which has a foreign key with another table named Category. I want to Display the Category name in place of Category id inside a Jquery Datatable that lists all the subjects from the database.

    I suggest you can change query statement in order to contain the fields of the Category table. You can refer the follow Cross-Table Queries statement.

    var query =
        from order in orders.AsEnumerable()
        join detail in details.AsEnumerable()
        on order.Field<int>("SalesOrderID") equals
            detail.Field<int>("SalesOrderID")
        where order.Field<bool>("OnlineOrderFlag") == true
        && order.Field<DateTime>("OrderDate").Month == 8
        select new
        {
            SalesOrderID =
                order.Field<int>("SalesOrderID"),
            SalesOrderDetailID =
                detail.Field<int>("SalesOrderDetailID"),
            OrderDate =
                order.Field<DateTime>("OrderDate"),
            ProductID =
                detail.Field<int>("ProductID")
        };

    Then, you can display the Category name in place of Category id inside the Jquery Datatable.

                            "columns": [
                            { "data": "SubjectId" },
                            { "data": "SubjectTitle" },
                            { "data": "CategoryName" }]

    Best Regards,

    Yong Lu

    Wednesday, April 18, 2018 7:28 AM