Skip to content
Advertisement

Why Is My API Not Returning Nested Object (.NET 6 Entity Framework and SQL DB)

I have created an API, using EF and database first approach via scaffolding.

I have the following in my context file

            modelBuilder.Entity<Request>(entity =>
        {
            entity.ToTable("Request", "rmr");

            entity.Property(e => e.CreatedOn).HasColumnType("datetime");

            entity.Property(e => e.RaisedBy).HasMaxLength(256);

            entity.Property(e => e.WorklistName).HasMaxLength(32);

        });

        modelBuilder.Entity<RequestLine>(entity =>
        {
            entity.HasKey(e => new { e.RequestId, e.LineNumber })
                .HasName("PK_RMR_REQUESTLINE");

            entity.ToTable("RequestLine", "rmr");

            entity.Property(e => e.Batch).HasMaxLength(32);

            entity.Property(e => e.CancelBy).HasMaxLength(256);

            entity.Property(e => e.CancelDate).HasColumnType("datetime");

            entity.Property(e => e.CostCentre).HasMaxLength(32);

            entity.Property(e => e.Destination).HasMaxLength(32);

            entity.Property(e => e.FirstDeliveryDate).HasColumnType("datetime");

            entity.Property(e => e.ProcessOrder).HasMaxLength(32);

            entity.Property(e => e.Sku)
                .HasMaxLength(32)
                .HasColumnName("SKU");

            entity.Property(e => e.SubmittedDate).HasColumnType("datetime");

            entity.HasOne(d => d.Request)
                .WithMany(p => p.RequestLines)
                .HasForeignKey(d => d.RequestId)
                .HasConstraintName("FK_RMR_REQUESTLINE_REQUEST");
        });

and then the following in my models

Request:

public partial class Request
{
    public Request()
    {
        RequestLines = new HashSet<RequestLine>();
    }

    public long Id { get; set; }
    public string RaisedBy { get; set; } = null!;
    public DateTime CreatedOn { get; set; }
    public string? WorklistName { get; set; }

    public virtual ICollection<RequestLine> RequestLines { get; set; }
}

Request Line:

public partial class RequestLine
{

    public long RequestId { get; set; }
    public int LineNumber { get; set; }
    public string Sku { get; set; } = null!;
    public string Batch { get; set; } = null!;
    public int Quantity { get; set; }
    public string? CostCentre { get; set; }
    public string? ProcessOrder { get; set; }
    public string Destination { get; set; } = null!;
    public DateTime FirstDeliveryDate { get; set; }
    public DateTime? SubmittedDate { get; set; }
    public DateTime? CancelDate { get; set; }
    public string? CancelBy { get; set; }
}

My Request controller looks like this:

[HttpGet]
public async Task<ActionResult<IEnumerable<Request>>> GetRequests()
{
  if (_context.Requests == null)
  {
      return NotFound();
  }
    return await _context.Requests.ToListAsync();
}

In Swagger, it is displayed correctly in the example, based on the model:

[
  {
    "id": 0,
    "raisedBy": "string",
    "createdOn": "2022-06-15T17:32:27.129Z",
    "worklistName": "string",
    "requestLines": [
      {
        "requestId": 0,
        "lineNumber": 0,
        "sku": "string",
        "batch": "string",
        "quantity": 0,
        "costCentre": "string",
        "processOrder": "string",
        "destination": "string",
        "firstDeliveryDate": "2022-06-15T17:32:27.129Z",
        "submittedDate": "2022-06-15T17:32:27.129Z",
        "cancelDate": "2022-06-15T17:32:27.129Z",
        "cancelBy": "string",
        "request": "string"
      }
    ]
  }
] 

But when running the GET, what I am actually seeing is:

[
  {
    "id": 1,
    "raisedBy": "GEORGE",
    "createdOn": "2022-06-13T13:14:31.813",
    "worklistName": "WORKLIST1",
    "requestLines": []
  },
  {
    "id": 10008,
    "raisedBy": "FFFFFF",
    "createdOn": "2022-06-15T16:34:18.297",
    "worklistName": "WORKLIST1",
    "requestLines": []
  }
]

The requestLines lists are showing empty, but there is data present in my DB for these.

Apologies for the length of the question. If further information is needed please let me know.

Thanks in advance!

Advertisement

Answer

Something like this

return await _context.Requests.Include(x => x.RequestLines).ToListAsync();
7 People found this is helpful
Advertisement