I get this error when I'm doing the datatable serverside processing.

The error is thrown when I call this method with Ajax.

I think the problem is server-side coding and I didn't get the problem maybe it's a dynamic linq syntax error.

What is the right syntax for dynamic linq for this statement?

This is my C# code:

public ActionResult Indexer()
{         
    int start = Convert.ToInt32(Request["start"]);
    int length = Convert.ToInt32(Request["length"]);

    string searchValue = Request["search[value]"];           
    string sortColumnName = Request["columns[" Request["order[0][column]"]   "][name]"];

    string sortDirection = Request["order[0][dir]"];

    int recordsTotal = 0;     

    List<Employee> Employee = _context.Employees.ToList();

    if (!string.IsNullOrEmpty(searchValue)) //filter
    {
        Employee = Employee.Where(x => x.Emp_ID.ToString().Contains(searchValue.ToString()) || 
                                       x.First_Name.ToLower().Contains(searchValue.ToLower()) || 
                                       x.Last_Name.ToLower().Contains(searchValue.ToLower()) || 
                                       x.Gender.ToLower().Contains(searchValue.ToLower()) || 
                                       x.Salary.ToString().Contains(searchValue.ToString())).ToList();
    }

    //sorting

    if (!(string.IsNullOrEmpty(sortColumnName) && string.IsNullOrEmpty(sortDirection)))
    {
        // This line throws the error
        Employee = Employee.OrderBy(sortColumnName   " "   sortDirection).ToList();
    }

    // Paging
    Employee = Employee
                   .Skip(start).Take(length)
                   .ToList<Employee>();

    recordsTotal = Employee.Count();

    return Json(new { data = Employee }, JsonRequestBehavior.AllowGet);
}

And this is the script which I believe is fine:

@section scripts {
    <script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
    <script>
    $(document).ready(function () {
        $('#mytable').DataTable({
            "ajax": {
                "url": "/Home/Indexer",
                "type": "POST",
                "datatype": "josn",
            },
            "columns": [
                { "data": "Emp_ID", "name": "Emp_Id" },
                { "data": "First_Name", "name": "First_Name" },
                { "data": "Last_Name", "name": "Last_Name" },
                { "data": "Gender", "name": "Gender" },
                { "data": "Salary", "name": "Salary" },
            ],
            "serverSide": "true",
            "order": [0, "acs"],
            "processing": "true",
        });
    })
</script>
}

CodePudding user response：

You have a couple of problems here.

First, the list

List<Employee> Employee = _context.Employees.ToList();

is a really bad idea. It's reading the entire database table into memory, which, if it's large, could cause memory issues. But, more importantly, everything done after that will be done in C# on your web server, instead of by the database server. -- You are cutting the database server out of the very thing it was designed to do. You want to keep it as an IQueryable<> until the very end, and that would be the only place you use .ToList().

IQueryable<Employee> Employee = _context.Employees;

Next, we have the first if(), which is mostly fine, but you know searchValue is a string, so why are you continually trying to convert it to a string? Why keep converting it to lower case? And again, no ToList()

if (!string.IsNullOrEmpty(searchValue)) //filter
{
    searchValue = searchValue.ToLower();
    Employee = Employee.Where(x => x.Emp_ID.ToString().Contains(searchValue) || 
                                   x.First_Name.ToLower().Contains(searchValue) || 
                                   x.Last_Name.ToLower().Contains(searchValue) || 
                                   x.Gender.ToLower().Contains(searchValue) || 
                                   x.Salary.ToString().Contains(searchValue));
}

Now, we get to the line you asked about. Basically, you are trying to get LINQ to use SQL syntax. Linq wants its own. But first, you have a logic error in your if() statement. You have essentially if (!(A && B)). That's equal to if(!A || !B). What you really want is if(!A && !B).

if (!string.IsNullOrEmpty(sortColumnName) && !string.IsNullOrEmpty(sortDirection))
{

Getting the proper OrderBy statement from a string is a tricky topic, requiring reflection, nicely documented in this question: Dynamic Order By in Linq

Finally, we actually run the query of the dataserver, which is triggered by the ToList().

// Paging
var lstEmployee = Employee
               .Skip(start).Take(length)
               .ToList();

recordsTotal = lstEmployee.Count();

return Json(new { data = lstEmployee }, JsonRequestBehavior.AllowGet);