How do I write EF.Functions extension method?
EFC 3.0 has changed this process a little, as per https://learn.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));
}
}
Adding new scalar method to EF.Functions
is easy - you simply define extension method on DbFunctions
class. However providing SQL translation is hard and requires digging into EFC internals.
However EFC 2.0 also introduces a much simpler approach, explained in Database scalar function mapping section of the New features in EF Core 2.0 documentation topic.
According to that, the easiest would be to add a static method to your DbContext
derived class and mark it with DbFunction
attribute. E.g.
public class MyDbContext : DbContext
{
// ...
[DbFunction("SOUNDEX")]
public static string Soundex(string s) => throw new Exception();
}
and use something like this:
string param = ...;
MyDbContext db = ...;
var query = db.Customers
.Where(e => MyDbContext.Soundex(e.LastName) == MyDbContext.Soundex(param));
You can declare such static methods in a different class, but then you need to manually register them using HasDbFunction
fluent API.