Using `GetQueryAsync` produces more complex SQL query than regular OData query

[hmz777]

This is my first time using AutoMapper with OData, I have a test EFCore API enabled with all the odata features with the following URL: https://localhost:<port>/odata/patients

Patients:

public class PatientDTO : DTOBase
{
    public string? FirstName { get; set; }
    public string? MiddleName { get; set; }
    public string? LastName { get; set; }
    public int Age { get; set; }
    public virtual Gender Gender { get; set; }
    public string? PhoneNumber { get; set; }
    public ExtraDataDTO? ExtraData { get; set; }
    public virtual List<AppointmentDTO> Appointments { get; set; }
    public virtual List<TreatmentDTO> Treatments { get; set; }
    public virtual List<NoteDTO> Notes { get; set; }
}

(The DTO in this situation has the exact same structure as actual DB Entity)

but using GetQueryAsync like this:

// GET: api/Patients
[HttpGet]
public async Task<IActionResult> GetPatients(ODataQueryOptions<PatientDTO> options)
{
    return Ok(await _context.Patients.GetQueryAsync<PatientDTO, Patient>(mapper, options));
}

produces this SQL:

SELECT p."Id",
       p."FirstName",
       p."MiddleName",
       p."LastName",
       p."Age",
       p."Gender",
       p."PhoneNumber", (e."Id" IS NULL), e."Id",
                                          e."Data",
                                          a."AppointmentType",
                                          a."Date",
                                          a."Id",
                                          t0."Id",
                                          t0."TreatmentType",
                                          t0."TotalCost",
                                          t0."PaymentType",
                                          t0."PaymentStatus",
                                          t0."Id0",
                                          t0."Value",
                                          n."Id",
                                          n."Value"
FROM "Patients" AS p
LEFT JOIN "ExtraDatas" AS e ON p."ExtraDataId" = e."Id"
LEFT JOIN "Appointments" AS a ON p."Id" = a."PatientId"
LEFT JOIN
  (SELECT t."Id",
          t."TreatmentType",
          t."TotalCost",
          t."PaymentType",
          t."PaymentStatus",
          p0."Id" AS "Id0",
          p0."Value",
          t."PatientId"
   FROM "Treatments" AS t
   LEFT JOIN "Payments" AS p0 ON t."Id" = p0."TreatmentId") AS t0 ON p."Id" = t0."PatientId"
LEFT JOIN "Notes" AS n ON p."Id" = n."PatientId"
ORDER BY p."Id",
         e."Id",
         a."Id",
         t0."Id",
         t0."Id0"

while a regular odata action with EnableQuery with the same URL:

 // GET: api/Patients
[HttpGet]
[EnableQuery]
public async Task<IActionResult> GetPatients()
{
   return Ok(_context.Patients);
}

SELECT p."Id",
       p."Age",
       p."CreationDate",
       p."ExtraDataId",
       p."FirstName",
       p."Gender",
       p."LastName",
       p."MiddleName",
       p."PhoneNumber",
       p."UpdateDate"
FROM "Patients" AS p                                                                                        

Am I missing something here?

[wbuck]

Can you show your AutoMapper profile for the PatientDTO? I believe what you need to do is call ExplicitExpansion in your Patient profile.

CreateMap<Patient, PatientDTO>( )
    .ForAllMembers( opts => opts.ExplicitExpansion( ) );

The default behavior will expand all of your navigation properties. Setting ExplicitExpansion will of course mean that a consumer of the API will now have to explicitly expand the navigation properties in order to include those entities in the result set.

Also, as a side note (unrelated to this library) you're currently executing your query synchronously. Take a look at this issue. In order to get around that you can explicitly call ToListAsync on the IQueryable.

[HttpGet]
public async Task<IActionResult> GetPatients(ODataQueryOptions<PatientDTO> options)
{
    var queryable = await _context.Patients.GetQueryAsync( mapper, options )
    return Ok( await queryable.ToListAsync( ) );
}

[hmz777]

@wbuck Thank you! This works as expected. And I appreciate the issue link, I didn't know about this.