How to map a method to a SQL function?

Question

I develop a web application with clean architecture. So my context database in persistence app and endpoint (ASP.NET Core MVC app) access to database with an interface in application layer.

I want map a method to a SQL function in my Oracle database. In case I have only a single ASP.NET Core MVC app there is a document in link and everything is OK.

But I don’t know how can configure it in CLEAN Architecture.

DataBaseContext class

[DbFunction("AddTwoNum ", " MySchema ")]
public string AddTwoNum (int? a, int? b)
{
    throw new NotImplementedException();       
}

OnModelCreating:

modelBuilder.HasDefaultSchema("MySchema");
modelBuilder.HasDbFunction(typeof(DataBaseContext).GetMethod(nameof(AddTwoNum), new[] {   typeof(int), typeof(int) }));

IDataBaseContext interface:

string AddTwoNum(int? a, int? b);

In my ASP.NET Core MVC app:

var query = from b in _context.MyTable 
            select b,
                   name = _context.AddTwoNum (b.AField, b.BField),;

When I run a query in my ASP.NET Core MVC app, it generates an exception and run this line map function in DataBaseContext

 throw new NotImplementedException();

Answer 1

I can't speak about CLEAN and similar architectures, but currently (EFC 6.0) EF Core gives you only two options for mapping method to database function - the CLR method must be either (1) instance method of the actual DbContext derived class or (2) static method of any class.

This is sort of mentioned in the documentation

Note

In the example, the method is defined on DbContext, but it can also be defined as a static method inside other classes.

even though it would be better if they say that directly rather than as a note for an example. And of course they "forgot" to say that this is a must, i.e. "also can be" really is "must be".

Anyway, now knowing the facts, apparently option (1) is not applicable in your scenario, since from CLR metadata standpoint interface method is not the same as the class method implementing it, hence the mapping has no effect and EF Core resorts to client evaluation if this is the final projection, or error if there are other LINQ operators applied afterwards.

So the only option is (2) - static method. Since you are on net6, you can define it directly in the interface (after removing the one you've added before):

public interface IDataBaseContext
{
    public static string AddTwoNum(int? a, int? b)
        => throw new NotSupportedException();
}

or you could use separate static class in the same project. The key is to have a static method. Note that there is no EF Core dependency here - no attributes (data annotations) etc. Just plain static method throwing exception if called.

Then in the project containing the DbContext, you just provide the mapping with something like this (simplest):

modelBuilder
    .HasDbFunction(() => IDataBaseContext.AddTwoNum(default, default));

This returns DbFunctionBuilder which can be used to fluently configure other mappings if needed like HasName, HasSchema, HasParameter etc.

Finally, this of course changes the way you "call" the method in the query, since C# does not allows calling static methods via instance variable, so it has to be plain static method call syntax:

select new {
   b,
   name = IDataBaseContext.AddTwoNum(b.AField, b.BField),
}

or add

using static IDataBaseContext;

so you can the more naturally looking

select new {
   b,
   name = AddTwoNum(b.AField, b.BField),
}