A Practical Approach to EF Core Using Scalar Functions
Introduction
When creating an application, some logical operations can be implemented on the database side using scalar functions. In SQL, a scalar function is a type of function that operates on a single value or a small number of input values and always returns a single value as a result. These functions are reusable blocks of code that perform calculations or data manipulation.
Basic characteristics of scalar functions
Single result: No matter how many input values a scalar function takes, it always produces one output value.
Data transformation: Scalar functions are often used to transform or modify data. This may include calculations, string manipulation, date and time operations, and more.
Logic Encapsulation: By encapsulating complex logic inside a function, scalar functions can simplify SQL queries, making them more readable and maintainable.
Predefined and custom functions: SQL provides a set of predefined scalar functions to perform commonly used operations such as math and string functions. You can also create your own scalar functions to solve specific problems.
Using scalar functions with EF Core
In this tutorial we will demonstrate how to:
Migrate a custom SQL scalar function into a database using Entity Framework Core.
Call and use this function in your .NET application, providing seamless integration between database logic and application code.
We will use a database AdventureWorks2019 for our examples. You can download the AdventureWorks2019 database follow this link.
Requirements
In software development, coding is not the first step. We must have some requirements, and development must begin with their analysis. We are planning to use the AdventureWorks2019 database and need to create a function that will calculate the total unit price for a specific offer identified by its ID. We will use a table
Sales.SalesOrderDetailand its columnsUnitPriceAndSalesOfferId.
Getting started
If you don't like reading articles, here is my YouTube video where I explain everything step by step.
I prefer to implement/write SQL functions directly using SQL IDEs such as Microsoft SQL Server Management Studio and then copy them into Visual Studio for 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;
END We are implementing a basic web API project in Asp.net Core along with EF Core. Create a new Asp.net Core Web API project (in our repository it's called EfCoreWithScalarFunctionsAPI). We plan to work with EF Core, so 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.Tools For migration ufn_GetTotalUnitPriceBySalesOfferId from Visual Studio we just need to create an empty migration file. To do this, enter add-migration Initial and press Enter. This should create 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 migration SQL file is ready, but we don't have a connection string that references our database.
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"ConnectionStrings": {
"AdventureWorksDb": "Data Source=.;Initial Catalog=AdventureWorks2019;Integrated Security=SSPI;TrustServerCertificate=TRUE;"
},
"AllowedHosts": "*"
}appsettings.json file configuration
Add a folder 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);
}
}
}AdventureWorksDbContext contains method GetTotalUnitPriceBySpecialOfferId. It has no implementation because it will be mapped to our scalar function at runtime. To interact with our function correctly, we must override the method OnModelCreating and configure our function there. This method is called by Entity Framework Core (EF Core) during model configuration to determine how C# classes are mapped to the database schema.
Inside OnModelCreating
modelBuilder.HasDbFunction(...): This line sets up a database function (UDF – User Defined Function) that EF Core can translate into an equivalent SQL function call when building queries..GetMethod(name of (GetTotalUnitPriceBySpecialOfferId), new[] {type (int) }): Receives information about the method through reflection.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.base.OnModelCreating(modelBuilder);: calls the method implementationOnModelCreatingbase class.
Ultimately this code tells EF Core to recognize the custom method (GetTotalUnitPriceBySpecialOfferId) in your class AdventureWorksDbContext as a database function. When we use this method in our LINQ queries, EF Core will translate it into an equivalent SQL call using the provided name (“ufn_GetTotalUnitPriceBySalesOfferId“). This allows you to use C# logic directly in SQL queries.
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 Program.csto specify the database connection string.
Now run the command update-database from the NuGet Package Manager console and this should migrate our scalar function to the AdventureWorks2019 database.
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()
{
//this is just an example of using it, not an optimal solution...
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 called 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 receives order details. It uses LINQ to query the table SalesOrderDetails.
The query filters the data where the custom function GetTotalUnitPriceBySpecialOfferId returns the total unit price greater than 10,000 for the linked SpecialOfferId. The results are then limited to the first 10 using Take(10). Finally, the received data is converted to a list asynchronously and returned as a successful HTTP response (status code 200) using Ok(response).
