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
How do you call a scalar function? First, specify the name of the function after the CREATE FUNCTION keywords. Second, specify a list of parameters surrounded by parentheses after the function name. Third, specify the data type of the return value in the RETURNS statement.
Step 1: Create an entity class which inherits “DbContext” class. Step 2: The following is the structure of the database with table and stored procedure. Step 3: Create a class to store the returned tabular value. Step 4: Create an object for the entity above and method to call a function.
Step 1 − Select the Console Application from the middle pane and enter TableValuedFunctionDemo in the name field. Step 2 − In Server explorer right-click on your database. Step 3 − Select New Query and enter the following code in T-SQL editor to add a new table in your database.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With