EF Core LINQ can't translate schema submethod

Question

We have a BaseSchema class which contains an IEnumerable of another schema which contains data for each language used by our application.

We wrote a helper method in the base schema which will get the property in the description schema for the supplied language or the first one with a value if none is found.

return await Context.Allergies
    .Include(allergy => allergy.Descriptions)
    .AsNoTracking()
    .Select(allergy => new SynchronizableAllergyViewModel
    {
        Id = allergy.Id.ToString(),
        IsCommon = allergy.IsCommon,
        Name = allergy.GetTranslatedString(d => d.Name, Language),
    })
    .ToListAsync();

public string GetTranslatedString(Func<TMultilingualSchema, string> stringParam, 
    TLanguageSchemaId languageId)
{
    return Descriptions
        .OrderBy(d => d.LanguageId.Equals(languageId) ? 0 : 1)
        .Select(stringParam)
        .FirstOrDefault(d => !string.IsNullOrWhiteSpace(d)) ?? string.Empty;
}

We are on .NET 6.0 with EF Core 6.0.16 on SQL Server.

When using the helper method, we get

The LINQ expression 'd => d.Name' could not be translated

But, we can copy the method body in-place and get the expected result

return await Context.Allergies
    .Include(allergy => allergy.Descriptions)
    .AsNoTracking()
    .Select(allergy => new SynchronizableAllergyViewModel
    {
        Id = allergy.Id.ToString(),
        IsCommon = allergy.IsCommon,
        Name = allergy.Descriptions.OrderBy(d => d.LanguageId.Equals(Language) ? 0 : 1)
            .Select(d => d.Name)
            .FirstOrDefault(d => !string.IsNullOrWhiteSpace(d)) ?? string.Empty
    })
    .ToListAsync();

We tried to change the method to use Expressions and building the expression from the parameter name with no success.

When we try the "copy" solution on .NET 7 with EF Core 7.0.5, it gives the same result as the method call.

Answer 1

You can do that with LINQKit

Prerequisites:

1. Install package LinqKit.Microsoft.EntityFrameworkCore. Select appropriate version depending on EF Core major version.
2. Activate LINQKit via options:

builder
    .UseSqlServer(connectionString)
    .WithExpressionExpanding(); // enabling LINQKit extension

Modify your method in the following way:

public class Allergy
{
    ... // other properties

    [Expandable(nameof(GetTranslatedStringImpl))]
    public string GetTranslatedString(Func<TMultilingualSchema, string> stringParam, 
        TLanguageSchemaId languageId)
    {
        throw new NotImplementedException();
    }

    private static Expression<Func<Allergy, Func<TMultilingualSchema, string>, TLanguageSchemaId, string>> GetTranslatedStringImpl()
    {
        return (allergy, stringParam, languageId) => allergy.Descriptions
                .OrderBy(d => d.LanguageId.Equals(languageId) ? 0 : 1)
                .Select(stringParam)
                .FirstOrDefault(d => !string.IsNullOrWhiteSpace(d)) ?? string.Empty    
    }
}

After that you can use your method in LINQ Queries.