Problem with applying filter on IQueryable

[rpanchevski]

We are trying to implement latest OData in our project. We are using EF Core, and what we are trying to do is, map the domain set to new IQueryable of Dto Models using custom expression function as mapper. After the apply of the OData filters, the query is broken and cannot be executerd.

Assemblies affected

Microsoft.AspNetCore.OData v7.3.0
Microsoft.EntityFrameworkCore v3.1.2
.Net Core 3.1 WebApi Project

Reproduce steps

IQueryable that is returned in the controller

    _dbContext.PurchaseTypes.Select(Mapper.ToDto())

Expression function used for mapping

    public static Expression<Func<PurchaseType, PurchaseTypeDto>> ToDto()
    {
        return domain => new PurchaseTypeDto
        {
            Id = domain.Id,
            Code = domain.Code
        };
    }

In the controller, we have service call (which returns IQueryable) and we are applying the ODataQueryOptions opts.

    [HttpGet]
    public async Task<IActionResult> Get(ODataQueryOptions<PurchaseTypeDto> opts)
    {
        var query =
            opts.ApplyTo(_purchaseTypeService.GetAll()) as IQueryable<PurchaseTypeDto>;

        var result = await query.ToListAsync();

        return Ok(result);
    }

Expected result

When the url is accessed https://localhost:44361/odata/PurchaseType?%24filter=Id%20eq%202,
execute the query and result is returned

Actual result

The created query could not be executed

InvalidOperationException: The LINQ expression
'DbSet < PurchaseType >
.Where(p => new PurchaseTypeDto {
Id = p.Id,
Code = p.Code
}.Id == __TypedProperty_0)' could not be translated.

[ElizabethOkerio]

@rpanchevski Can you try the same thing with EF CORE 2.2. EF CORE 3 has some limitations along client-side valuation of functions.

[rpanchevski]

@rpanchevski Can you try the same thing with EF CORE 2.2. EF CORE 3 has some limitations alongside client valuation of functions.

It works using EF core 2.2.6

[stevendarby]

One such function that has to be evaluated client-side is substring - see #20173.

For functions that can't be translated to SQL, is there a plan for the ApplyTo method to handle the ToList/etc. internally? Without parsing the functions ourselves, it's difficult to know if the filter can be evaluated in SQL or in client before we pass it to AppyTo.