In this article I will explain with an example, how to populate jQuery DataTable using Entity Framework using C# in ASP.Net MVC Razor.
The data from the SQL Server Database will be populated using Entity Framework in C#.
An HTML Table will be applied the jQuery DataTables plugin and then using a jQuery AJAX call the data will be fetched from the Controller and will be assigned to the jQuery DataTables plugin in ASP.Net MVC Razor.
 
 
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Download and install Northwind Database
 
 
Creating an Entity Data Model
The very first step is to create an ASP.Net MVC Application and connect it to the Northwind Database using Entity Framework. For more details please refer my article ASP.Net MVC: Simple Entity Framework Tutorial with example.
Following is the Entity Data Model of the Customers Table of the Northwind Database which will be used later in this project.
Populate jQuery DataTable using Entity Framework using C# in ASP.Net MVC
 
 
Controller
The Controller consists of two Action methods.
Action method for handling GET operation
Inside this Action method, simply the View is returned.
 
Action method for handling jQuery AJAX operation
This Action method handles the call made from the jQuery AJAX function from the View.
Note: The following Action method handles AJAX calls and hence the return type is set to JsonResult.
 
The records are fetched from the Customers table using Entity Framework and converted to a Generic List.
Finally, the Generic List is returned to the View as JSON object.
public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        return View();
    }
 
    [HttpPost]
    public JsonResult AjaxMethod()
    {
        NorthwindEntities entities = new NorthwindEntities();
        List<Customer> customers = (from customer in entities.Customers
                                    select customer).ToList();
        return Json(customers);
    }
}
 
 
View
The View consists of an HTML Table which will be populated using jQuery DataTables plugin.
The Header Row values are already been added to the Table.
Note: In order to use jQuery DataTables, it is required to add THEAD and TBODY Tags to the HTML Table.
 
Inside the jQuery document ready event handler, a jQuery AJAX call is made to the Controller’s Action method.
Inside the Success event handler, the HTML Table has been applied with jQuery DataTables plugin and the JSON data returned from the Controller’s Action method is assigned to the data property of the jQuery DataTables plugin.
The jQuery DataTables plugin has been assigned following properties:
bLengthChange – true
The Records per Page DropDownList will be shown if set to True and hidden if set to False. Default is True.
lengthMenu – Array values
The Text and Value for the Records per Page DropDownList. It is a multi-dimensional array. The first set is the Value parts while the second set is the Text parts.
bFilter – true
The Search Box will be displayed if set to True and hidden if set to False. Default is True.
bSort – true
The Sorting feature will be enabled if set to True and disabled if set to False. Default is True.
bPaginate – true
The Paging feature will be enabled if set to True and disabled if set to False. Default is True.
Columns – Array values
The names of the Columns to be displayed and mapped to the JSON data are specified.
@{
    Layout = null;
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width"/>
    <title>Index</title>
    <style type="text/css">
        body
        {
            font-family: Arial;
            font-size: 10pt;
        }
    </style>
</head>
<body>
    <div style="width: 500px">
        <table id="tblCustomers" cellpadding="0" cellspacing="0" border="1" style="border-collapse:collapse">
            <thead>
                <tr>
                    <th>Customer Id</th>
                    <th>Name</th>
                    <th>City</th>
                    <th>Country</th>
                </tr>
            </thead>
            <tbody></tbody>
        </table>
    </div>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
    <link href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.css" rel="stylesheet" type="text/css"/>
    <script type="text/javascript">
        $(function () {
            $.ajax({
                type: "POST",
                url: "/Home/AjaxMethod",
                data: '{}',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: OnSuccess,
                failure: function (response) {
                    alert(response.d);
                },
                error: function (response) {
                    alert(response.d);
                }
            });
        });
        function OnSuccess(response) {
            $("#tblCustomers").DataTable(
            {
                bLengthChange: true,
                lengthMenu: [[5, 10, -1], [5, 10, "All"]],
                bFilter: true,
                bSort: true,
                bPaginate: true,
                data: response,
                columns: [{ 'data': 'CustomerID' },
                          { 'data': 'ContactName' },
                          { 'data': 'City' },
                          { 'data': 'Country' }]
            });
        };
    </script>
</body>
</html>
 
 
Screenshot
Populate jQuery DataTable using Entity Framework using C# in ASP.Net MVC
 
 
Downloads
Download Code