$expand not working with ODataQueryOptions in the backend

[renatolombardo]

I have a controller which has OdataQueryOptions as a parameter, as follows:

[EnableQuery]
public async Task<ActionResult<IEnumerable<SecuredEmployee>>> Get([FromQuery] int? managerLevel,
                                                                  [FromQuery] string? managerEmployeeNumber,
                                                                  [FromQuery] bool isSearch = false,
                                                                  ODataQueryOptions<SecuredEmployee>? options = null)
{
    var userId = "[email protected]";
    isSearch = true;

    var employees = await mediator.Send(new ListEmployeesQuery(managerLevel, managerEmployeeNumber, userId, isSearch: isSearch), CancellationToken.None);

    var filteredResults = options?.Filter.ApplyTo(employees.AsQueryable(), new ODataQuerySettings()) as IQueryable<SecuredEmployee>;

    var expandedResults = options?.SelectExpand.ApplyTo(filteredResults, new ODataQuerySettings()) as IQueryable<SecuredEmployee>;

    return Ok(employees);
}

And here is the call:

https://localhost:7145/odata/ExportSearchResults/
        ?$filter=((contains(forename, 'dange')) or (contains(surname, 'dange')) or (contains(employeeNumber, 'dange'))) and (status eq true)
        &$expand=PerformanceReviews,EngagementScores,TalentAssessments,SecuredEmployeeDetails
        &isSearch=true&$top=5&$skip=0&$count=true

After the return Ok(employees), the filter and expand are correctly applied.

Also, filteredResults returns the correct results, with $filter applied. expandedResults returns null.

Here is the SelectExpand property:

And here's the OData configuration:

public static ODataConventionModelBuilder AddODataEntities(this ODataConventionModelBuilder modelBuilder)
{
    modelBuilder.EntitySet<SecuredEmployee>("Employee").EntityType.HasKey(entity => entity.EmployeeId);
    modelBuilder.EntitySet<SecuredPerformanceReview>("PerformanceReview").EntityType.HasKey(entity => entity.Id);
    modelBuilder.EntitySet<SecuredEngagementScore>("EngagementScores").EntityType.HasKey(entity => entity.Id);
    modelBuilder.EntitySet<SecuredTalentAssessment>("TalentAssessments").EntityType.HasKey(entity => new { entity.EmployeeId, entity.Year });
    modelBuilder.EntitySet<TalentAssessmentAuditLog>("TalentAssessmentAuditLogs").EntityType.HasKey(entity => new { entity.EmployeeId, entity.Year, entity.IdentityId, entity.EffectiveDate });
    modelBuilder.EntitySet<SecuredEmployeeDetails>("EmployeeDetails").EntityType.HasKey(entity => new { entity.EmployeeId });
    modelBuilder.EntitySet<SecuredMobilityConsideration>("MobilityConsiderations").EntityType.HasKey(entity => entity.Id);
    modelBuilder.EntitySet<SecuredEmployee>("ExportSearchResults").EntityType.HasKey(entity => entity.EmployeeId);

    return modelBuilder;
}

And the OData statement:

services.AddControllers().AddOData(opt => opt.Count().Filter().Expand().Select().OrderBy().SetMaxTop(100)
    .AddRouteComponents("odata", builder.GetEdmModel()));

The reason is that I'm trying to use the same filter applied in the frontend to export the exact same result to an Excel file, so I need $expand and $filter to work properly. In this case, $expand is not working within the controller. Only after the result is returned.

[julealgon]

Do not use EnableQuery and pass ODataQueryOptions at the same time. This will cause operations to be applied twice.

I'd also recommend applying the entire ODataQueryOptions if you can instead of picking and choosing what to apply like what you are doing.

[xuzhg]

@renatolombardo expandedResults returns null is expected when you apply the '$select and $expand' to the data source then cast to 'IQueryable< SecuredEmployee >'. This is becasue the 'RESULT' of $select or $expand is not a SecuredEmployee again, the result is the projected object, behide the scense of ASP.NET Core OData, it's an object of "SelectExpandWrapper",

I had a PR to provide extension for customers to 'cast' the result of $select and $expand to its origin 'type', you can refer to this 'PR. It's still looking forward the review from the team.