ASP.Net C#控制器返回DataTable实现DataTables服务器端处理报错

错误根源分析 #

你遇到的Invalid JSON response错误主要来自三个核心问题：

1. 控制器直接返回DataTable类型，ASP.NET MVC默认序列化后的JSON格式不符合DataTables服务器端要求的结构（必须包含draw、recordsTotal、recordsFiltered、data四个核心字段）。
2. 控制器未接收并处理DataTables发送的服务器端参数（分页、排序、搜索条件），无法实现真正的服务器端数据处理。
3. 视图中Ajax配置存在参数大小写错误，且未正确设置请求格式。

完整解决方案 #

1. 定义DataTables请求/响应模型 #

在项目中添加以下类，用于接收前端请求参数和构造符合要求的响应结构：

// 接收DataTables前端请求参数的模型
public class DataTablesRequest
{
    public int draw { get; set; }
    public int start { get; set; }
    public int length { get; set; }
    public Search search { get; set; }
    public List<Order> order { get; set; }
    public List<Column> columns { get; set; }
}

public class Search
{
    public string value { get; set; }
    public bool regex { get; set; }
}

public class Order
{
    public int column { get; set; }
    public string dir { get; set; }
}

public class Column
{
    public string data { get; set; }
    public string name { get; set; }
    public bool searchable { get; set; }
    public bool orderable { get; set; }
    public Search search { get; set; }
}

// 返回给DataTables的响应模型
public class DataTablesResponse<T>
{
    public int draw { get; set; }
    public int recordsTotal { get; set; }
    public int recordsFiltered { get; set; }
    public List<T> data { get; set; }
}

2. 修改控制器方法 #

将原控制器方法替换为以下代码，实现服务器端分页、过滤、排序逻辑，并返回符合要求的JSON：

[HttpPost]
public JsonResult UserAuditTrail(DataTablesRequest request)
{
    int currentYear = DateTime.Now.Year;
    // 初始化原始数据查询
    var query = db.tblUserAuditTrails.Where(x => x.fld_CreatedDT.Value.Year == currentYear);

    // 统计总记录数（未过滤）
    int recordsTotal = query.Count();

    // 处理搜索过滤
    if (!string.IsNullOrEmpty(request.search.value))
    {
        string searchValue = request.search.value.ToLower();
        query = query.Where(x => 
            x.fld_CreatedBy.ToLower().Contains(searchValue) ||
            x.fld_UserActivity.ToLower().Contains(searchValue) ||
            x.fld_CreatedDT.Value.ToString("yyyy-MM-dd").Contains(searchValue)
        );
    }

    // 处理排序
    if (request.order != null && request.order.Any())
    {
        var targetColumn = request.columns[request.order[0].column];
        query = request.order[0].dir switch
        {
            "asc" when targetColumn.data == "fld_ID" => query.OrderBy(x => x.fld_ID),
            "desc" when targetColumn.data == "fld_ID" => query.OrderByDescending(x => x.fld_ID),
            "asc" when targetColumn.data == "fld_CreatedBy" => query.OrderBy(x => x.fld_CreatedBy),
            "desc" when targetColumn.data == "fld_CreatedBy" => query.OrderByDescending(x => x.fld_CreatedBy),
            "asc" when targetColumn.data == "fld_UserActivity" => query.OrderBy(x => x.fld_UserActivity),
            "desc" when targetColumn.data == "fld_UserActivity" => query.OrderByDescending(x => x.fld_UserActivity),
            "asc" when targetColumn.data == "fld_CreatedDT" => query.OrderBy(x => x.fld_CreatedDT),
            "desc" when targetColumn.data == "fld_CreatedDT" => query.OrderByDescending(x => x.fld_CreatedDT),
            _ => query.OrderBy(x => x.fld_CreatedDT) // 默认排序
        };
    }
    else
    {
        query = query.OrderBy(x => x.fld_CreatedDT);
    }

    // 处理分页
    int recordsFiltered = query.Count();
    var paginatedData = query.Skip(request.start).Take(request.length).ToList();

    // 构造响应并返回
    var response = new DataTablesResponse<tblUserAuditTrail>
    {
        draw = request.draw,
        recordsTotal = recordsTotal,
        recordsFiltered = recordsFiltered,
        data = paginatedData
    };

    return Json(response);
}

3. 修正视图中的Ajax配置 #

修改视图中的DataTables初始化代码，修正参数错误并配置正确的请求格式：

<script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.min.js"></script>
<script>
    $(document).ready(function () {
        $("#tbl_model").DataTable({
            "processing": true,
            "serverSide": true,
            "filter": true,
            "ajax": {
                "url": "@Url.Action("UserAuditTrail", "AuditTrail")",
                "type": "POST",
                "dataType": "json", // 修正为jQuery要求的驼峰式参数名
                "contentType": "application/json; charset=utf-8", // 指定请求格式
                "data": function (d) {
                    return JSON.stringify(d); // 将DataTables参数序列化为JSON字符串
                }
            },
            "columnDefs": [{
                targets: 0,
                render: function (data, type, row, meta) {
                    return meta.row + meta.settings._iDisplayStart + 1;
                }
            }],
            "columns": [
                { "data": "fld_ID", "name": "Id", "autoWidth": true },
                { "data": "fld_CreatedBy", "name": "User", "autoWidth": true },
                { "data": "fld_UserActivity", "name": "Activity", "autoWidth": true },
                { "data": "fld_CreatedDT", "name": "Date", "autoWidth": true }
            ]
        });
    });
</script>

内容的提问来源于stack exchange，提问作者Aini Hidayah Hairuzaman