constructing a datatable with a foreign key

Question

If someone can help me with this one, I would really appreciate it. I'm trying to get a Javascript table to display in a Razor pages application I'm writing. It's mostly working, but I am having an issue with a column which has a foreign key. The column is consistently coming up blank. So, I'm using Visual studio 2019 community with .net core 3.2.
The misbehaving table looks like this

As you can see, the Category column is blank. The main page has this code

<div class="col-12 border p-3">

<table id="DT_load" class="table table-sm table-striped table-bordered" style="width:100%">
    <thead>
        <tr>

            <th>Date</th>
            <th>Category</th>
            <th>Valuation</th>
            <th>Actions</th>


        </tr>
    </thead>
</table>

</div>
@section Scripts
{

<script src="~/js/valuationsList.js"></script>

}

The javascript file that it calls is here:

var dataTable;


$(document).ready(function () {
loadDataTable();
});

function loadDataTable() {

dataTable = $('#DT_load').dataTable
    ({

        "ajax":
        {
            "url": "/api/valuations",
            "type": "GET",
            "datatype": "json"
        },
        "columns":
            [
              { "data": "date", "width": "30%" },
              { "data": "category", "width": "20%" },
                { "data": "valuation", "width": "15%" },
                {

                    "data": "id",
                    "render": function (data) {
                        return `
                     <div class="text-center">
                     <a href="/Valuations/Edit?id=${data}" class='btn btn-link text-blue' style='cursor:pointer; width:70px;'>
                         Edit
                     </a>
                     &nbsp;
                     <a href="/Valuations/Delete?id=${data}" class='btn btn-link text-blue' style='cursor:pointer; width:70px;'>
                         Delete
                     </a>

                     </div>`;
                    }, "width": "25%"
                }
            ],
        "language":
            {
            "emptyTable": "no data found"
        },
        "width": "100%"
    });
}

And the relevant method in the controller looks like this

        [HttpGet]
    public async Task<IActionResult> GetAll()
    {
        return Json(new { data = await _context.CategoryValuations.ToListAsync() });
    }

Finally, the table I am trying to access looks like this:

and the category_id field is foreign keyed to the category table which looks like this:

If I use "category" in the javascript file, then the column is blank. category_id returns an error. Any help gratefully received.

Thanks in advance.

as per Sergey's comment. Here are the classes.

First, the parent Category class

using System;
using System.Collections.Generic;

namespace Investments10.Models
{
public partial class Category
{
    public Category()
    {
        CategoryPerformance = new HashSet<CategoryPerformance>();
        CategoryValuations = new HashSet<CategoryValuations>();
        TransferValuesTransferFrom = new HashSet<TransferValues>();
        TransferValuesTransferTo = new HashSet<TransferValues>();
    }

    public int Id { get; set; }
    public string Category1 { get; set; }
    public bool InUse { get; set; }

    public virtual ICollection<CategoryPerformance> CategoryPerformance {get; set; }
    public virtual ICollection<CategoryValuations> CategoryValuations { get; set; }
    public virtual ICollection<TransferValues> TransferValuesTransferFrom { get; set; }
    public virtual ICollection<TransferValues> TransferValuesTransferTo { get; set; }
}
}

And now the Category Valuations class which is foreign keyed to Category.

using System;
using System.Collections.Generic;

namespace Investments10.Models
{
public partial class CategoryValuations
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
    public decimal Valuation { get; set; }
    public int CategoryId { get; set; }

    public virtual Category Category { get; set; }
}
}

Answer 1

Try this action code:

  
    public async Task<JsonResult> GetAll()
    {
        return Json(new { data = await _context.CategoryValuations
                                  .Select( i=> new {
                                    id=i.Id,
                                    categoryId=i.CategoryId,
                                    date=i.Date,
                                    category=i.Category.Category1,
                                    valuation=i.Valuation
                                   })
                                  .ToListAsync() 
                        });
    }