OData controller function to get entity collections using $expand async

Question

I recently updated a test OData project from .Net core 3.11 and OData4 to .Net6 core, EF6 and OData8. As part of this I reviewed the Controller code (it was several years old) and implemented async functions.

I noticed that with the older function using IActionResult<Iqueryable>> will return related entities when queried using the $expand query option. However when I used Task<IActionResult> the related entities return empty.

CompaniesController.cs - previous function

[EnableQuery]
[HttpGet("odata/Companies({key})")]
 public ActionResult<IQueryable<Company>> GetbyId([FromODataUri] int key)
 {
     IQueryable<Company> result = _reviewsDbContext.Companies
        .Where(p => p.CompanyId == key)
        .Include("Rating");

         if (result == null)
         {
             return NotFound();
         }
         else
         {
             return Ok(result);
         }

 }

and the async update:

CompaniesController.cs - updated

[HttpGet("odata/v1/Companies({key})")]
public async Task<ActionResult<IQueryable<Company>>> GetbyId([FromODataUri] int key)
{
    var result = await _reviewsDbContext.Companies
        .FirstOrDefaultAsync(p => p.CompanyId == key);

         if (result == null)
         {
             return NotFound();
         }
         else
         {
             return Ok(result);
          }
}

The other notable difference is .Where versus FirstOrDefaultAsync.

I also updated the function to use async Task<ActionResult<IQueryable>> using .FirstOrDefaultAsync and had the same result (no related entities).

Any suggestions on the best way to introduce an async function while still supporting the $expand query option for related entities?

Thanks.

[Update1] Adding program.cs snippet as per the request in the comments.

program.cs
<snippet>

builder.Services.AddControllers()
    .AddOData(opt => 
        opt.AddRouteComponents("odata",
            new ReviewsEntityDataModel().GetEntityDataModel()
                  
           
  ).Select().Expand().Filter().OrderBy().Count().SetMaxTop(100)
        );

Answer 1

Digging into this it seems that this is an issue due to Deferred Execution. In short combining [EnableQuery] with async code is not currently supported.

This was called out in a Pluralsight course from Kevin Dockx. I didn't find anything in the docs calling this out, but this would explain the behaviour I'm seeing and Kevin's example

Basically, use async where it makes sense and where the expansion (or other query options are required, disable async for now.