C# .NET Core 6.0 - IQueryable, OData, and mapped function called from LINQ statement can't be translated

Question

I'm having issues with OData, IQueryable's and mapped functions called from LINQ statements.

This is in a function (creating an IQueryable):

IQueryable<AvailableView> queryable = 
    _aContext.AView
             .FromSqlRaw(sql).AsNoTracking()
             .OrderBy(linqOrderByClause)
             .Select(x => new AvailableView()
                              {
                                  Afield1 = x.Afield1
                                  Afield2 = ConvertSomething(x.Afield2)
                              });

Here is the mapped static function ConvertSomething (well, not really, I just want to show that this function could be as simple as this):

private static string ConvertSomething(string Afield2)
{
    return Afield2;
}

This IQueryable is executed in some "paged response" class we created, and when it's time to execute the IQueryable, here's some of the code:

ODataQuerySettings settings = new ODataQuerySettings();

// ODataQueryOptions options is passed into this function
if (options != null)
{
    if (options.Filter != null)
    {
        queryable = options.Filter.ApplyTo(queryable, settings) as IQueryable<dynamic>;
    }

    if (options.OrderBy != null)
    {
        queryable = options.OrderBy.ApplyTo(queryable, settings) as IQueryable<dynamic>;
    }

    if (options.SelectExpand != null)
    {
        queryable = options.SelectExpand.ApplyTo(queryable, settings) as IQueryable<dynamic>;
    }
}

List<dynamic> dataPage = queryable
                            .Skip(skip)
                            .Take(take)
                            .ToList(); <---- this is where I run into the error

When we execute our code - no issues.

When we add OData, for example:

"$filter=tolower(Afield2) eq 'hey'"

It blows up when we execute the ToList(). I can apply the same filter to Afield1, no problem, just not Afield2. It's because we are using that static mapped function.

The error message is along the lines of:

System.InvalidOperationException: The LINQ expression 'DbSet().FromSql(our sql statement), __p_0) .OrderBy(a => a.SomeField) .ThenBy(a => a.SomeField) .ThenBy(a => a.AnotherField) .ThenBy(a => a.OneMoreField) .Where(a => (string)OurProject.ConvertSomething(a.AField2).ToLower() == __TypedProperty_3)' could not be translated.

Additional information:
Translation of method 'blah.blah.blah.ConvertSomething' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Answer 1

When working with EF IQueryable and deferred execution, any/all expressions used need to be able to be translated down into SQL. Providers will support some framework extension methods and type-specific methods (such as those under System.DateTime) but they obviously will not understand custom extension methods or other custom functions.

The simplest solution, especially when it apples to projections, is to move the translation responsibility down into the projection. (ViewModel)

public class AvailableView
{
     public int Afield1 { get; set; }
     public string RawAfield2 { get; set; }
     public string Afield2 => ConvertSomething(RawAfield2);
}

.Select(x => new AvailableView()
{
    Afield1 = x.Afield1
    RawAfield2 = x.Afield2
});

Where the conversion can be a private static method under the view model, a Static Singleton helper class, etc. The consuming view would use the Afield2 for the validated/formatted value.