Datatable server side processing c# mvc

Question

Controller:

public class HomeController : Controller
{
    public ActionResult Index()
    {
        return View();
    }
    [HttpPost]
    public ActionResult LoadData()
    {
        var draw = Request.Form.GetValues("draw").FirstOrDefault();
        var start = Request.Form.GetValues("start").FirstOrDefault();
        var length = Request.Form.GetValues("length").FirstOrDefault();
        var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();
        var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
        int pageSize = length != null ? Convert.ToInt32(length) : 0;
        int skip = start != null ? Convert.ToInt32(start) : 0;
        int totalRecord = 0;
        using (ACETeaEntities db = new ACETeaEntities())
        {
            var v = (from item in db.Drinks_Category select item);
            if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
            {
                v = v.OrderBy(sortColumn + " " + sortColumnDir);
            }
            totalRecord = v.Count();
            var data = v.Skip(skip).Take(pageSize).ToList();
            return Json(new { draw = draw, recordsFilterd = totalRecord, recordsTotal = totalRecord, data = data }, JsonRequestBehavior.AllowGet);
        }
    }
}

View:

<script src="~/Scripts/jquery-1.7.2.js" ></script>

<script src="~/Scripts/DataTables/jquery.dataTables.js" ></script>

<link rel="stylesheet" type="text/css" href="~/Content/DataTables/css/jquery.dataTables.css">


<script>

    $(document).ready(function () {
        $('#example').dataTable({
            "processing": true,
            "serverSide": true,
            "filter": true,
            "ordermulti": false,
            "ajax": {
                "url": "/Home/LoadData",
                "type": "POST",
                "dataType": "json"
            },
            "columns": [
                { "data": "Name_category", "autoWidth": true },
                { "data": "Id_category", "autoWidth": true },
                { "data": "Parent", "autoWidth": true }
            ]
        });
    });
</script>


<div style="margin:30px;">
    <table id="example" class="display" cellspacing="0" width="100%">
        <thead>
            <tr style="text-align:left;">
                <th>Id</th>
                <th>Name</th>
                <th>Parent</th>
            </tr>
        </thead>
        <tfoot>
            <tr style="text-align:left;">      
            </tr>
        </tfoot>
    </table>
</div>

My code bugs at: v = v.OrderBy(sortColumn + " " + sortColumnDir);

Can someone help me fix it?

Answer 1

Note that you can also try the more updated library System.Linq.Dynamic.Core which does support OrderBy and also ThenBy.

Answer 2

When using the OrderBy method you need to pass the column you want to sort by as a property and use a different method for defining the direction

For example:

v = v.OrderBy(i => i.myColumn);
v = v.OrderByDescending(i => i.myColumn);

Since you are getting your parameters as a string, you have two options:

1) You can build your function first before passing it as well as define your direction

For example:

if (sortColumn == "myColumn")
{
    myOrderByFunc = i => i.myColumn;
}
elseif (sortColumn == "myOtherColumn")
{
    myOrderByFunc = i => i.myOtherColumn;
}

if (direction == "asc")
{
    v = v.OrderBy(myOrderByFunc);
}
elseif (direction == "desc")
{
    v = v.OrderByDescending(myOrderByFunc);
}

2) You can use the System.Linq.Dynamic library available here to use strings directly in the linq query (as you are doing now)