How to call a Scalar Function in Entity Framework Core

Question

following this article I was trying to call an inside SQL Function into my application using Entity Framework Core

I created the static method in context, like this:

public class DbContext : DbContext
{
    public DbContext(DbContextOptions<DbContext> options) : base(options)
    {
    }

    [DbFunction("FN_ENCRYPT", "DBO")]
    public static string FN_ENCRYPT(string ENC)
    {
        throw new NotImplementedException();
    }
}

after this, I get a little confused about how to call it, so I tried in this way (because its a static method, of course):

public string Encript(string word)
{
    return DbContext.FN_ENCRYPT(word);
}

but, guess what? I received a "nice" NotImplementedException :)

Can somebody help me?

thanks in advance

Answer 1

Using the call itself it throws the exception because it actually executes the C# code. The reason it is recommended to throw an exception is exactly this, to avoid inadvertent use, ie by directly calling it. That signature will be interpreted by the given LINQ provider and translate into the proper SQL statements.

In MS EF Core Database scalar function mapping :

they can be used in LINQ queries and translated to SQL

Here is a working demo :

Model and DbContext

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime DOB  { get; set; }
}

public class MyDbContext:DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options):base(options)
    { }

    public DbSet<Employee> Employees { get; set; }

    [DbFunction("CalculateAge", "dbo")]
    public static int CalculateAge(DateTime dob)
    {
        throw new NotImplementedException();
    }
}

and use

public IActionResult GetAge(int employeeId)
    {
        var query = _context.Employees
                .Where(x => x.Id == employeeId)
                .Select(d =>new
                { 
                    Name=d.Name,
                    DOB=d.DOB,
                    Age= MyDbContext.CalculateAge(d.DOB)
                }).ToList();
        return Json(query);
    }

Result