A Practical Approach to EF Core Using Scalar Functions
When developing an application, some logic can be implemented on the database side using scalar functions. In SQL, a scalar function is a type of function that operates on one or a small number of input values and always returns one value as a result. These functions are reusable blocks of code that perform calculations or data manipulation.
Here are the main characteristics of scalar functions:
Single result: A scalar function always returns one value, no matter how many inputs it takes.
Data modification: Scalar functions are used to transform or change data in various ways. This may include calculations, string manipulation, date/time operations, and more.
Simplifying Queries: Encapsulating complex logic inside a function allows you to simplify SQL queries, making them more readable and maintainable.
Predefined and custom functions: SQL has a set of predefined scalar functions to perform common operations. You can also create your own custom scalar functions to solve specific problems.
This tutorial will show you how to migrate your scalar SQL function into a database and how to call it using Entity Framework Core (EF Core).
You can download the Adventureworks2019 database from here.
Requirements
In software development, coding is not the first step. You must first have requirements, and development must begin with their analysis. We are planning to use the AdventureWorks2019 database and we need to create a function that calculates the total cost per item for a particular sales offer identified by its ID. We will use a table Sales.SalesOrderDetail and its columns UnitPrice And SalesOfferId.
I prefer to implement and write SQL functions directly using an SQL IDE such as Microsoft SQL Server Management Studio, and then copy them into Visual Studio for later migration. Here is our function:
CREATE OR ALTER FUNCTION [dbo].[ufn_GetTotalUnitPriceBySalesOfferId]
(
@specialOfferId INT
)
RETURNS DECIMAL(16,2)
AS
BEGIN
DECLARE @result DECIMAL(16,2);
SELECT @result = SUM(UnitPrice)
FROM Sales.SalesOrderDetail AS SOD
WHERE SOD.SpecialOfferID = @specialOfferId;
RETURN @result;
ENDWe will be implementing a basic Web API project in ASP.NET Core along with EF Core. Create a new Web API project in ASP.NET Core (in our repository it's called EfCoreWithScalarFunctionsAPI). Since we plan to work with EF Core, we need to install EF Core related packages. Open Tools -> NuGet Package Manager -> Package Manager Console and enter the following commands.
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.ToolsOh, I forgot to mention: if reading seems boring, here is a YouTube video version where I explain everything from scratch and in more detail.
To migrate a function ufn_GetTotalUnitPriceBySalesOfferId from Visual Studio, we just need to generate an empty migration file. To do this, enter the command add-migration Initial and press Enter. This should generate an empty migration file. Now we need to update it. This is what it should look like in the end.
using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace EfCoreWithScalarFunctionsAPI.Migrations
{
/// <inheritdoc />
public partial class Initial : Migration
{
/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(@"
CREATE FUNCTION ufn_GetTotalUnitPriceBySalesOfferId(@specialOfferId as int)
RETURNS DECIMAL(16,2) AS
BEGIN
DECLARE @result as decimal(16,2);
SELECT @result = SUM(Unitprice)
FROM Sales.SalesOrderDetail AS SOD
WHERE SOD.SpecialOfferID = @specialOfferId;
RETURN @result;
END");
}
/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(@"DROP FUNCTION dbo.ufn_GetTotalUnitPriceBySalesOfferId");
}
}
}Our SQL migration file is ready, but we don't have a connection string that references our database.
Here is our file appsettings.json.
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"ConnectionStrings": {
"AdventureWorksDb": "Data Source=.;Initial Catalog=AdventureWorks2019;Integrated Security=SSPI;TrustServerCertificate=TRUE;"
},
"AllowedHosts": "*"
}Add a folder named Database to the project root and add AdventureWorksDbContext with the following content.
using Microsoft.EntityFrameworkCore;
namespace EfCoreWithScalarFunctionsAPI.Database
{
public class AdventureWorksDbContext : DbContext
{
public DbSet<SalesOrderDetail> SalesOrderDetails { get; set; }
public decimal GetTotalUnitPriceBySpecialOfferId(int salesOfferId)
=> throw new System.NotImplementedException();
public AdventureWorksDbContext(DbContextOptions<AdventureWorksDbContext> dbContextOptions)
: base(dbContextOptions)
{ }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasDbFunction(typeof(AdventureWorksDbContext)
.GetMethod(nameof(GetTotalUnitPriceBySpecialOfferId), new[] { typeof(int) }))
.HasName("ufn_GetTotalUnitPriceBySalesOfferId");
base.OnModelCreating(modelBuilder);
}
}
}Our AdventureWorksDbContext contains a method called GetTotalUnitPriceBySpecialOfferId. It has no implementation since it will be mapped to our scalar function at runtime. To interact with our function correctly, we must override the method OnModelCreating and construct our function there. This method is called by Entity Framework Core (EF Core) during model configuration so you can define how your C# classes map to the database schema.
Inside OnModelCreating:
modelBuilder.HasDbFunction(…): This line configures a database function (UDF – user defined function) that EF Core can translate into an equivalent SQL function call when building your queries. First argument (type of(AdventureWorksDbContext) specifies a class containing a UDF method (GetTotalUnitPriceBySpecialOfferId).
.GetMethod(name of (GetTotalUnitPriceBySpecialOfferId), new[] {type(int)}): Gets information about a method through reflection for a specific method in this class.
name of (GetTotalUnitPriceBySpecialOfferId): Gets the method name as a string.
new[] {type(int)}: Creates an array indicating that the UDF accepts an int parameter.
.HasName(“ufn_GetTotalUnitPriceBySalesOfferId”): Configures the name that EF Core will use for the UDF in generated SQL queries. Here it is set to “ufn_GetTotalUnitPriceBySalesOfferId” (assuming this is the actual UDF name in your database).
base.OnModelCreating(modelBuilder);: Calls implementation OnModelCreating base class, which can contain additional model configurations specific to your application.
Ultimately this code tells EF Core that it should recognize a custom method (GetTotalUnitPriceBySpecialOfferId) in your class AdventureWorksDbContext as a database function. When we use this method in our LINQ queries, EF Core will convert it into an equivalent SQL call using the provided name (“ufn_GetTotalUnitPriceBySalesOfferId”). This allows you to use C# logic directly in database queries.
And the only missing element is our model SalesOrderDetail.
using System.ComponentModel.DataAnnotations.Schema;
namespace EfCoreWithScalarFunctionsAPI.Database
{
[Table("SalesOrderDetail", Schema = "Sales")]
public class SalesOrderDetail
{
public int SalesOrderDetailId { get; set; }
public int SalesOrderId { get; set; }
public int? ProductId { get; set; }
public decimal UnitPrice { get; set; }
public decimal UnitPriceDiscount { get; set; }
public decimal LineTotal { get; set; }
public int SpecialOfferId { get; set; }
}
}At the end we need to update our file Program.csto specify the database connection string.
Now run the command update-database in the NuGet Package Manager Console and this should migrate our scalar function to the database AdventureWorks2019.
Using a Scalar Function
To call our newly created function, let's create a new controller (AdventureWorksController) with the following content.
using EfCoreWithScalarFunctionsAPI.Database;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Infrastructure;
using Microsoft.EntityFrameworkCore;
namespace EfCoreWithScalarFunctionsAPI.Controllers
{
[ApiController]
[Route("api/[controller]")]
public class AdventureWorksController : ControllerBase
{
private readonly AdventureWorksDbContext _adventureWorksDbContext;
public AdventureWorksController(AdventureWorksDbContext adventureWorksDbContext)
{
_adventureWorksDbContext = adventureWorksDbContext;
}
[HttpGet]
public async Task<ActionResult<IEnumerable<SalesOrderDetail>>> GetSalesOrderInformationAsync()
{
var response = await (from sod in _adventureWorksDbContext.SalesOrderDetails
where _adventureWorksDbContext.GetTotalUnitPriceBySpecialOfferId(sod.SpecialOfferId) > 10_000
select sod)
.Take(10)
.ToListAsync();
return Ok(response);
}
}
}This code defines a controller named AdventureWorksControllerwhich handles HTTP requests related to sales order details. It injects an instance AdventureWorksDbContext through the constructor to interact with the database.
Method GetSalesOrderInformationAsync is an asynchronous action that retrieves sales order details. It uses LINQ to query the table SalesOrderDetails.
The query filters the data where the custom function GetTotalUnitPriceBySpecialOfferId returns the total unit cost greater than 10,000 for the linked SpecialOfferId. The results are then limited to the first 10 records using Take(10). Ultimately, the retrieved parts are asynchronously converted into a list and returned as a successful HTTP response (status code 200) using Ok(response).
Conclusion
In this tutorial, we walked through the key steps to integrate SQL scalar functions using EF Core in an ASP.NET Core application. We started by defining the requirements, where we used the database AdventureWorks2019 to create a function that calculates the total unit cost of an item based on a sales offer ID.
We learned how to migrate an SQL function to a database using EF Core. The process involved creating an empty migration file and updating it to include our scalar function. We have also added the database connection string to appsettings.json and configured the database context AdventureWorksDbContextusing the method OnModelCreating for mapping our function.
After that we looked at how to call our function in the controller AdventureWorksControllerusing LINQ queries. We used the method GetTotalUnitPriceBySpecialOfferIdwhich was mapped to our SQL function and showed how to return the result in an HTTP response.
In summary, we've demonstrated how to integrate custom SQL functions into a C# application using EF Core, allowing you to extend the functionality of your queries and take advantage of existing database logic in your code.
