Practical approach to EF Core with Scalar functions

Introduction

When building an application, you may have some logic in your database side implemented using scalar functions. In SQL, a scalar function is a type of function that operates on either a single value or a small number of input values and always returns a single value as output. These functions are essentially reusable blocks of code that perform calculations or manipulations on data.

Here are the main characteristics of scalar functions.

A scalar function has a Single Output. No matter how many inputs a scalar function takes, it always produces one output value.
Scalar functions are used to transform or modify data in various ways. This can involve calculations, string manipulations, date/time operations, and more.
By encapsulating complex logic within a function, scalar functions can simplify SQL queries and make them more readable and maintainable.
SQL comes with a set of pre-defined scalar functions for common operations. You can also create your own custom scalar functions to address specific needs.

This tutorial will demonstrate how to migrate your scalar SQL function to the database and how to call it using Entity Framework Core(EF Core)

You can download the Adventureworks2019 database from here.

The requirements

In software development, coding is not the first step. We should have some requirements and development should start with analyzing these requirements. We plan to use the AdventureWorks2019 database and we require to create a function that calculates the total unit price for a specific sales offer identified by its ID. We will use Sales.SalesOrderDetail table and its UnitPrice and SalesOfferId columns.

SalesOfferId

Getting started

I prefer to implement/write SQL functions directly using SQL IDE like Microsoft SQL Server Management Studio and then copy it to Visual Studio to migrate it. 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 will implement the basic Asp.net Core web API project along with EF Core. Create a new Asp.net Core Web API project (it is called EfCoreWithScalarFunctionsAPI in our repository). We plan to work with EF Core, so we need to install packages related to EF Core. Open Tools -> nuget package manager -> package manager console and type the following commands.

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools

To migrate ufn_GetTotalUnitPriceBySalesOfferId from Visual Studio we just need to generate an empty migration file. To do it, just type add-migration Initial and hit enter. It should generate an empty migration file. Now we need to update it. Here is how 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 any connection string that refers to our database.

Here is our appsettings.json file.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "AdventureWorksDb": "Data Source=.;Initial Catalog=AdventureWorks2019;Integrated Security=SSPI;TrustServerCertificate=TRUE;"
  },
  "AllowedHosts": "*"
}

Add a folder called Database to the root of the project 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 specifies a method called GetTotalUnitPriceBySpecialOfferId. It doesn’t have any implementation because at runtime it will be mapped to our scalar function. In order to properly interact with our function we should override the OnModelCreating method and construct our function there. This method is called Entity Framework Core (EF Core) during model configuration to allow you to define how your C# classes map to the database schema.

Within 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. The first argument (type of(AdventureWorksDbContext)) specifies the class containing the UDF method (GetTotalUnitPriceBySpecialOfferId)
.GetMethod(name of (GetTotalUnitPriceBySpecialOfferId), new[] {type (int) }): retrieves the reflection information for the specific method within that class.
name of (GetTotalUnitPriceBySpecialOfferId): gets the name of the method as a string.
new[] {type (int) }: creates an array specifying that the UDF takes an int (integer) parameter.
.HasName("ufn_GetTotalUnitPriceBySalesOfferId"): This configures the name that EF Core will use for the UDF in the generated SQL queries. Here, it's set to "ufn_GetTotalUnitPriceBySalesOfferId" (assuming this is the actual name of the UDF in your database).
base.OnModelCreating(modelBuilder);: This calls the base class's implementation of OnModelCreating, which might contain additional model configurations specific to your application.

In the end, this code tells EF Core to recognize a custom method (GetTotalUnitPriceBySpecialOfferId) in your AdventureWorksDbContext class as a database function. When we use this method in our LINQ queries, EF Core will translate it to an equivalent SQL call using the provided name ("ufn_GetTotalUnitPriceBySalesOfferId"). This allows you to leverage your C# logic directly within your database queries.

And the only missing item is our SalesOrderDetail model.

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; }
    }
}

In the end, we need to update our Program. cs to identify our database connection.

Now run the update-database command from the Nuget package manager console and it should migrate our scalar function to the AdventureWorks2019 database.

Using scalar function

In order 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 AdventureWorksController that handles HTTP requests related to sales order details. It injects an AdventureWorksDbContext instance through the constructor to interact with the database.

The GetSalesOrderInformationAsync method is an asynchronous action that retrieves sales order details. It uses LINQ to query the SalesOrderDetails table.

The query filters for details where a custom function GetTotalUnitPriceBySpecialOfferId returns a total unit price exceeding 10,000 for the associated SpecialOfferId. It then limits the results to the top 10 using Take(10). Finally, the retrieved details are converted to a list asynchronously and returned as a successful HTTP response (status code 200) using Ok(response).