Unable to display DataTable in ASP.NET Core Web API

Question

I have to display a datatable in my ASP.NET Core Web API.

Here is my code.

Controller:

[Route("api/[controller]")]
[ApiController]
public class MapDataTableDemo : ControllerBase
{
    Product p;
    
    public MapDataTableDemo()
    {
        p = new Product();
    }
     
    [HttpGet]
    public async Task<IActionResult> MapDataTable()
    {
        var response = p.GetDataTable();
        return Ok(response);
    }
}

Product model class:

public class Product
{
    public DataTable dataTable { get; set; }

    public DataTable GetDataTable()
    {
        this.dataTable = new DataTable();
        this.dataTable.Columns.Add("product name");
        this.dataTable.Columns.Add("product price");
        this.dataTable.Rows.Add("mobile", 20000);
        this.dataTable.Rows.Add("tv", 15000);
        this.dataTable.Rows.Add("washing machine", 30000);
        return this.dataTable;
    }
}

When I debug the API, I can see the response but unable to display the datatable.

I get error as follows:

Error: response status is 500

Response body

System.NotSupportedException: Serialization and deserialization of 'System.Type' instances are not supported. Path: $.Columns.DataType.

System.NotSupportedException: Serialization and deserialization of 'System.Type' instances are not supported.

at System.Text.Json.Serialization.Converters.UnsupportedTypeConverter1.Write(Utf8JsonWriter writer, T value, JsonSerializerOptions options) at System.Text.Json.Serialization.JsonConverter1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.Serialization.Metadata.JsonPropertyInfo1.GetMemberAndWriteJson(Object obj, WriteStack& state, Utf8JsonWriter writer) at System.Text.Json.Serialization.Converters.ObjectDefaultConverter1.OnTryWrite(Utf8JsonWriter writer, T value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.Serialization.JsonConverter1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.JsonConverter1.TryWriteAsObject(Utf8JsonWriter writer, Object value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.Serialization.JsonConverter1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.Converters.IEnumerableConverter1.OnWriteResume(Utf8JsonWriter writer, TCollection value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.Serialization.JsonCollectionConverter`2.OnTryWrite(Utf8JsonWriter writer, TCollection value, JsonSerializerOptions options, WriteStack& state)

Any solutions?

Thank you!

Answer 1

While working with ASP.NET Core and DataTables, one might face issues when trying to implement server-side processing without a traditional MVC controller. This solution explains how you can integrate DataTables directly with Razor Pages in an ASP.NET Core project, addressing common issues such as Ajax requests and handling server-side data processing.

Problem

The challenge arises when trying to set up DataTables in Razor Pages without using a controller. Common issues include:

• Ajax request failures.
• Handling server-side pagination, sorting, and searching.
• Formatting data for DataTables in a way it expects.

Solution

Here’s a step-by-step guide to implementing DataTables with Razor Pages, including how to properly configure Ajax and server-side processing.

1. Razor Page Markup

Create a table in your Razor page that will be populated by DataTables**. You can include buttons for actions like add, edit, or delete records:

@page
@model YourApp.Pages.Vehicules.IndexModel

<div class="toolbar chrome-style-toolbar mb-3">
    <button onclick="OnNew()" type="button" class="btn btn-chrome-style" data-bs-toggle="modal" data-bs-target="#newWindow">
        <i class="fas fa-plus"></i> New
    </button>
    <button onclick="OnEdit()" id="editButton" type="button" class="btn btn-chrome-style" data-bs-toggle="modal" data-bs-target="#editWindow">
        <i class="fas fa-edit"></i> Edit
    </button>
    <button id="deleteButton" type="button" class="btn btn-chrome-style" data-bs-toggle="modal" data-bs-target="#confirmationModal">
        <i class="fas fa-trash"></i> Delete
    </button>
</div>

<div class="container">
    <div class="table-responsive">
        <table id="maindatatable" class="table table-bordered">
            <thead>
                <tr>
                    <th>UID</th>
                    <th>License Plate</th>
                    <th>Brand</th>
                    <th>Chassis Number</th>
                    <th>Client</th>
                </tr>
            </thead>
            <tbody></tbody>
        </table> 
    </div>
</div>

@section Scripts {
    <script src="~/lib/jquery/dist/jquery.min.js"></script>
    <script src="~/lib/datatables/js/jquery.dataTables.min.js"></script>
    <script src="~/js/vehicules.js"></script>
}

2. JavaScript for DataTables Initialization

In your JavaScript file (vehicules.js), initialize DataTables and point it to the API that fetches the data for server-side processing:

$(document).ready(function () {
    $('#maindatatable').DataTable({
        ajax: {
            url: "/api/Vehicules/GetVehiculeList",  // The API that serves the data
            type: "POST"
        },
        processing: true,
        serverSide: true,
        filter: true,
        columns: [
            { "data": "uid", name: "UID", "visible": false },
            { "data": "matricule", name: "License Plate" },
            { "data": "marque", name: "Brand" },
            { "data": "numeroChassis", name: "Chassis Number" },
            { "data": "client", name: "Client" }
        ]
    });
});

3. Controller Logic

Create a controller to handle the server-side request. The logic will manage pagination, sorting, searching, and send back the data in the format expected by DataTables.

[Route("api/[controller]")]
[ApiController]
public class VehiculesController : ControllerBase
{
    private readonly WebDbContext _context;

    public VehiculesController(WebDbContext context)
    {
        _context = context;
    }

    [HttpPost("GetVehiculeList")]
    public IActionResult GetVehiculeList()
    {
        var draw = Request.Form["draw"].FirstOrDefault();
        var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();
        var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();
        var searchValue = Request.Form["search[value]"].FirstOrDefault();
        int pageSize = Convert.ToInt32(Request.Form["length"].FirstOrDefault() ?? "0");
        int skip = Convert.ToInt32(Request.Form["start"].FirstOrDefault() ?? "0");

        // Base query
        var query = _context.Vehicules
                            .Include(v => v.Client)
                            .Include(v => v.Marque)
                            .AsQueryable();

        // Apply search filter
        if (!string.IsNullOrEmpty(searchValue))
        {
            query = query.Where(x => x.Matricule.ToLower().Contains(searchValue.ToLower()) ||
                                     x.NumeroChassis.ToLower().Contains(searchValue.ToLower()) ||
                                     x.Client.Nom.ToLower().Contains(searchValue.ToLower()) ||
                                     x.Marque.Libelle.ToLower().Contains(searchValue.ToLower()));
        }

        // Get total records count before pagination
        var totalRecords = query.Count();

        // Apply sorting if needed
        if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortColumnDirection))
        {
            if (sortColumnDirection == "asc")
                query = query.OrderBy(x => EF.Property<object>(x, sortColumn));
            else
                query = query.OrderByDescending(x => EF.Property<object>(x, sortColumn));
        }

        // Apply pagination
        var vehicules = query.Skip(skip).Take(pageSize)
                             .Select(v => new
                             {
                                 UID = v.UID,
                                 Matricule = v.Matricule,
                                 Marque = v.Marque.Libelle,
                                 NumeroChassis = v.NumeroChassis,
                                 Client = v.Client.Nom,
                                 DateCreate = v.DateCreate,
                                 DateUpdate = v.DateUpdate
                             })
                             .ToList();

        // Return data in format DataTables expects
        var returnObj = new
        {
            draw = draw,
            recordsTotal = totalRecords,
            recordsFiltered = totalRecords,  // Implement custom filter logic if needed
            data = vehicules
        };

        return Ok(returnObj);
    }
}

4. Key Points to Ensure
5. Server-Side Processing: Ensure serverSide: true is set in your DataTables initialization to enable server-side processing.
6. Search and Sorting: Implement filtering and sorting within the query for accurate results.
7. API Endpoint: Ensure the ajax URL points to the correct API endpoint that returns the data in JSON format.

Why This Solution Works

This setup ensures that DataTables is properly initialized and retrieves its data from an ASP.NET Core API. The controller returns the data in a format expected by DataTables, including pagination, sorting, and search capabilities. This structure also allows you to easily integrate other actions (like creating, editing, and deleting records) as required by your application.

Common Issues and Fixes

• 500 Errors: Ensure the API route is correct and matches the DataTables request.
• Data Not Showing: Verify the returned data format, especially the names of fields returned by the API.

This approach resolves common issues with DataTables and ASP.NET Core Razor Pages and should serve as a guide for others facing similar problems.

Answer 2

Install the Microsoft.AspNetCore.Mvc.NewtonsoftJson NuGet package.

Add this line of code in Program.cs:

builder.Services.AddControllers().AddNewtonsoftJson();

It worked for me.