I've this custom function created in a Postgres db.
CREATE OR REPLACE FUNCTION schema."ProcessDocument"(number text, document jsonb)
RETURNS jsonb
LANGUAGE plpgsql
AS $function$
DECLARE
result jsonb;
begin
// insert into result
RETURN result;
end;
$function$
;
in my dbcontext I am defining the custom function as
public byte[] ProcessDocument(string number, byte[] document) => throw new NotImplementedException();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasDbFunction(GetType().GetMethod(nameof(ProcessDocument), [typeof(string), typeof(byte[])])!).HasSchema(Schema));
}
and my query is
await dbContext.Table
.Where(x => x.number == number)
.Select(x => dbContext.ProcessDocument(x.number, x.Document))
.AsNoTracking()
.FirstOrDefaultAsync(cancellationToken);
When I run the above code, the app is throwing the NotImplementedException which means that EFCore is not understanding the custom db function and attempting to run it on the client. For this specific scenario what is the problem? and how I can tell EF to translate it as a function call?
Finally found an answer after so searching for so long, not event ChatGPT could help here. This is not very clear in the documentation and I am not sure why EF is behaving like this, maybe someone from the EF team can shed some more light on this.
The solution simply was to mark the function as static, that's it 🙂. So the function definition should be like this and the dbcontext registration can be simply made with expression function.
public static byte[] ProcessDocument(string number, byte[] document) => throw new NotImplementedException();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasDbFunction(() => ProcessDocument(default!, default!)).HasSchema(Schema));
}
Found the main issue. I was invoking the ProcessDocument from an interface while registering it from the dbcontext itself. So I assume that EF expects the method to be called from a variable which is type is exactly as the Registration it gots.