CODEWITHSUNDEEP
c#sql-server-2016ef-core-5.0
sofsntp
sofsntp

Reputation: 2164

HasDbFunction not mapping the CLR function to the SQL Server function

I want to create MyDbFunction scalar function in SQL sever and call it from a .NET 5 application using the HasDbFunction mapping. For some reason the mapping is always ignored. I have simplified my example to make sure it's not a signature/arguments issue but that has not helped. I built it following the official documentation: https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping

Step 1. I created this CLR function:

public int MyCLRFunction (int @Code)
  => throw new NotSupportedException("Mapping issue for MyDbFunction");

Step2. I added the mapping to my datacontext SPContext as below:

static void OnModelCreating(ModelBuilder modelBuilder)  
{                 
  modelBuilder.HasDbFunction(typeof(SPContext).GetMethod(nameof(SPContext.MyCLRFunction), new[] 
  { typeof(int) })).HasName("MyDbFunction");
}

When I run it in debug mode, I can see the debugger calling the mapping when running OnModelCreating. I expect that when calling MyCLRFunction(2) it would return 42 using after calling MyDbFunction on the database. It shouldn't be triggering the exception in the body of the CLRFunction but exception is always triggered. What could cause the mapping to be ignored?

Code for the SQL Function on the default schema

Create FUNCTION [dbo].[MyDbFunction]
(
    @Code int
    
)
RETURNS int
AS
BEGIN

return 42

END

I tried to add this tag, but not helping either

[DbFunction("MyDbFunction", "dbo")]
public int MyCLRFunction (int @Code)
  => throw new NotSupportedException("Mapping issue for MyDbFunction");

I tried to define MyCLRFunction as a static method in another class as suggested in the official documentation:

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

I am not sure how this is supposed to work because if it's defined as a static method in another class and run it without calling the datacontext first the mapping with HasDbFunction is never being called.

Upvotes: 3

Views: 4520

Answers (2)

Delmirio Segura
Delmirio Segura

Reputation: 1691

I had the same problem as you, I solved it by using GetRuntimeMethod instead of GetMethod.

  modelBuilder.HasDbFunction(typeof(EFFunctions)
          .GetRuntimeMethod(
              nameof(EFFunctions.fn_GetString),
              new[] { typeof(DateTime), typeof(int), typeof(int), typeof(string), typeof(int)}
          ))
          .HasName(nameof(EFFunctions.fn_GetString));

Upvotes: 0

sofsntp
sofsntp

Reputation: 2164

So far the most efficient I see is this:

    public static decimal? MyFunction(int code)
    {
        using (var db = new MyContext())
        {
            try
            {
                return db.AnyEntityWithValues.Take(1).Select(t => db.MyCLRFunction(code).Single();
            }
            catch (Exception e)
            {
                return null;
            }
        }
    }

I wish I could just do without using an extension method of the DataContext

  var myVal = db.MyCLRFunction(code);

Upvotes: 0

Related Questions