How do I write EF.Functions extension method?

Question

I see that EF Core 2 has EF.Functions property EF Core 2.0 Announcement which can be used by EF Core or providers to define methods that map to database functions or operators so that those can be invoked in LINQ queries. It included LIKE method that gets sent to the database.

But I need a different method, SOUNDEX() that is not included. How do I write such a method that passes the function to the database the way DbFunction attribute did in EF6? Or I need to wait for MS to implement it? Essentially, I need to generate something like

SELECT * FROM Customer WHERE SOUNDEX(lastname) = SOUNDEX(@param)

user_127566 · Answer

EFC 3.0 has changed this process a little, as per https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#udf-empty-string

Example of adding CHARINDEX in a partial context class:

public partial class MyDbContext
{
    [DbFunction("CHARINDEX")]
    public static int? CharIndex(string toSearch, string target) => throw new Exception();

    partial void OnModelCreatingPartial(
        ModelBuilder modelBuilder)
    {
        modelBuilder
            .HasDbFunction(typeof(MyDbContext).GetMethod(nameof(CharIndex)))
            .HasTranslation(
                args =>
                    SqlFunctionExpression.Create("CHARINDEX", args, typeof(int?), null));
    }
}

How do I write EF.Functions extension method?

2 Answers